使用POI读写Excel文件
读
首先转换类型到File
然后遍历第一页,我的execl有三行,如果三行中有空值,会返回空值的行号,否则就解析数据放入实体集合
package com.docc.util;
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.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
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.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
import org.springframework.web.multipart.MultipartFile;
import com.docc.model.Inventory;
import com.docc.util.ExcelHelper;
import com.sun.org.apache.regexp.internal.recompile;
import oracle.net.aso.f;
public class ReadInventory {
//使用POI解析Excel文件
@Test
public static List<Inventory> Read(MultipartFile file) throws FileNotFoundException, IOException{
//转File
File f = null;
if(file.equals("")||file.getSize()<=0){
file = null;
}else{
InputStream ins = file.getInputStream();
f=new File(file.getOriginalFilename());
inputStreamToFile(ins, f);
}
//包装一个Excel文件对象
//HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(f));
XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(f));
//暂时读取文件中第一个Sheet标签页
XSSFSheet hssfSheet = workbook.getSheetAt(0);
/*
* 遍历标签页中所有的行,数据放入集合inventorys
*/
List <Inventory> inventorys = new ArrayList<Inventory>();
try {
for (Row row : hssfSheet) {
/*
* 跳过首行
*/
int rowNum = row.getRowNum();
if(rowNum == 0){
continue;
}
/*
* 判断三行为空结束跳出
*/
if(row.getCell(0)==null || getValue(row.getCell(0),"####").trim().equals("")){
if(row.getCell(1)==null || getValue(row.getCell(1),"####").trim().equals("")){
if(row.getCell(2)==null || getValue(row.getCell(2),"####").trim().equals("")){
break;
}
}
}
/*
* 判断出现空行后重新遍历处理,返回一个包含空行号的集合
*/
if(row.getCell(0)==null || getValue(row.getCell(0),"####").trim().equals("")
|| row.getCell(1)==null || getValue(row.getCell(1),"####").trim().equals("")
|| row.getCell(2)==null || getValue(row.getCell(2),"####").trim().equals("")){
/*
* 处理,创建集合
*/
List <Inventory> inventorynull = new ArrayList<Inventory>();
Inventory inventoryn = new Inventory();
inventoryn.setCompany("以下行主字段为空:");
inventorynull.add(inventoryn);
/*
* 循环计数,放入集合
*/
for(Row rownull : hssfSheet){
/*
* 跳过第一行
*/
int rowji = rownull.getRowNum();
if(rowji == 0){
continue;
}
/*
* 结束退出
*/
if(rownull.getCell(0)==null || getValue(rownull.getCell(0),"####").trim().equals("")){
if(rownull.getCell(1)==null || getValue(rownull.getCell(1),"####").trim().equals("")){
if(rownull.getCell(2)==null || getValue(rownull.getCell(2),"####").trim().equals("")){
break;
}
}
}
/*
* 计数开始
*/
if(rownull.getCell(0)==null || getValue(rownull.getCell(0),"####").trim().equals("")
|| rownull.getCell(1)==null || getValue(rownull.getCell(1),"####").trim().equals("")
|| rownull.getCell(2)==null || getValue(rownull.getCell(2),"####").trim().equals("")){
int ji = rownull.getRowNum() +1;
String jiString = ji + "";
Inventory inventory = new Inventory();
inventory.setCompany(jiString);
inventorynull.add(inventory);
}//单个计数完毕
}//循环计数结束,已经把计数放入集合
//可以把集合返回了,里面是计数结果
return inventorynull;
}//判断出有空行的处理结束,已返回一个包含空行号的集合
/*
* 检查空处理完毕,开始获取三行数据,放入inventorys
*/
String company = getValue(row.getCell(0),"####");
String reportdate = getValue(row.getCell(1),"####");
String type = getValue(row.getCell(2),"####");
Inventory inventory = new Inventory();
inventory.setCompany(company);
inventory.setReportdate(reportdate);
inventory.setType(type);
inventorys.add(inventory);
}//遍历所有的行循环,获取了数据集合inventorys
} catch (Exception e) {
// TODO: handle exception
/*
* 在解析过程中出现的异常
*/
List <Inventory> inventoryno = new ArrayList<Inventory>();
Inventory inventory = new Inventory();
inventory.setCompany("解析数据异常");
inventoryno.add(inventory);
return inventoryno;
}// 获取了数据集合inventorys
return inventorys;
}
/*
* 文件类型转换
*/
public static void inputStreamToFile(InputStream ins,File file) {
try {
OutputStream os = new FileOutputStream(file);
int bytesRead = 0;
byte[] buffer = new byte[8192];
while ((bytesRead = ins.read(buffer, 0, 8192)) != -1) {
os.write(buffer, 0, bytesRead);
}
os.close();
ins.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/*
*读取excel列
*/
public static String getValue(Cell cell, String format) {
String cellValue = "";
switch (cell.getCellTypeEnum()) {
case NUMERIC: // 数字
if(DateUtil.isCellDateFormatted(cell)){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
cellValue=sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
}else{
DecimalFormat df = new DecimalFormat(format);
cellValue=df.format(cell.getNumericCellValue());
// cellValue=String.valueOf(cell.getNumericCellValue());
}
break;
case STRING: // 字符串
cellValue = String.valueOf(cell.getStringCellValue());
break;
case BOOLEAN: // Boolean
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case FORMULA: // 公式
cellValue = String.valueOf(cell.getCellFormula());
break;
case BLANK: // 空值
cellValue = "";
break;
case ERROR: // 故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
}
}
/**
*
* 读取单元格(包含公式值)
* @param cell
* @param format
* @param workbook
* @return
*/
private static String getValueE(Cell cell, String format,Workbook workbook) {
String cellValue = "";
switch (cell.getCellTypeEnum()) {
case NUMERIC: // 数字
if(DateUtil.isCellDateFormatted(cell)){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
cellValue=sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
}else{
DecimalFormat df = new DecimalFormat(format);
cellValue=df.format(cell.getNumericCellValue());
// cellValue=String.valueOf(cell.getNumericCellValue());
}
break;
case STRING: // 字符串
cellValue = String.valueOf(cell.getStringCellValue());
break;
case BOOLEAN: // Boolean
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case FORMULA: // 公式
FormulaEvaluator formulaEvaluator = null;
formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
double shu=formulaEvaluator.evaluate(cell).getNumberValue();
DecimalFormat df = new DecimalFormat("0.##");
cellValue=df.format(shu);
break;
case BLANK: // 空值
cellValue = "";
break;
case ERROR: // 故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
}
写
package com.docc.util;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.ss.usermodel.HorizontalAlignment;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import com.docc.model.Inventory;
import com.docc.model.InventorySearch;
public class WriteInventory {
/**
*
* 按条件清单导出
*/
public void exportInventory(HttpServletRequest request, HttpServletResponse response,List<Inventory> inventorylist) throws Exception{
// 第一步，创建一个webbook，对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet("Excel文件下载");
// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow row = sheet.createRow((int) 0);
// 第四步，创建单元格，并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
HSSFCell cell = row.createCell((short) 0);
cell.setCellValue("清单号");
cell.setCellStyle(style);
cell = row.createCell((short) 1);
cell.setCellValue("财报id");
cell.setCellStyle(style);
cell = row.createCell((short) 2);
cell.setCellValue("组织机构代码");
cell.setCellStyle(style);
cell = row.createCell((short) 3);
cell.setCellValue("公司名称");
cell.setCellStyle(style);
cell = row.createCell((short) 4);
cell.setCellValue("报告年份");
cell.setCellStyle(style);
cell = row.createCell((short) 5);
cell.setCellValue("报告类型");
cell.setCellStyle(style);
cell = row.createCell((short) 6);
cell.setCellValue("录入人");
cell.setCellStyle(style);
cell = row.createCell((short) 7);
cell.setCellValue("审核人");
cell.setCellStyle(style);
cell = row.createCell((short) 8);
cell.setCellValue("是否发布");
cell.setCellStyle(style);
cell = row.createCell((short) 9);
cell.setCellValue("发布时间");
cell.setCellStyle(style);
cell = row.createCell((short) 10);
cell.setCellValue("未采集原因");
cell.setCellStyle(style);
cell = row.createCell((short) 11);
cell.setCellValue("是否有有息负债");
cell.setCellStyle(style);
cell = row.createCell((short) 12);
cell.setCellValue("清晰度");
cell.setCellStyle(style);
cell = row.createCell((short) 13);
cell.setCellValue("更名");
cell.setCellStyle(style);
cell = row.createCell((short) 14);
cell.setCellValue("备注");
cell.setCellStyle(style);
//写数据
for (int i = 0; i < inventorylist.size(); i++) {
row = sheet.createRow((int) i + 1);
Inventory user = (Inventory) inventorylist.get(i);
row.createCell((short) 0).setCellValue(user.getId());
row.createCell((short) 1).setCellValue(user.getFinancialid());
row.createCell((short) 2).setCellValue(user.getOrgcode());
row.createCell((short) 3).setCellValue(user.getCompany());
row.createCell((short) 4).setCellValue(user.getReportdate());
row.createCell((short) 5).setCellValue(user.getType());
row.createCell((short) 6).setCellValue(user.getUsername());
row.createCell((short) 7).setCellValue(user.getCheckname());
row.createCell((short) 8).setCellValue(user.getIsarchive());
row.createCell((short) 9).setCellValue(user.getArchivetime());
row.createCell((short) 10).setCellValue(user.getReason());
row.createCell((short) 11).setCellValue(user.getIshave());
row.createCell((short) 12).setCellValue(user.getDefinition());
row.createCell((short) 13).setCellValue(user.getNewname());
row.createCell((short) 14).setCellValue(user.getComments());
}
outWrite(request, response, wb, "compliance labeling.xls");
}
private static void outWrite(HttpServletRequest request, HttpServletResponse response, HSSFWorkbook wb,
String fileName) throws IOException {
OutputStream output = null;
try {
output = response.getOutputStream();
response.reset();
response.setHeader("Content-disposition", "attachment; fileName="+fileName);
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setCharacterEncoding("utf-8");
wb.write(output);
output.flush();
} catch (IOException e) {
e.printStackTrace();
}finally {
if(output != null){
output.close();
}
}
}
}
修改
修改之后是需要保存的,
FileOutputStream excelFileOutPutStream = new FileOutputStream(filename);
workbook.write(excelFileOutPutStream);
excelFileOutPutStream.flush();
excelFileOutPutStream.close();
模版copy
一个文件夹下的模版,copy到同文件夹下
XSSFWorkbook workbook = null;
XSSFSheet hssfSheet =null;
String savedir ="D:/1我的桌面/2桌面临时空间/";
String filename=savedir+company+reportyear+type+".xlsx";
File file = new File(filename);
Files.copy(Paths.get(savedir+"财务指标导出模板.xlsx"), new FileOutputStream(savedir+company+reportyear+type+".xlsx"));//新产生的文件名
workbook = new XSSFWorkbook(new FileInputStream(file));
hssfSheet = workbook.getSheetAt(0);
FileOutputStream excelFileOutPutStream = new FileOutputStream(filename);
workbook.write(excelFileOutPutStream);
excelFileOutPutStream.flush();
excelFileOutPutStream.close();
