Apache POI导出excel
2024-09-25 13:53:31
public String exportXls(HttpServletRequest request, HttpServletResponse response) {
try {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFRow row = null;
HSSFCell cell = null;
//建立新的sheet对象
HSSFSheet sheet = wb.createSheet("导出信息");//sheet的名称
sheet.setColumnWidth(0, 5*256);//设置第columnIndex+1列的列宽，单位为字符宽度的1/256
sheet.setColumnWidth(1, 30*256);
sheet.setColumnWidth(2, 20*256);
sheet.setColumnWidth(3, 10*256);
sheet.setColumnWidth(4, 10*256);
sheet.setColumnWidth(5, 10*256);
sheet.setColumnWidth(6, 10*256);
sheet.setColumnWidth(7, 10*256);
//标题样式
HSSFCellStyle titleStyle = wb.createCellStyle();
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
org.apache.poi.ss.usermodel.Font ztFont = wb.createFont();
ztFont.setItalic(false);
ztFont.setColor(org.apache.poi.ss.usermodel.Font.COLOR_NORMAL);
ztFont.setFontHeightInPoints((short) 16);
ztFont.setFontName("宋体");
ztFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
titleStyle.setFont(ztFont);
//----------------二级标题格样式----------------------------------
HSSFCellStyle titleStyle2 = wb.createCellStyle(); //表格样式
titleStyle2.setAlignment(HSSFCellStyle.ALIGN_LEFT);
titleStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
HSSFFont ztFont2 = wb.createFont();
ztFont2.setItalic(false); // 设置字体为斜体字
ztFont2.setColor(org.apache.poi.ss.usermodel.Font.COLOR_NORMAL);
ztFont2.setFontHeightInPoints((short)11); // 将字体大小设置为18px
ztFont2.setFontName("宋体"); // 字体应用到当前单元格上
titleStyle2.setFont(ztFont2);
//----------------------------------------------------------
//----------------单元格样式----------------------------------
HSSFCellStyle cellStyle = wb.createCellStyle(); //表格样式
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
HSSFFont cellFont = wb.createFont();
cellFont.setItalic(false); // 设置字体为斜体字
cellFont.setColor(org.apache.poi.ss.usermodel.Font.COLOR_NORMAL);
cellFont.setFontHeightInPoints((short)10); // 将字体大小设置为18px
cellFont.setFontName("宋体"); // 字体应用到当前单元格上
cellStyle.setFont(cellFont);
cellStyle.setWrapText(true);//设置自动换行
//----------------------------------------------------------
// ----------------------创建第一行---------------
// 在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
row = sheet.createRow(0);
// 创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
cell = row.createCell(0);
// 合并单元格CellRangeAddress构造参数依次表示 起始行，截至行，起始列， 截至列
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
// 设置单元格内容
cell.setCellValue("测试excel");
cell.setCellStyle(titleStyle);
// ----------------------------------------------
// ------------------创建第二行()---------------------
row = sheet.createRow(1); // 创建第二行
cell = row.createCell(0);
sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 1));
cell.setCellValue("入库单号:15203");
cell.setCellStyle(titleStyle2);
cell = row.createCell(2);
sheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 4));
cell.setCellValue("执行单号:14520");
cell.setCellStyle(titleStyle2);
cell = row.createCell(5);
sheet.addMergedRegion(new CellRangeAddress(1, 1, 5, 7));
String createDate = MapUtil.getRealValue(headInfo, "create_date", "");
cell.setCellValue("创建时间:2018-06-19 13:59:23");
cell.setCellStyle(titleStyle2);
//---------------------创建第三行-------------------------------------
row = sheet.createRow(2);
cell = row.createCell(0);
sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 1));
cell.setCellValue("供应商:京东");
cell.setCellStyle(titleStyle2);
cell = row.createCell(2);
sheet.addMergedRegion(new CellRangeAddress(2, 2, 2, 4));
cell.setCellValue("仓库名称:一号仓库");
cell.setCellStyle(titleStyle2);
cell = row.createCell(5);
sheet.addMergedRegion(new CellRangeAddress(2, 2, 5, 7));
cell.setCellValue("总金额:23.65");
cell.setCellStyle(titleStyle2);
// ------------------创建表头start---------------------
row = sheet.createRow(3); // 创建第四行
sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 0));
cell = row.createCell(0);
cell.setCellValue("序号");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(3, 3, 1, 1));
cell = row.createCell(1);
cell.setCellValue("物料名称");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(3, 3, 2, 2));
cell = row.createCell(2);
cell.setCellValue("物料编号");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(3, 3, 3, 3));
cell = row.createCell(3);
cell.setCellValue("物料产地");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(3, 3, 4, 4));
cell = row.createCell(4);
cell.setCellValue("入库价格");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(3, 3, 4, 4));
cell = row.createCell(5);
cell.setCellValue("参考价格");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(3, 3, 5, 5));
cell = row.createCell(6);
cell.setCellValue("需求数量");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(3, 3, 6, 6));
cell = row.createCell(7);
cell.setCellValue("总金额");
cell.setCellStyle(cellStyle);
//循环填充数据
int i =1;
int rowNum= 3;
List<Object> data = null;
for (Map m : mapList) {
data = new ArrayList<>();
data.add(i);
data.add((String) m.get("wuliao_name"));
data.add((String) m.get("wuliao_no"));
data.add((String) m.get("wuliao_address"));
data.add((Double) m.get("danjia"));
data.add((Double) m.get("reference_price"));
data.add((Integer) m.get("wuliao_count"));
data.add((Double) m.get("totalmoney"));
rowNum ++; //从第四行开始
row = sheet.createRow(rowNum);
for (int j = 0; j < data.size(); j++) { //将数据添加到单元格中
sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, j, j));
cell = row.createCell(j);
cell.setCellValue(""+data.get(j)+"");
cell.setCellStyle(cellStyle);
}
i++;
}
//添加底部合计行
row = sheet.createRow(rowNum+1);
sheet.addMergedRegion(new CellRangeAddress(rowNum+1, rowNum+1, 0, 4));
cell = row.createCell(0);
cell.setCellStyle(cellStyle);
cell = row.createCell(1);
cell.setCellStyle(cellStyle);
cell = row.createCell(2);
cell.setCellStyle(cellStyle);
cell = row.createCell(3);
cell.setCellStyle(cellStyle);
cell = row.createCell(4);
cell.setCellStyle(cellStyle);
cell = row.createCell(5);
cell.setCellStyle(cellStyle);
cell = row.createCell(6);
cell.setCellValue(totalNumber);
cell.setCellStyle(cellStyle);
cell = row.createCell(7);
cell.setCellValue(new BigDecimal(totalMoney).setScale(2, RoundingMode.DOWN).toString());
cell.setCellStyle(cellStyle);
OutputStream output = response.getOutputStream();
response.reset();
response.setHeader("Content-Disposition", "filename="
+ new String("测试导出".getBytes("gb2312"), "iso8859-1"))+".xls";//设置文件头编码方式和文件名
response.setContentType("application/msexcel");
wb.write(output);
output.close();
} catch (Exception e) {
e.printStackTrace();
}
}
最新文章
- javascript设计模式实践之模板方法--具有百叶窗切换图片效果的JQuery插件（二）
- Java随笔三
- Hibernate配置log4j日志环境
- java开发中的23中设计模式详解--大话设计模式
- 【深入Java虚拟机】之一：Java内存模型与内存溢出
- jsp-avaBean
- MySQL基础 - 编码设置
- vijos 1038 括号+路径 ***
- WordPress 性能检测与速度优化
- (转)IIS设置优化（需根据服务器性能，调整具体参数值）
- vs2010 调试C++程序 快捷键
- Access to the path "Library\UnityAssemblies\UnityEngine.xml" is denied.
- Codeforces 376A. Night at the Museum
- idea一款颜值很高的theme
- laravel 控制器类DB类操作
- python_flask 基础巩固(自定义URL转换器)
- Chrome浏览器的版本查看 以及V8 javascript 引擎版本查看
- 使用WebClient下载网页，用正则匹配需要的内容
- django for 循环中，获取序号
- c++ 重载、覆盖 （隐藏）（virtual）
热门文章
- 17、docker多机网络通信overlay
- Spring cache 缓存
- Python学习-27.Python中的列表（list）
- Web app制作细节:web app互动制作技巧
- Flash(as3) 调整显示对象颜色
- C# volatile 关键字
- 通过sys.objects查询SQL SERVER数据库改动内容
- SqlAlchemy操作（三）
- Aizu 2249Road Construction 单源最短路变形《挑战程序设计竞赛》模板题
- （samba启动失败）smb.service: main process exited, code=exited, status=1/FAILURE