POI Demo
POI API
http://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/Workbook.html
所需相关jar下载
commons-collections4-4.4.jar
commons-compress-1.19.jar
poi-4.1.1.jar
poi-ooxml-4.1.1.jar
poi-ooxml-schemas-4.1.1.jar
POI 中的CellType类型以及值的对应关系
CellType 类型 值 CELL_TYPE_NUMERIC 数值型 0 CELL_TYPE_STRING 字符串型 1 CELL_TYPE_FORMULA 公式型 2 CELL_TYPE_BLANK 空值 3 CELL_TYPE_BOOLEAN 布尔型 4 CELL_TYPE_ERROR 错误 5
POI读取Excel
Import Class
import java.io.FileInputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
根据Excel的地址读取Excel表格数据
/**
* @Title:getDataFromExcel
* @author:马家立
* @date:2019年11月19日 上午10:02:22
* @Description:TODO 根据Excel的地址读取Excel表格数据
* @param filePathExcel的绝对路径
* @return Map<String,Object> {end:ok或者error;msg:错误信息原因;counts:读取条数}
* @throws IOException
*/
public static Map<String, Object> getDataFromExcel(String filePath) throws IOException {
// 声明结果map
Map<String, Object> resultMap = new HashMap<String, Object>();
// 判断是否为excel类型文件
if (!filePath.endsWith(".xls") && !filePath.endsWith(".xlsx")) {
System.out.println("文件不是excel类型");
resultMap.put("end", "error");
resultMap.put("msg", "文件不是excel类型");
return resultMap;
}
// 声明文本输入流
FileInputStream fis = null;
// 声明一个新的工作簿
Workbook wookbook = null;
// 声明一个新的工作表
Sheet sheet = null;
try {
// 获取一个绝对地址的流
fis = new FileInputStream(filePath);
// 2003版本的excel，用.xls结尾, 2007版本以.xlsx
if (filePath.endsWith(".xls")) {
wookbook = new HSSFWorkbook(fis);// 得到工作簿
} else {
// XSSFWorkbook
wookbook = new XSSFWorkbook(fis);// 得到工作簿
}
// 得到第一个工作表
sheet = wookbook.getSheetAt(0);
// 得到第二个工作表
// sheet = wookbook.getSheetAt(1);
// 得到第三个工作表
// sheet = wookbook.getSheetAt(2);
// 封装处理Excel工作子表的数据
resultMap = packageDataBySheet(sheet);
} finally {
if (!QwyUtil.isNullAndEmpty(wookbook)) {
wookbook.close();
}
}
return resultMap;
}
封装处理Excel工作子表的数据
/**
* @Title:packageDataBySheet
* @author:马家立
* @date:2019年11月19日 上午9:55:26
* @Description:TODO 封装处理Excel工作子表的数据
* @param sheetExcel工作簿中的子表
* @return Map<String,Object> {end:ok或者error;msg:错误信息原因;counts:读取条数}
*/
public static Map<String, Object> packageDataBySheet(Sheet sheet) {
// 返回结果map
Map<String, Object> resultMap = new HashMap<String, Object>();
try {
resultMap.put("end", "ok");
// 获得表头
Row rowHead = sheet.getRow(0);
// 获取Excel的所有行数量
int rows = sheet.getLastRowNum();
// 获取Excel的所有列数量
int lines = rowHead.getPhysicalNumberOfCells();
if (0 == rows) {
System.out.println("Excel文件内没有数据！");
resultMap.put("end", "error");
resultMap.put("msg", "Excel文件内没有数据！");
return resultMap;
}
// 读取条数
int counts = 0;
// 是否跳过读取下一行
boolean isNext = false;
// 外圈循环读取所有行：获得所有行的数据
for (int i = 0; i <= rows; i++) {
// 是否跳过读取下一行:每次初始化为false
isNext = false;
counts++;
// 获得第i行对象
Row row = sheet.getRow(i);
// 获取单元格为空则直接下一行
if (isNullAndEmpty(row)) {
continue;
}
List<String> list = new ArrayList<>();
// 内圈循环读取所有列：遍历每一行的的数据,lineNum:列数
for (int j = 0; j < lines; j++) {
// 获取该单元格相应的类型的值
String str = getRightTypeCell(row.getCell(j), i, j);
// 如果第一列为空则直接读取下一行
if (isNullAndEmpty(str) && (0 == j)) {
isNext = true;
break;
}
list.add(str);
}
// 是否跳过读取下一行
if (isNext) {
continue;
}
String str1 = list.get(0); // 参数1
// String str2 = list.get(1); // 参数2
// String str3 = list.get(2); // 参数3
// and so on...
if (i == 0) {
if ("str1".endsWith(str1)) {
System.out.println("读取的排课Excel数据格式正确");
} else {
resultMap.put("end", "error");
resultMap.put("msg", "读取的排课Excel数据格式错误！");
System.err.println("读取的排课Excel数据格式错误");
break;
}
} else {
/**
* 处理数据
*/
}
}
resultMap.put("counts", counts + "");
} catch (Exception e) {
e.printStackTrace();
resultMap.put("end", "error");
resultMap.put("msg", "OperationExcel的packageDataBySheet方法异常！");
}
return resultMap;
}
返回该单元格相应的类型的值
/**
* @Title:getRightTypeCell
* @author:马家立
* @date:2020年6月26日 上午11:55:55
* @Description:TODO 返回该单元格相应的类型的值
* @param cell--一个单元格的对象
* @param rowNum--行数
* @param lineNum--列数
* @return String
* @throws Exception
*/
public static String getRightTypeCell(Cell cell, int rowNum, int lineNum) throws Exception {
// 单元格内容
String result = "";
System.out.println("rowNum:" + rowNum + "\tlineNum:" + lineNum);
// 如果单元格为空或者单元格里面的数据为空则返回
if ((cell == null) || cell.equals(null) || (CellType.BLANK == cell.getCellType())) {
result = "";
} else {
// 判断数据类型
switch (cell.getCellType()) {
case BLANK:
result = cell.getStringCellValue();
break;
case BOOLEAN:
result = String.valueOf(cell.getBooleanCellValue());
break;
case ERROR:
result = String.valueOf(cell.getErrorCellValue());
break;
case FORMULA:
result = cell.getCellFormula();
break;
case NUMERIC:
result = String.valueOf(cell.getNumericCellValue());
/**
* --#与0的区别{#：没有则为空；0：没有则补0}
*/
// 没有则自动补.00--100则为100.00;100.00则为100.00;1.05则为1.05;1.5则为1.50
// result = new DecimalFormat("0.00").format(cell.getNumericCellValue());
// 100则为100;100.00则为100;1.05则为1.05;1.5则为1.5
result = new DecimalFormat("0.##").format(cell.getNumericCellValue());
break;
case STRING:
result = cell.getRichStringCellValue().getString();
break;
default:
result = cell.getStringCellValue();
break;
}
}
return result;
}
校验对象是否为空
/**
* @Title:isNullAndEmpty
* @author:马家立
* @date:2019年11月19日 上午10:23:49
* @Description:TODO 校验对象是否为空
* @param obj校验对象
* @return boolean
*/
public static boolean isNullAndEmpty(Object obj) {
if ((null != obj) && !"".equals(obj.toString()) && !"null".equals(obj)) {
return false;
} else {
return true;
}
}
main方法测试
public static void main(String[] args) {
try {
// 读取文件地址
String file_path = "D:\\123.xlsx";
String end;
// 传路径是为了方法灵活
Map<String, Object> map = getDataFromExcel(file_path);
end = map.get("end") + "";
System.out.println("封装处理结果:" + end);
} catch (Exception e) {
e.printStackTrace();
}
}
POI导出Excel
maven项目引用POI的jar
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<!-- 引入后XSSFWorkbook依旧无法使用的话，需要继续引用这个jar包 -->
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
Import Class
import java.awt.Color;
import java.io.File;
import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
POI-Excel默认样式
/**
* @Title:getPOIStyle
* @Description:TODO 获取POI-Excel默认样式
* @author:马家立
* @date:2020-7-6 11:33:21
* @param workbook
* @return XSSFCellStyle
* @throws Exception
*/
public static XSSFCellStyle getPOIStyle(XSSFWorkbook workbook) throws Exception {
XSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER); // 居中
style.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直
// 加粗字体
XSSFFont font = workbook.createFont();
// 字体大小
font.setFontHeight(12);
// 字体颜色
//font.setColor(new XSSFColor(Color.green));
//font.setColor(new XSSFColor(new Color(9, 99, 9)));//rgb颜色
// 字体加粗
// font.setBold(true);
style.setFont(font);
return style;
}
POI-Excel标题样式
/**
* @Title:getPOITitleStyle
* @Description:TODO 获取POI-Excel标题样式
* @author:马家立
* @date:2020-7-6 11:34:12
* @param workbook
* @return XSSFCellStyle
* @throws Exception
*/
public static XSSFCellStyle getPOITitleStyle(XSSFWorkbook workbook) throws Exception {
XSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER); // 居中
style.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直
// 加粗字体
XSSFFont font = workbook.createFont();
// 字体大小
font.setFontHeight(15);
// 字体颜色
// font.setColor(Font.COLOR_RED);
// 字体加粗
font.setBold(true);
style.setFont(font);
return style;
}
创建单元格,写入数据
/**
* @Title:creatCell
* @Description:TODO 创建单元格,写入数据
* @author:马家立
* @date:2020-7-6 12:03:54
* @param xssfRow--行
* @param content--内容
* @param line--第几列
* @param style--样式
* @throws Exception
* void
*/
public static void creatCell(XSSFRow xssfRow, String content, int line, XSSFCellStyle style) throws Exception {
// 创建列
XSSFCell cell = xssfRow.createCell(line);
if (null!=content&&!"".equals(content)) {
// 写入内容
cell.setCellValue(content);
}
// 设置样式
cell.setCellStyle(style);
}
POI-Excel写入内容并生成文件
/**
* @Title:writeExcel
* @Description:TODO Excel写入内容并生成文件
* @author:马家立
* @date:2020-7-6 11:43:51
* @param filePath--文件路径(C:\\Users\\userName\\Desktop\\")
* @param fileName--文件名字,带后缀(POIExcel.xls)
* @throws Exception
* void
*/
public void writeExcel(String filePath,String fileName) throws Exception {
XSSFWorkbook workbook = null;
FileOutputStream fout = null;
try {
// 路径若为空则默认为桌面
if(null==filePath||"".equals(filePath)) {
filePath = "C:\\Users\\"+System.getenv().get("USERNAME")+"\\Desktop\\";
}
// 文件名若为空则默认为POIExcel.xls
if(null==fileName||"".equals(fileName)) {
fileName = "POIExcel.xls";
}
/**
* -- 第一步：创建一个webbook，对应一个Excel文件
*/
workbook = new XSSFWorkbook();
/**
* -- 第二步：在webbook中添加一个sheet,对应Excel文件中的sheet
*/
XSSFSheet sheet = workbook.createSheet("子表名");
/**
* --第三步：声明格式写入内容
*/
// 声明标题样式
XSSFCellStyle titleStyle = getPOITitleStyle(workbook);
// 声明内容样式
XSSFCellStyle contentStyle = getPOIStyle(workbook);
// 合并单元格格式：起始行号，终止行号， 起始列号，终止列号
CellRangeAddress region = new CellRangeAddress(0, 0, 0, 2);
sheet.addMergedRegion(region);
// 第一行写入数据
int row = 0;
XSSFRow xssfRow = sheet.createRow(row);
creatCell(xssfRow, "第一行第一列", 0, titleStyle);
// 第二行写入数据
row++;
xssfRow = sheet.createRow(row);
creatCell(xssfRow, "第二行第一列", 0, contentStyle);
creatCell(xssfRow, "第二行第二列", 1, contentStyle);
creatCell(xssfRow, "第二行第三列", 2, contentStyle);
// 设置每一列的宽度为自适应
int lines = 3;
for (int i = 0; i < lines; i++) {
sheet.autoSizeColumn(i, true);
sheet.setColumnWidth(i, (sheet.getColumnWidth(i) * 17) / 10);
}
/**
* --创建多个表
*/
XSSFSheet sheet2 = workbook.createSheet("子表名2");
// 合并单元格格式：起始行号，终止行号， 起始列号，终止列号
sheet2.addMergedRegion(region);
// 第一行写入数据
row = 0;
xssfRow = sheet2.createRow(row);
creatCell(xssfRow, "第一行第一列222", 0, titleStyle);
// 第二行写入数据
row++;
xssfRow = sheet2.createRow(row);
creatCell(xssfRow, "第二行第一列222", 0, contentStyle);
creatCell(xssfRow, "第二行第二列222", 1, contentStyle);
creatCell(xssfRow, "第二行第三列222", 2, contentStyle);
// 设置每一列的宽度为自适应
lines = 3;
for (int i = 0; i < lines; i++) {
sheet2.autoSizeColumn(i, true);
sheet2.setColumnWidth(i, (sheet.getColumnWidth(i) * 17) / 10);
}
/**
* --第四步：创建File文件,把workbook写入File
*/
File file = new File(filePath+fileName);
if (!file.exists()) {
// 文件不存在则创建新文件
file.createNewFile();
fout = new FileOutputStream(file);
workbook.write(fout);
} else {
// 文件存在则覆盖写入数据
fout = new FileOutputStream(file);
workbook.write(fout);
}
} finally {
if (null != fout) {
fout.close();
}
if (null != workbook) {
workbook.close();
}
}
}
main函数POI方式导出Excel
public static void main(String[] args) {
POIExcel poiExcel = new POIExcel();
try {
// 测试POI写入Excel
poiExcel.writeExcel(null, null);
System.out.println("ok");
} catch (Exception e) {
e.printStackTrace();
}
}
POI 工具方法
POI-Excel宽度自适应
public static void autoColumn(XSSFSheet sheet){ int maxColumn = sheet.getRow(0).getPhysicalNumberOfCells(); for(int i = 0; i < maxColumn; i++){ sheet.autoSizeColumn(i); } for(int columnNum = 0; columnNum <= maxColumn; columnNum++){ int columnWidth = sheet.getColumnWidth(columnNum) / 256; for(int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++){ Row currentRow; if(sheet.getRow(rowNum) == null){ currentRow = sheet.createRow(rowNum); }else{ currentRow = sheet.getRow(rowNum); } if(currentRow.getCell(columnNum) != null){ Cell currentCell = currentRow.getCell(columnNum); try { int length = currentCell.toString().getBytes("GBK").length; if(columnWidth < length + 1){ columnWidth = length + 8; } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } } sheet.setColumnWidth(columnNum, columnWidth * 256); } }
POI 使用输出流向前端返回Excel文件
poi向前端返回excel，有两种方法，先介绍简单粗暴的，就是最常用的直接用流向浏览器返回文件，比较简单粗暴，前端直接使用get方法即可，主要是后台的操作。 前端页面： window.location.href="这里写路径，有参数的话也可以带上"; 后端java代码，注意一下火狐浏览器的文件名的特殊处理：/** * 下载材料excel方法1，该方法暂时没启用 2020年3月12日 14:33:26 * excel * @param req * @param res */ @RequestMapping(method={RequestMethod.GET, RequestMethod.POST,RequestMethod.HEAD},value="getMaterialsExcel",produces="application/json") public void getMaterialsExcel(HttpServletRequest req,HttpServletResponse res){ ServletOutputStream sos = null; try{ logger.debug("************************开始下载材料excel*******************"); // 不用分页了，注意要带上过滤条件 Map<String, Object> param = new HashMap<String,Object>(); // 查询的过滤条件 param.put("productName", req.getParameter("productName")); param.put("model", req.getParameter("model")); param.put("productStatus", req.getParameter("productStatus")); res.setContentType("application/json"); res.setCharacterEncoding("utf-8"); // service中得到具体业务相关的HSSFWorkbook HSSFWorkbook wb = downloadExcelService.getMaterialsExcel(param); String fileName = "测试测试.xls"; String agent = req.getHeader("USER-AGENT").toLowerCase(); String uncodeFileNameString = ""; if(agent.contains("firefox")){ // 火狐 uncodeFileNameString = "=?UTF-8?B?" + (new String(Base64Utils.encodeToString(fileName.getBytes("UTF-8")))) + "?="; logger.debug("火狐浏览器"); }else{ // 其他 uncodeFileNameString = URLEncoder.encode(fileName, "UTF-8"); uncodeFileNameString = uncodeFileNameString.replace("+", "%20"); // 空格被转为了 "+" ，要转成utf-8的空格符号 "%20" logger.debug("不是火狐浏览器"); } res.setContentType("application/octet-stream;charset=UTF-8"); res.setHeader("Content-disposition", "attachment; filename=" + uncodeFileNameString); sos = res.getOutputStream(); wb.write(sos); }catch(Exception e){ e.printStackTrace(); logger.error("下载材料excel时异常: ", e); }finally{ if(sos != null){ try { // sos是字节流，不用flush了，直接close sos.close(); } catch (IOException e) { e.printStackTrace(); logger.error("未正确关闭sos"); } } } }
I have a dream : Sandy beach B-J-N.
