POI Excel文件的读取与写入
1. 创建目录
if(!(new File(path).isDirectory())){
new File(path).mkdirs();
}
2. 读取Excel文件，并进行写入操作
Workbook workbook = new HSSFWorkbook(new FileInputStream(path+filename));
Sheet sourceSheet = workbook.getSheetAt(0);
int rowEnd = sourceSheet.getLastRowNum();
for (int i = 0; i <= rowEnd; i++) {//按 行 读取
Row row = sourceSheet.getRow(i);
//读取
String str=row.getCell(n).getStringCellValue();//n表示第几列
//写入
row.createCell(m, Cell.CELL_TYPE_STRING).setCellValue(nbFile.getProductId().toString());//m表示第几列
}
FileOutputStream out=new FileOutputStream(WebConstants.FILE.UPLOAD_PATH + nbFile.getUserId() + "/" + nbFile.getFilename());
out.flush();
workbook.write(out);
out.close();
3. 写入Excel文件
HSSFWorkbook resultWorkbook=new HSSFWorkbook();
HSSFSheet resultSheet =resultWorkbook.createSheet("sheet1");
for(int i=0;i<100;i++){
Row row=resultSheet.createRow(i);
row.createCell(n, Cell.CELL_TYPE_STRING).setCellValue(trans.getName());//n表示第几列
}
FileOutputStream out=new FileOutputStream(path+filename+".xls"); //向d://test.xls中写数据
out.flush();
resultWorkbook.write(out);
out.close();
4. maven项目引入POI包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.10-FINAL</version>
</dependency>
5. 例子
package cn.*.*.quartz;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.List;
@Component
public class BatchQuery {
private final static Log logger = LogFactory.getLog(BatchQuery.class);
@Autowired
private HandleFileService handleFileService;
@Autowired
private CheckService checkService;
private String path="D://excel/"
@Scheduled(cron="${batch.query.frequency}")
public void query() {
try {
List<NbFile> fileList = handleFileService.getNbFile();
logger.info("batchQuery number="+fileList.size());
if (fileList != null && fileList.size() > 0) {
for (file1 File: fileList) {
if(!(new File(path).isDirectory())){
new File(path).mkdirs();
}
//结果文件
HSSFWorkbook resultWorkbook=new HSSFWorkbook();
HSSFSheet resultSheet =resultWorkbook.createSheet("sheet1");
//客户请求文件
Workbook workbook = new HSSFWorkbook(new FileInputStream(path+ file1.getFilename()));
Sheet sourceSheet = workbook.getSheetAt(0);
int rowEnd = sourceSheet.getLastRowNum();
//边读取Excel文件，边写入结果，写入已读记录
for (int i = 0; i <= rowEnd; i++) {//按 行 读取
Row row = sourceSheet.getRow(i);
Trans trans=queryByProduct(row,file1.getProductId(),file1.getUserId());
if (null!=trans){//有结果
//把查询结果添加入新的Excel文件
addResultToExcel(resultWorkbook,resultSheet,trans, i,file1);
//标志该条记录已查
addQueryFlag(workbook,row, file1);
}
}
handleFileService.delete(nbFile);
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
//每查询一条记录，在该记录后面添加已查询的标志
private void addQueryFlag(Workbook workbook,Row row,NbFile nbFile)throws Exception{
row.createCell(WebConstants.PP_CELLINDEX.STATE, Cell.CELL_TYPE_STRING).setCellValue(nbFile.getProductId().toString());
FileOutputStream out=new FileOutputStream(WebConstants.FILE.UPLOAD_PATH + nbFile.getUserId() + "/" + nbFile.getFilename());
out.flush();
workbook.write(out);
out.close();
}
//把查询信息 写入 Excel文件中
private void addResultToExcel(Workbook resultWorkbook,HSSFSheet resultSheet,Trans trans,int i,NbFile nbFile)throws Exception {
Row row=resultSheet.createRow(i);
row.createCell(WebConstants.PP_CELLINDEX.NAME, Cell.CELL_TYPE_STRING).setCellValue(trans.getName());
row.createCell(WebConstants.PP_CELLINDEX.IDCARD, Cell.CELL_TYPE_STRING).setCellValue(trans.getIdcard());
row.createCell(WebConstants.PP_CELLINDEX.PHONE, Cell.CELL_TYPE_STRING).setCellValue(trans.getPhone());
row.createCell(WebConstants.PP_CELLINDEX.BANKCARD, Cell.CELL_TYPE_STRING).setCellValue(trans.getBankcard());
row.createCell(WebConstants.PP_CELLINDEX.CPHM, Cell.CELL_TYPE_STRING).setCellValue(trans.getCphm());
row.createCell(WebConstants.PP_CELLINDEX.CLLX, Cell.CELL_TYPE_STRING).setCellValue(trans.getCllx());
row.createCell(WebConstants.PP_CELLINDEX.DABH, Cell.CELL_TYPE_STRING).setCellValue(trans.getDabh());
row.createCell(WebConstants.PP_CELLINDEX.JSZH, Cell.CELL_TYPE_STRING).setCellValue(trans.getJszh());
JSONObject json=JSONObject.parseObject(trans.getResultJSON());
row.createCell(WebConstants.PP_CELLINDEX.RESULTCODE, Cell.CELL_TYPE_STRING).setCellValue(json.getString("resultCode"));
row.createCell(WebConstants.PP_CELLINDEX.RESULTMESSAGE, Cell.CELL_TYPE_STRING).setCellValue(json.getString("resultMessage"));
FileOutputStream out=new FileOutputStream(WebConstants.FILE.RESULT_PATH+nbFile.getUserId()+"_"+WebConstants.ProductEnum.nameFromId(nbFile.getProductId())+".xls"); //向d://test.xls中写数据
out.flush();
resultWorkbook.write(out);
out.close();
}
//查询结果
private Trans queryByProduct(Row row,Integer productId,Integer userId)throws Exception{
if (null==row.getCell(WebConstants.PP_CELLINDEX.STATE)||!productId.toString().equals(row.getCell(WebConstants.PP_CELLINDEX.STATE).getStringCellValue())){
Trans trans=new Trans();
trans.setProductId(productId);
trans.setUserId(userId);
trans.setName(row.getCell(WebConstants.PP_CELLINDEX.NAME) == null ? null : row.getCell(WebConstants.PP_CELLINDEX.NAME).getStringCellValue());
trans.setIdcard(row.getCell(WebConstants.PP_CELLINDEX.IDCARD) == null ? null:row.getCell(WebConstants.PP_CELLINDEX.IDCARD).getStringCellValue());
trans.setPhone(row.getCell(WebConstants.PP_CELLINDEX.PHONE) == null ? null : row.getCell(WebConstants.PP_CELLINDEX.PHONE).getStringCellValue());
trans.setBankcard(row.getCell(WebConstants.PP_CELLINDEX.BANKCARD) == null ? null : row.getCell(WebConstants.PP_CELLINDEX.BANKCARD).getStringCellValue());
trans.setCphm(row.getCell(WebConstants.PP_CELLINDEX.CPHM) == null ? null : row.getCell(WebConstants.PP_CELLINDEX.CPHM).getStringCellValue());
trans.setCllx(row.getCell(WebConstants.PP_CELLINDEX.CLLX) == null ? null : row.getCell(WebConstants.PP_CELLINDEX.CLLX).getStringCellValue());
trans.setJszh(row.getCell(WebConstants.PP_CELLINDEX.JSZH) == null ? null : row.getCell(WebConstants.PP_CELLINDEX.JSZH).getStringCellValue());
trans.setDabh(row.getCell(WebConstants.PP_CELLINDEX.DABH) == null ? null : row.getCell(WebConstants.PP_CELLINDEX.DABH).getStringCellValue());
String result=checkService.check(trans);
if (!StringUtil.isEmpty(result)){
trans.setResultJSON(result);
}else {
return null;
}
return trans;
}
return null;
}
}
