在此之前打开POI分类已存在三篇文章介绍了POI的使用及流下载，近期项目发现之前的使用中是有缺陷的，今天做一下总结，从现在起我们可以忽略前三篇文章对POI的使用，但是流下载方式还是可以参考的，这里还是再提一笔，流下载使用ajax会出异常，不生效，我是用的是提交form表单的方式来进行流下载，很遗憾，暂时取不到返回值
下面我们继续谈一下POI的使用
SSFWorkbook和XSSFWorkbook，但它们实现了同一个接口Workbook,这对我们的编程实现提供了一定的灵活性
XSSFWorkbook即可，HSSFWorkbook是存在一定缺陷，之前使用过HSSFWorkbook，数据量达到65536就会报错导出失败，近期做新的项目才发现原因
相对于导入工作，需要动态对文件识别Excel2003或是Excel2007+,有些文章写到通过后缀名来辨别，这存在一个问题，用户修改后缀之后，结果可想而知，幸亏POI提供了一个便利的方法Workbook create(InputStream inp)，通过输入流动态创建，GOOD！！！下面，我也会提供相关的用法。在这里还要给一个提示，使用3.9版本这种方法报错，在此，我来提供一个maven info
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.15</version>
</dependency>没错，这三个包都是需要的，正是为了实现这个功能，我对此封装了一个POI的工具包
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
public class PoiUtil {
private PoiUtil() {
}
/**
* Excel2003和Excel2007+创建方式不同
* Excel2003使用HSSFWorkbook 后缀xls
* Excel2007+使用XSSFWorkbook 后缀xlsx
* 此方法可保证动态创建Workbook
*
* @param is
* @return
*/
public static Workbook createWorkbook(InputStream is) throws IOException, InvalidFormatException {
return WorkbookFactory.create(is);
}
/**
*导入数据获取数据列表
* @param wb
* @return
*/
public static List<List<Object>> getDataList(Workbook wb) {
List<List<Object>> rowList = new ArrayList<>();
for (int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) {
Sheet sheet = wb.getSheetAt(sheetNum);
for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
if (null == row)
continue;
List<Object> cellList = new ArrayList<>();
for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
Cell cell = row.getCell(j);
cellList.add(getCellValue(cell));
}
rowList.add(cellList);
}
}
return rowList;
}
private static String getCellValue(Cell cell) {
String cellvalue = "";
if (cell != null) {
// 判断当前Cell的Type
switch (cell.getCellType()) {
// 如果当前Cell的Type为NUMERIC
case HSSFCell.CELL_TYPE_NUMERIC: {
short format = cell.getCellStyle().getDataFormat();
if (format == 14 || format == 31 || format == 57 || format == 58) { //excel中的时间格式
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
double value = cell.getNumericCellValue();
Date date = DateUtil.getJavaDate(value);
cellvalue = sdf.format(date);
}
// 判断当前的cell是否为Date
else if (HSSFDateUtil.isCellDateFormatted(cell)) { //先注释日期类型的转换，在实际测试中发现HSSFDateUtil.isCellDateFormatted(cell)只识别2014/02/02这种格式。
// 如果是Date类型则，取得该Cell的Date值 // 对2014-02-02格式识别不出是日期格式
Date date = cell.getDateCellValue();
DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
cellvalue = formater.format(date);
} else { // 如果是纯数字
// 取得当前Cell的数值
cellvalue = NumberToTextConverter.toText(cell.getNumericCellValue());
}
break;
}
// 如果当前Cell的Type为STRIN
case HSSFCell.CELL_TYPE_STRING:
// 取得当前的Cell字符串
cellvalue = cell.getStringCellValue().replaceAll("'", "''");
break;
case HSSFCell.CELL_TYPE_BLANK:
cellvalue = null;
break;
// 默认的Cell值
default: {
cellvalue = " ";
}
}
} else {
cellvalue = "";
}
return cellvalue;
}
/**
* 此方法生成表头并写入表头名称
*
* @param nodes 节点
* @param sheet 工作簿
* @param style 单元格样式
* @return 数据加载开始行
*/
public static int generateHeader(List<HeaderNode> nodes, Sheet sheet, CellStyle style) {
Map<RowKey, Row> hssfRowMap = new HashMap<>();
int dataStartRow = 0;
for (HeaderNode node : nodes) {
if (!(node.firstRow == node.getLastCol() || node.getFirstCol() == node.getLastCol())) {
CellRangeAddress cra = new CellRangeAddress(node.getFirstRow(), node.getLastRow(),
node.getFirstCol(), node.getLastCol());
sheet.addMergedRegion(cra);
}
dataStartRow = dataStartRow >= node.getLastRow() ? dataStartRow : node.getLastRow();
RowKey key = new RowKey();
key.setFirstRow(node.getFirstRow());
key.setLastRow(node.getLastRow());
Row row = hssfRowMap.get(key);
if (null == row) {
row = sheet.createRow(node.getFirstRow());
hssfRowMap.put(key, row);
}
Cell cell = row.createCell(node.getFirstCol());
cell.setCellValue(node.getName());
cell.setCellStyle(style);
}
return dataStartRow+1;
}
public static class HeaderNode {
private String name;
private int firstRow;
private int lastRow;
private int firstCol;
private int lastCol;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getFirstRow() {
return firstRow;
}
public void setFirstRow(int firstRow) {
this.firstRow = firstRow;
}
public int getLastRow() {
return lastRow;
}
public void setLastRow(int lastRow) {
this.lastRow = lastRow;
}
public int getFirstCol() {
return firstCol;
}
public void setFirstCol(int firstCol) {
this.firstCol = firstCol;
}
public int getLastCol() {
return lastCol;
}
public void setLastCol(int lastCol) {
this.lastCol = lastCol;
}
}
private static class RowKey {
private int firstRow;
private int lastRow;
public int getFirstRow() {
return firstRow;
}
public void setFirstRow(int firstRow) {
this.firstRow = firstRow;
}
public int getLastRow() {
return lastRow;
}
public void setLastRow(int lastRow) {
this.lastRow = lastRow;
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (!(o instanceof RowKey)) return false;
RowKey key = (RowKey) o;
return firstRow == key.firstRow &&
lastRow == key.lastRow;
}
@Override
public int hashCode() {
return Objects.hash(firstRow, lastRow);
}
}
public static void main(String[] args) {
// 第一步，创建一个webbook，对应一个Excel文件
Workbook workbook = new XSSFWorkbook();
// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
Sheet sheet = workbook.createSheet("测试");
// 第四步，创建单元格，并设置值表头 设置表头居中
CellStyle style = workbook.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER); // 水平居中格式
style.setVerticalAlignment(VerticalAlignment.CENTER); //垂直居中
List<HeaderNode> nodes = new ArrayList<>();
HeaderNode headerNode1 = new HeaderNode();
headerNode1.setName("test1");
headerNode1.setFirstRow(0);
headerNode1.setLastRow(1);
headerNode1.setFirstCol(0);
headerNode1.setLastCol(5);
nodes.add(headerNode1);
HeaderNode headerNode34 = new HeaderNode();
headerNode34.setName("test4");
headerNode34.setFirstRow(3);
headerNode34.setLastRow(4);
headerNode34.setFirstCol(0);
headerNode34.setLastCol(5);
nodes.add(headerNode34);
HeaderNode headerNode2 = new HeaderNode();
headerNode2.setName("test2");
headerNode2.setFirstRow(2);
headerNode2.setLastRow(2);
headerNode2.setFirstCol(0);
headerNode2.setLastCol(4);
nodes.add(headerNode2);
HeaderNode headerNode3 = new HeaderNode();
headerNode3.setName("test3");
headerNode3.setFirstRow(2);
headerNode3.setLastRow(2);
headerNode3.setFirstCol(5);
headerNode3.setLastCol(5);
nodes.add(headerNode3);
generateHeader(nodes, sheet, style);
try {
FileOutputStream output = new FileOutputStream("e:\\workbook.xls");
workbook.write(output);
output.flush();
} catch (IOException e) {
e.printStackTrace();
}
}
}
下面是导出报表及流下载的代码
List<PoiUtil.HeaderNode> nodes = new ArrayList<>();
for (RpStyleItem item : styleItems){
PoiUtil.HeaderNode headerNode = new PoiUtil.HeaderNode();
headerNode.setFirstRow(item.getRow() - 1);
headerNode.setLastRow(headerNode.getFirstRow()+item.getSizeY() - 1);
headerNode.setFirstCol(item.getCol() - 1);
headerNode.setLastCol(headerNode.getFirstCol() + item.getSizeX() - 1);
headerNode.setName(item.getName());
nodes.add(headerNode);
}
// 第一步，创建一个webbook，对应一个Excel文件
// XSSFWorkbook针对于excel2007+
// HSSFWorkbook针对于Excel2003(数据超过65536会报错)
Workbook workbook = new XSSFWorkbook();
// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
Sheet sheet = workbook.createSheet(template.getName());
// 第三步，创建单元格样式
CellStyle style = workbook.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER); // 水平居中格式
style.setVerticalAlignment(VerticalAlignment.CENTER); //垂直居中
int rowNum = PoiUtil.generateHeader(nodes,sheet,style);
String sql = template.getReportSql().replace("\n", " ");
String reportDate = com.jrq.components.date.DateUtil.dateFormat(new Date(), "yyyyMMdd");
//测试数据 String reportDate = "20170711";
List<Map<String, Object>> datas = reportService.listReportData(reportDate, sql);
for (Map<String, Object> map : datas){
Collection<Object> values = map.values();
int index = 0; //cell单元格索引
Row row = sheet.createRow(rowNum++);
for (Object obj : values){
row.createCell(index++).setCellValue(obj.toString());
}
}
/* 直接将将文件保存在本地，供测试样式使用
try {
OutputStream output=new FileOutputStream("e:\\workbook.xls");
workbook.write(output);
output.flush();
output.close();
} catch (IOException e) {
e.printStackTrace();
}*/
String downFileName = new String(template.getType().getName()+reportDate+".xls");
try {
//若不进行编码在IE下会乱码
downFileName = URLEncoder.encode(downFileName, "UTF-8");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
try {
// 清空response
response.reset();
response.setContentType("application/msexcel");//设置生成的文件类型
response.setCharacterEncoding("UTF-8");//设置文件头编码方式和文件名
response.setHeader("Content-Disposition", "attachment; filename=" + downFileName);
OutputStream os=response.getOutputStream();
workbook.write(os);
os.flush();
os.close();
} catch (IOException e) {
LOG.error("文件流操作异常");
jr.setRet("文件流操作异常");
return jr;
}
jr.setSuc(JsonResponse.SUCCESS);
return jr;
下面是导入功能的代码
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
MultipartFile file = multipartRequest.getFile("fileUpload");
if (!file.isEmpty()) {
Workbook workbook = PoiUtil.createWorkbook(file.getInputStream());
if (null != workbook) {
List<List<Object>> list = PoiUtil.getDataList(workbook);}}可以看到这里是前端上传了excel文件这里，前端的HTML也贴一下
<form method="POST" enctype="multipart/form-data" id="form1"
action="/center/addition/list/uploadfile">
<input class="typeahead scrollable" type="file" id="fileUpload"
name="fileUpload"/>
<input type="submit" value="上传" id="btn_submit"/>
</form>
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
