读取指定excel,修改并某个值并另存到指定路径

时间:2024-11-02 14:37:38

HSSFWorkBook是解析excel2007以前的版本(xls)
之后的版本使用XSSFWrokBook(xlsx)

附:处理excel2007之后的版本代码:

package gbyp.autoQuery.action;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.nio.channels.FileChannel;
import java.util.Date;
import java.util.Iterator; import org.apache.poi.hssf.extractor.ExcelExtractor;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFHyperlink;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook; import xsf.IContextDictionary;
import xsf.data.DBManager;
import xsf.data.DataRow;
import xsf.data.DataTable;
import xsf.data.Sql;
import xsf.web.HttpContext;
import xsf.web.IAction; public class ExcelExport { public static void main(String[] args) {
ExcelExport ee = new ExcelExport();
ee.writeExcel2();
} public void writeExcel2() {
// 声明excel对象
XSSFWorkbook wb = null;
// 声明poi流
// POIFSFileSystem fs=null;
// 写读取的文件路径
String path = "E:/需要完善的项目清单.xlsx";
try { // 创建文件流 创建excell对象
wb = new XSSFWorkbook(new FileInputStream(path)); // 获得工作薄, 得到工作表
XSSFSheet sheet = wb.getSheetAt(0); boolean isOne = true;
for (Iterator<Row> iter = sheet.rowIterator(); iter.hasNext();) {
// 得到行
Row row = iter.next();
// 迭代列
// 循环每一行的所有列
int i = 0;
for (Iterator<Cell> cellIter = row.cellIterator(); cellIter
.hasNext();) {
// 得到列对象
Cell cell = cellIter.next();
if (i == 1) {
String content = cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC ? cell
.getNumericCellValue() + ""
: cell.getStringCellValue();
if (!content.equals("项目名称")) {
//数据库取值并赋值
cell.setCellValue("****项目");
}
} i++;
}
if (isOne) {
isOne = false;
}
} String path2 = "E:/需要完善的项目清单2.xlsx";
// 创建输出流
OutputStream out = new FileOutputStream(path2);
// 将数据写入文件
wb.write(out);
// 关闭文件
out.close(); } catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}

附:处理excel2007之前版本代码:

package gbyp.autoQuery.action;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.nio.channels.FileChannel;
import java.util.Date;
import java.util.Iterator;
import org.apache.poi.hssf.extractor.ExcelExtractor;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFHyperlink;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress; import xsf.IContextDictionary;
import xsf.data.DBManager;
import xsf.data.DataRow;
import xsf.data.DataTable;
import xsf.data.Sql;
import xsf.web.HttpContext;
import xsf.web.IAction; public class ExcelExport { public static void main(String[] args) {
ExcelExport ee = new ExcelExport();
ee.writeExcel2();
} public void writeExcel2() {
// 声明excel对象
HSSFWorkbook wb = null;
// 声明poi流
POIFSFileSystem fs = null;
// 写读取的文件路径
String path = "E:/需要完善的项目清单.xls";
try { // 设置要读取的文件路径
// 创建文件流
fs = new POIFSFileSystem(new FileInputStream(path)); // HSSFWorkBook相当于一个excel文件,HSSFWorkBook是解析excel2007以前的版本(xls)
// 之后的版本使用XSSFWrokBook(xlsx)
// 创建excell对象
wb = new HSSFWorkbook(fs); // 获得工作薄
// 得到工作表
HSSFSheet sheet = wb.getSheetAt(0); boolean isOne = true; for (Iterator<Row> iter = sheet.rowIterator(); iter.hasNext();) {
// 得到行
Row row = iter.next();
// 迭代列
// 循环每一行的所有列
int i = 0;
for (Iterator<Cell> cellIter = row.cellIterator(); cellIter.hasNext();) {
// 得到列对象
Cell cell = cellIter.next();
if (i == 1) {
String content = cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC ? cell.getNumericCellValue() + "": cell.getStringCellValue();
if(!content.equals("项目名称")){
cell.setCellValue("****项目");
}
} i++;
}
if (isOne) { isOne = false;
}
} String path2 = "E:/需要完善的项目清单2.xls";
// 创建输出流
OutputStream out = new FileOutputStream(path2);
// 将数据写入文件
wb.write(out);
// 关闭文件
out.close(); } catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} }