poi在日常的导入导出中是比较常用到的,最近也总结了下接触到的poi相关的导入导出的一些代码,有问题可以指出:
package com.poi;
import km.org.apache.poi.hssf.usermodel.*;
import km.org.apache.poi.hssf.util.HSSFColor;
import km.org.apache.poi.ss.util.CellRangeAddress;
import km.org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.ss.usermodel.*;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* @Author: zhangximing
* @Email: 530659058@qq.com
* @Date: 2022/10/17 11:07
* @Description: poi导入导出excel
*/
public class operateExcel {
/**
* 导入excel
* @param file
*/
public void importExcel(File file){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
// 获取文件输入流,读取文件
Workbook workbook = null;
try {
workbook = WorkbookFactory.create(new FileInputStream(file));
} catch (IOException e) {
e.printStackTrace();
}
// 读取第一个工作表,校验sheet文件是否合规。
Sheet sheet = checkSheet(workbook);
if(null!=sheet){
//获取第一行
Row firstRow = sheet.getRow(0);
//获取总行数 若出现缺少一行的情况则用sheet.getPhysicalNumberOfRows();
int totalRowNum = sheet.getLastRowNum();
//获取总列数
int totalCellNum = firstRow.getLastCellNum();
// 遍历行
for (int i = 0; i <=totalRowNum; i++) {
Row row = sheet.getRow(i);
//遍历列
System.out.print("第"+(i+1)+"行:");
for (int j = 0; j <=totalCellNum; j++){
String value = "";
if (null != row.getCell(j)){
//单独过滤序号
if (j > 0){
value = getCellDate(row.getCell(j),sdf);
}else{
value = row.getCell(j).toString().trim();
}
}
System.out.print(value);
System.out.print(" ");
}
System.out.println();
}
}else{
System.out.println("excel格式不合法");
}
}
/**
* 导出excel
* @param dataList 数据集合
* @param title excel内标题
* @param exportPath 导出路径(文件路径)
*/
public void exportExcel(List<List<String>> dataList, String title, String exportPath){
HSSFWorkbook wb = new HSSFWorkbook();
// 创建一个Excel的Sheet
HSSFSheet sheet = wb.createSheet("first sheet");
// 一般项目data的值需要校验是否为空,这边直接当做不为空处理
// 行数
int rowLen = dataList.size();
// 列数
List<String> btDataList = dataList.get(0);
int cellLen = btDataList.size();
//=========设置首行为标题,多列合并==========
// 设置行-下面为第一行
HSSFRow row0 = sheet.createRow(0);
// 设置高度
row0.setHeight((short)500);
// 设置列-下面为第一行的第一列
HSSFCell cell00 = row0.createCell(0);
// 设置大标题字体
HSSFFont headfont = setHSSFFontHt(wb,22);
// 设置样式
HSSFCellStyle headstyle = wb.createCellStyle();
// 使用了上面设置的字体样式
headstyle.setFont(headfont);
headstyle.setLocked(true);
// 设置样式
setCellCommonStyle(headstyle,0);
// 合并单元格:参数说明:1:开始行 2:结束行 3:开始列 4:结束列
// 注意,在这里使用表字段名-1来作结束列,因为我们是从0开始数的,所以要减去一个
CellRangeAddress range = new CellRangeAddress(0, 0, 0, cellLen - 1);
// 将表的合并单元格样式设置进去
sheet.addMergedRegion(range);
// 标题单元格样式
RegionUtil.setBorderTop(HSSFCellStyle.BORDER_THIN, range, sheet,wb);
RegionUtil.setBorderRight(HSSFCellStyle.BORDER_THIN, range, sheet,wb);
RegionUtil.setBorderLeft(HSSFCellStyle.BORDER_THIN, range, sheet,wb);
// 设置表的第一行的第一列的样式
cell00.setCellStyle(headstyle);
// 设置大标题
cell00.setCellValue(title);
//=========设置第二行为表头=========
HSSFRow row1 = sheet.createRow(1);
// 设置字体
HSSFFont columnHeadFont = setHSSFFontSt(wb,10);
// 字体加粗
columnHeadFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 列头的样式
HSSFCellStyle columnHeadStyle = wb.createCellStyle();
// 设置上面已经设置好的字体样式
columnHeadStyle.setFont(columnHeadFont);
columnHeadStyle.setLocked(true);
// 设置样式
setCellCommonStyle(columnHeadStyle,0);
// 设置第二行的行高
row1.setHeight((short) 750);
// 设置表头的值
for (int i=0;i<cellLen;i++){
HSSFCell cell1 = row1.createCell(i);
// 设置风格
cell1.setCellStyle(columnHeadStyle);
// 设置值
cell1.setCellValue(btDataList.get(i));
}
//=========设置内容=========
HSSFFont font = setHSSFFontSt(wb,10);
HSSFCellStyle style = wb.createCellStyle();
style.setFont(font);
setCellCommonStyle(style,1);
for (int i=1;i<rowLen;i++){
HSSFRow row = sheet.createRow(i+1);
//遍历数据并填充
List<String> nrDataList = dataList.get(i);
for (int j=0;j<nrDataList.size();j++){
HSSFCell cell = row.createCell(j);
cell.setCellValue(nrDataList.get(j));
cell.setCellStyle(style);
}
}
//设置默认宽度
autoColumn(sheet);
try {
System.out.println("excel开始写入至"+exportPath);
ByteArrayOutputStream os = new ByteArrayOutputStream();
wb.write(os);
InputStream input = new ByteArrayInputStream(os.toByteArray());
// 测试导出到本地
writeToLocal(exportPath,input);
// 如果是网页请求可以返回如下:filename是导出时文件名称
// Response.ok(input).header("Content-disposition", "attachment;filename=" + new String(filename.getBytes("GB2312"), "ISO8859-1") + ".xls").header("Cache-Control", "no-cache").build();
System.out.println("excel完成写入"+exportPath);
}catch (Exception e) {
System.out.println("导出excel报错,错误信息为:" + e.getMessage());
}
}
/**
* 合法返回sheet 不合法返回null
* @param workBook
* @return
*/
public Sheet checkSheet(Workbook workBook){
if(null == workBook){
return null;
}
Sheet sheet = workBook.getSheetAt(0);
//得到sheet的最后一行是否等于0
int row = sheet.getLastRowNum();
if(0==row){
return null;
}
return sheet;
}
//设置字体(黑体)
public HSSFFont setHSSFFontHt(HSSFWorkbook wb,int fontSize){
HSSFFont headfont = wb.createFont();
headfont.setFontName("黑体");
// 字体大小
headfont.setFontHeightInPoints((short) fontSize);
return headfont;
}
//设置字体(宋体)
public HSSFFont setHSSFFontSt(HSSFWorkbook wb,int fontSize){
HSSFFont headfont = wb.createFont();
headfont.setFontName("宋体");
// 字体大小
headfont.setFontHeightInPoints((short) fontSize);
return headfont;
}
//获取表格时间转换
public String getCellDate(Cell cell, SimpleDateFormat sdf){
String cellValue = "";
//自定义类型或数字类型(可以转换日期)
if (cell.getCellType()==CellType.NUMERIC || cell.getCellType()==CellType.FORMULA){
Date date = cell.getDateCellValue();
cellValue = sdf.format(date);
}else if (cell.getCellType()==CellType.STRING){
cellValue = cell.toString().trim();
cellValue = cellValue.replaceAll("\\/", "-");
cellValue = cellValue.replace("年","-");
cellValue = cellValue.replace("月","-");
cellValue = cellValue.replace("日","");
cellValue = cellValue.replace("时","-");
cellValue = cellValue.replace("分","-");
cellValue = cellValue.replace("秒","");
}
return cellValue;
}
/**
* 设置样式
* @param style
* @param isContext 是否为内容 1是
*/
private void setCellCommonStyle(HSSFCellStyle style,int isContext){
//内容自动换行
style.setWrapText(true);
if (isContext == 1){
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
}else{
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
}
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setLeftBorderColor(HSSFColor.BLACK.index);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setRightBorderColor(HSSFColor.BLACK.index);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setTopBorderColor(HSSFColor.BLACK.index);
}
//设置默认列宽(支持自适应以及换行)
private void autoColumn(HSSFSheet sheet){
int maxColumn = sheet.getRow(0).getPhysicalNumberOfCells();
//先自适应
for(int i = 0; i < maxColumn; i++){
//该只能针对英文、数字列宽自适应
sheet.autoSizeColumn(i);
// 解决自动设置列宽中文失效的问题
sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);
}
//最大宽度,超过则自动换行,未超过则自适应
int maxWidth = 50;
//遍历列,做自适应加宽或换行
for(int columnNum = 0; columnNum < maxColumn; columnNum++){
int columnWidth = sheet.getColumnWidth(columnNum) / 256;
for(int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++){
HSSFRow currentRow;
if(sheet.getRow(rowNum) == null){
currentRow = sheet.createRow(rowNum);
}else{
currentRow = sheet.getRow(rowNum);
}
if(currentRow.getCell(columnNum) != null){
HSSFCell currentCell = currentRow.getCell(columnNum);
try {
int length = currentCell.toString().getBytes("GBK").length;
if(columnWidth < length + 1){
columnWidth = length + 8;
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
System.out.println("第"+(columnNum+1)+"列:"+columnWidth+",最大列宽:"+maxWidth);
if (columnWidth>maxWidth){
columnWidth = maxWidth;
}
sheet.setColumnWidth(columnNum, columnWidth * 256);
}
}
/**
* 将InputStream写入本地文件
* @param destination 写入本地目录
* @param input 输入流
* @throws IOException IOException
*/
public static void writeToLocal(String destination, InputStream input)
throws IOException {
int index;
byte[] bytes = new byte[1024];
FileOutputStream downloadFile = new FileOutputStream(destination);
while ((index = input.read(bytes)) != -1) {
downloadFile.write(bytes, 0, index);
downloadFile.flush();
}
input.close();
downloadFile.close();
}
public static void main(String[] args) {
operateExcel operateExcel = new operateExcel();
// 导入excel
operateExcel.importExcel(new File("C:\\Users\\53065\\Desktop\\外出登记台账.xlsx"));
List<List<String>> dataList = new ArrayList<>();
List<String> btDataList = new ArrayList<>();
btDataList.add("名称");
btDataList.add("毕业院校");
btDataList.add("薪资");
btDataList.add("工作地");
btDataList.add("简介");
dataList.add(btDataList);
List<String> nrDataList1 = new ArrayList<>();
nrDataList1.add("张三");
nrDataList1.add("xx名校1");
nrDataList1.add("12k");
nrDataList1.add("广州");
nrDataList1.add("");
dataList.add(nrDataList1);
List<String> nrDataList2 = new ArrayList<>();
nrDataList2.add("李四");
nrDataList2.add("xx名校2");
nrDataList2.add("19k");
nrDataList2.add("深圳");
nrDataList2.add("一名2021年毕业于xx名校2的工程师,爱好打游戏、冲浪,一起么?");
dataList.add(nrDataList2);
List<String> nrDataList3 = new ArrayList<>();
nrDataList3.add("王五");
nrDataList3.add("xx本科");
nrDataList3.add("8k");
nrDataList3.add("中山");
nrDataList3.add("没啥简介");
dataList.add(nrDataList3);
// 导出excel
// operateExcel.exportExcel(dataList,"名校毕业生调查","D://名校毕业生调查表.xlsx");
}
}
导入的excel:
导入打印结果:
第一次没进行某些日期转换时,可能出现纯数字的这种情况,设计出了以下方法:
//获取表格时间转换
public String getCellDate(Cell cell, SimpleDateFormat sdf){
String cellValue = "";
//自定义类型或数字类型(可以转换日期)
if (cell.getCellType()==CellType.NUMERIC || cell.getCellType()==CellType.FORMULA){
Date date = cell.getDateCellValue();
cellValue = sdf.format(date);
}else if (cell.getCellType()==CellType.STRING){
cellValue = cell.toString().trim();
cellValue = cellValue.replaceAll("\\/", "-");
cellValue = cellValue.replace("年","-");
cellValue = cellValue.replace("月","-");
cellValue = cellValue.replace("日","");
cellValue = cellValue.replace("时","-");
cellValue = cellValue.replace("分","-");
cellValue = cellValue.replace("秒","");
}
return cellValue;
}
但有个缺点,需要过滤掉不需要转换的数字类型,比如序号这些
修改后结果:
导出结果:
这里可以看到,在超过特定的宽度后内容会自动换行。
最后如果是放到服务器端,请求时需要返回HttpServletResponse的,看看下面返回:
return Response.ok(input).header("Content-disposition", "attachment;filename=" + new String(filename.getBytes("GB2312"), "ISO8859-1") + ".xls").header("Cache-Control", "no-cache").build();
有什么问题大家都可以指出,感谢学习(#.#)