文章目录
- 系列文章目录
- 前言
- 一、是什么?
- 二、使用步骤
- 1.引入库
- 2.读入数据
- 总结
前言
在实际系统开发中, 有蛮多的场景涉及到Excel的导出, 例如报销单据的导出, 客户列表的导出等等.其中比较头疼的部分就是各种单元格式的调整了, 故以此文做记录, 以便后续使用少花时间, 也希望能帮助到需要的人
一、是什么? 二、使用步骤 1.引入库2.业务代码示例 创建一个sheet, sheet名字不能重复, 如已存在,则名字后+(n)3.3.0 cn.afterturn easypoi-base${easypoi.version} cn.afterturn easypoi-web${easypoi.version} cn.afterturn easypoi-annotation${easypoi.version}
HSSFSheet sheet1;
if (workbook.getSheet("sheetName") == null){
sheet1 = workbook.createSheet(sheetName);
} else {
sheetName++;
sheet1 = workbook.createSheet(sheetName + "(" + sheetName + ")");
}
合并单元格
//合并单元格 起始行,结束行,起始列,结束列 CellRangeAddress callRangeAddress = new CellRangeAddress(2,2,0,6);对指定单元格计算求和, 并保留两位小数
String sumString;//求和公式
for (int i = 8; i < 9 + list.size(); i++) {// 我从第二列开始每列都执行求和操作 因此i=2
Cell cell = cell3Row.createCell(i);// 创建单元格
String colString = CellReference.convertNumToColString(i); //长度转成ABC列
if (list.size() < 2){
sumString = "TEXT(SUM(IF(" + colString + "5<>""," + colString + "5,0)+" + "0,0),"0.00")";
}
else {
sumString = "TEXT(SUM(IF(" + colString + "5<>""," + colString + "5,0)";
for (int j=0; j""," + colString + (5 + j + 1) + ",0)";
}
sumString = sumString + "),"0.00")";
}
cell.setCellFormula(sumString);// 把公式塞入合计列
cell.setCellStyle(cellStyleLeft);//居左样式
}
将指定单元格的数字转化为大写人民币金额
Cell cell = cellRow.createCell(8);// 创建单元格
String colString = CellReference.convertNumToColString(8+list.size()); //长度转成ABC列
String sumString = ""RMB"&IF(" + colString + (5 + list.size()) + "=0,"",TEXT(" + colString + (5 + list.size()) + ","[DBNUM2]")&"元整")";
cell.setCellFormula(sumString);// 把公式塞入合计列
人民币符号+金额的拼接, 如:¥1.00
Cell cellFinal = cell4Row.createCell(5);
cellFinal.setCellFormula(""¥"&" + colString + (5 + list.size()));
下面是方法中部分代码的示例
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.ss.util.CellRangeAddress;
public void exportRecords(ExportDTO exportDTO, HttpServletResponse response) throws IOException {
if (null == exportDTO) {
return;
}
OutputStream out = new BufferedOutputStream(response.getOutputStream());
String fileName = name + "_记录表_" + startDay.replace("-", "") + "-" + endDay.replace("-", "");//此处示例导出文件的名字是: 名字_记录表_20220501-20220601
fileName = URLEncoder.encode(fileName, "UTF-8").replace("\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
HSSFWorkbook workbook = new HSSFWorkbook();
CellStyle cs = workbook.createCellStyle();
//设置自动换行
cs.setWrapText(true);
//表格中的文字居中展示
cs.setAlignment(HorizontalAlignment.CENTER);
cs.setVerticalAlignment(VerticalAlignment.CENTER);
HSSFSheet sheet1 = workbook.createSheet();
//单元格的默认宽度
sheet1.setDefaultColumnWidth(12);
int rowNum = 0;
//创建行
Row titleRow = sheet1.createRow(rowNum);
//在第一行创建列
Cell titleCell = titleRow.createCell(0);
String head = "统计时间:" + startDay + "至" + endDay;
titleCell.setCellValue(head);
合并单元格
CellRangeAddress titleAddress = new CellRangeAddress(titleRow.getRowNum(), titleRow.getRowNum(), 0, 5);
sheet1.addMergedRegion(titleAddress);
try {
workbook.write(out);
ResponseEntity.ok().body(out);
} finally {
out.close();
}
}


