1、模板开发
需求：由于excel表头是固定的，只需要填充内容，我们直接采用模板开发，可以省去定义表头格式和每个字段的列宽，之后将数据从数据库抓取出来按照一定格式填充即可。
FileInputStream inputStream=new FileInputStream("static/exceltemplate/A0041Template.xls"); //获取模板
POIFSFileSystem ps=new POIFSFileSystem(inputStream); //使用POI提供的方法得到excel的信息
HSSFWorkbook wb=new HSSFWorkbook(ps);
HSSFSheet sheet=wb.getSheetAt(0); //获取到工作表，因为一个excel可能有多个工作表
FileOutputStream out=new FileOutputStream("static/excel/A0041.xls");
2、设置样式
需求：由于不同字段需要不同的样式，如水平居中、垂直居中等样式。
ps：一般我们说的居中是水平居中
HSSFCellStyle style = wb.createCellStyle();//设置样式
style.setAlignment(HorizontalAlignment.CENTER);//水平居中
style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
style.setBorderBottom(BorderStyle.THIN);//底部边框线
style.setBorderLeft(BorderStyle.THIN);//左部边框线
style.setBorderRight(BorderStyle.THIN);//右边框线
style.setBorderTop(BorderStyle.THIN);//上边框线
3、设置字体
HSSFFont font = wb.createFont();
font.setFontName("宋体");//字体名称
font.setFontHeightInPoints((short) 12);//设置字体大小
font.setBold(true);//加粗
style.setFont(font);//选择需要用到的字体格式
3、创建行
Row row11 = sheet.createRow(1);//在第二行创建一行（索引是从0开始的）
4、创建列（即单元格）并设置样式
Cell first1 = row11.createCell(0);
Cell first22 = row11.createCell(1);
Cell first3 = row11.createCell(2);
Cell first4 = row11.createCell(3);
Cell first5 = row11.createCell(4);
Cell first6 = row11.createCell(5);
Cell first7 = row11.createCell(6);
Cell first8 = row11.createCell(7);
first1.setCellValue("出货日期:" + dateXmdk026);
first1.setCellStyle(style2);
first22.setCellStyle(style2);
first3.setCellStyle(style2);
first4.setCellStyle(style2);
first5.setCellStyle(style2);
first6.setCellStyle(style2);
first7.setCellStyle(style2);
first8.setCellStyle(style2);
5、合并单元格并写入值
如下是合并0-1列的单元格，行数为变量hang，设置值只需设置1个单元格上即可，样式都需要设置。
//单元格范围 参数（int firstRow, int lastRow, int firstCol, int lastCol)
CellRangeAddress cellRangeAddress1 = new CellRangeAddress(hang, hang, 0, 1);
sheet.addMergedRegion(cellRangeAddress1);
Row row1 = sheet.createRow(hang);
Cell first01 = row1.createCell(0);
Cell second02 = row1.createCell(1);
first01.setCellValue("表单编号");
first01.setCellStyle(style);
second02.setCellStyle(style);
6、常见错误
使用模板开发不能合并单元格，可能是由于单元格在模板中已经合并
7、常见模板开发
1、简单的小计功能
需求如下：
项目由于需要将不同规格的字段进行小计
1.首先在数据库sql中将数据根据某个字段进行order by，得到的数据就是按照字段排列的
2.前后索引进行对比规格相同与否
需求实现如图：
实现代码：
package com.testpoi.test;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
public class TestPoi2 {
public static void main(String[] args) throws IOException {
HSSFWorkbook workbook = new HSSFWorkbook();//创建一个excel
HSSFCellStyle style = workbook.createCellStyle();//设置样式
style.setAlignment(HorizontalAlignment.CENTER);//水平居中
style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
HSSFSheet sheet = workbook.createSheet("sheet");//创建一个工作表
List<Product> list = new ArrayList<Product>();
Product p1 = new Product();
p1.setGuige("apple");
p1.setPinming(11111);
Product p3 = new Product();
p3.setGuige("apple");
p3.setPinming(1);
Product p6 = new Product();
p6.setGuige("apple");
p6.setPinming(1);
Product p2 = new Product();
p2.setGuige("banana");
p2.setPinming(5);
Product p4 = new Product();
p4.setGuige("banana");
p4.setPinming(2);
Product p8 = new Product();
p8.setGuige("banana");
p8.setPinming(2);
Product p5 = new Product();
p5.setGuige("cherry");
p5.setPinming(3);
Product p7 = new Product();
p7.setGuige("cherry");
p7.setPinming(10);
Product p10 = new Product();
p10.setGuige("cherry2");
p10.setPinming(10);
Product p9 = new Product();
p9.setGuige("cherry2");
p9.setPinming(10);
list.add(p1);
list.add(p3);
list.add(p6);
list.add(p2);
list.add(p4);
list.add(p8);
list.add(p5);
list.add(p7);
list.add(p10);
list.add(p9);
int hang = 0;
int sum = 0;
for (int i = 0; i < list.size(); i++) {
if (i == 0) {
HSSFRow row0 = sheet.createRow((short) (hang));
hang++;
HSSFCell cell_00 = row0.createCell(0);
cell_00.setCellValue(list.get(i).getGuige());
sum = sum + list.get(i).getPinming();
System.out.println(list.get(i).getGuige());
} else {
if (list.get(i - 1).getGuige().equals(list.get(i).getGuige())) {// 如果当前记录和下一条相等，继续插入记录 //插入记录
HSSFRow row0 = sheet.createRow((short) (hang));
hang++;
sum = sum + list.get(i).getPinming();
HSSFCell cell_00 = row0.createCell(0);
cell_00.setCellValue(list.get(i).getGuige());
} else {// 前后记录name不同，开始插入小计
HSSFRow row0 = sheet.createRow((short) (hang));
hang++;
HSSFCell cell_00 = row0.createCell(0);
cell_00.setCellValue("小计：" + sum);
sum = 0;
HSSFRow row01 = sheet.createRow((short) (hang));
hang++;
HSSFCell cell_001 = row01.createCell(0);
cell_001.setCellValue(list.get(i).getGuige());
sum = sum + list.get(i).getPinming();
}
}
}
HSSFRow row0 = sheet.createRow((short) (hang));
hang++;
HSSFCell cell_00 = row0.createCell(0);
cell_00.setCellValue("小计：" + sum);
File file = new File("D:\\code\\javaworkspace\\comtestpoi\\src\\test\\java\\com\\testpoi\\test\\demo2.xls");
FileOutputStream fout = new FileOutputStream(file);
workbook.write(fout);
fout.close();
}
}
pom文件：
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>testpoi</groupId>
<artifactId>com.testpoi</artifactId>
<version>1.0-SNAPSHOT</version>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<source>8</source>
<target>8</target>
</configuration>
</plugin>
</plugins>
</build>
<dependencies>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.jetbrains</groupId>
<artifactId>annotations-java5</artifactId>
<version>RELEASE</version>
<scope>compile</scope>
</dependency>
</dependencies>
</project>
2、根据不同客户导出不同excel出货明细表。
1、表头：由于表头有些是很复杂的，故采用模板开发
2、小计个数和小计重量：需要根据统一材质、规格的货物进行分组小计个数和重量
3、共计个数和共计数量
主要看如何实现小计个数和重量以及共计个数和共计数量
以下为全部实现代码，稍微粗略且不同。
@RequestMapping(value = "/downloadA0017")
public ResponseEntity<byte[]> download(HttpServletRequest request,
@RequestParam("filename") String filename,
@RequestParam("xmdk026") String xmdk026,
@RequestHeader("User-Agent") String userAgent,
Model model) throws Exception {
FileInputStream inputStream=new FileInputStream("static/exceltemplate/A0017Template.xls"); //获取head.xls
POIFSFileSystem ps=new POIFSFileSystem(inputStream); //使用POI提供的方法得到excel的信息
HSSFWorkbook wb=new HSSFWorkbook(ps);
HSSFSheet sheet=wb.getSheetAt(0); //获取到工作表，因为一个excel可能有多个工作表
FileOutputStream out=new FileOutputStream("static/excel/A0017.xls");
HSSFCellStyle style = wb.createCellStyle();//设置样式
style.setAlignment(HorizontalAlignment.CENTER);//水平居中
style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
// style.setb
/**
* 设置字体
*/
HSSFFont font = wb.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 12);//设置字体大小
style.setFont(font);//选择需要用到的字体格式
/**
* 出货日期style and font
*/
HSSFCellStyle style2 = wb.createCellStyle();//设置样式
style2.setAlignment(HorizontalAlignment.LEFT);//水平居中
style2.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
style2.setBorderBottom(BorderStyle.THIN);
/**
* 设置字体
*/
HSSFFont font2 = wb.createFont();
font2.setFontName("宋体");
font2.setFontHeightInPoints((short) 11);//设置字体大小
style2.setFont(font2);//选择需要用到的字体格式
/**
* 出货日期style and font
*/
HSSFCellStyle style3 = wb.createCellStyle();//设置样式
style3.setAlignment(HorizontalAlignment.CENTER);//水平居中
style3.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
style3.setBorderBottom(BorderStyle.THIN);
/**
* 设置字体
*/
HSSFFont font3 = wb.createFont();
font3.setFontName("宋体");
font3.setFontHeightInPoints((short) 11);//设置字体大小
style3.setFont(font3);//选择需要用到的字体格式
/**
* -------------------------------------开始搬砖分割线--------------------------------------------------------------
*/
Date date = new SimpleDateFormat("yyyy-MM-dd").parse(xmdk026);
String dateXmdk026 = new SimpleDateFormat("yyyy年M月d日").format(date);
HSSFRow row110 = sheet.createRow((short)5);
Cell first1100001 = row110.createCell(0);
first1100001.setCellValue(" 发货日期：");
first1100001.setCellStyle(style2);
Cell second1100002 = row110.createCell(1);
Cell second1100003 = row110.createCell(2);
second1100003.setCellStyle(style3);
second1100003.setCellValue(dateXmdk026);
Cell second1100004 = row110.createCell(3);
Cell second1100005 = row110.createCell(4);
Cell second1100006 = row110.createCell(5);
Cell second1100007 = row110.createCell(6);
List<BcafAndImaalAndBcaaVO> list;
list = bcafAndImaalAndBcaaService.queryByXmdk("A0017", xmdk026);
int hang = 7;//从索引第7开始插入数据
float sum = 0;//共计数量
int total = 0;//共计重量
int index = 7;//小计个数使用的索引
float totalSum = 0;//小计重量
int i;
for ( i = 0; i < list.size(); i++) {
if (i == 0) {//首行不需要判断规格材质是否是同一组，直接插入数据即可
/**
* 首行
*/
HSSFRow row0 = sheet.createRow((short) (hang));
hang++;
//系统序号
HSSFCell cell_9 = row0.createCell(0);
cell_9.setCellValue("");
cell_9.setCellStyle(style);
//订购单号
HSSFCell cell_3 = row0.createCell(1);
cell_3.setCellValue("");
cell_3.setCellStyle(style);
//材质
HSSFCell cell_2 = row0.createCell(2);
cell_2.setCellValue(list.get(i).getImaal0042());
cell_2.setCellStyle(style);
//规格
HSSFCell cell_0 = row0.createCell(3);
cell_0.setCellValue(list.get(i).getImaal004());
cell_0.setCellStyle(style);
//炉号
HSSFCell cell_4 = row0.createCell(4);
cell_4.setCellValue(list.get(i).getBcaaud002());
cell_4.setCellStyle(style);
//单卷净重
HSSFCell cell_5 = row0.createCell(5);
cell_5.setCellValue(list.get(i).getBcaf016());
cell_5.setCellStyle(style);
//批号
HSSFCell cell_6 = row0.createCell(6);
cell_6.setCellValue(list.get(i).getBcaaud001());
cell_6.setCellStyle(style);
sum = sum + list.get(i).getBcaf016();
totalSum=totalSum+ list.get(i).getBcaf016();
} else {
if ((list.get(i - 1).getImaal004().equals(list.get(i).getImaal004()))&&(list.get(i - 1).getBcaaud002().equals(list.get(i).getBcaaud002()))) {// 如果当前记录和下一条相等，继续插入记录 //插入记录
HSSFRow row0 = sheet.createRow((short) (hang));
hang++;
sum = sum + list.get(i).getBcaf016();
totalSum=totalSum+ list.get(i).getBcaf016();
//系统序号
HSSFCell cell_9 = row0.createCell(0);
cell_9.setCellValue("");
cell_9.setCellStyle(style);
//订购单号
HSSFCell cell_3 = row0.createCell(1);
cell_3.setCellValue("");
cell_3.setCellStyle(style);
//材质
HSSFCell cell_2 = row0.createCell(2);
cell_2.setCellValue(list.get(i).getImaal0042());
cell_2.setCellStyle(style);
//规格
HSSFCell cell_0 = row0.createCell(3);
cell_0.setCellValue(list.get(i).getImaal004());
cell_0.setCellStyle(style);
//炉号
HSSFCell cell_4 = row0.createCell(4);
cell_4.setCellValue(list.get(i).getBcaaud002());
cell_4.setCellStyle(style);
//单卷净重
HSSFCell cell_5 = row0.createCell(5);
cell_5.setCellValue(list.get(i).getBcaf016());
cell_5.setCellStyle(style);
//批号
HSSFCell cell_6 = row0.createCell(6);
cell_6.setCellValue(list.get(i).getBcaaud001());
cell_6.setCellStyle(style);
} else {// 前后记录name不同，开始插入小计
/**
* ------------------小计start------------------------
*/
HSSFRow row0 = sheet.createRow((short) (hang));
hang++;
Cell first01 = row0.createCell(0);
Cell second02 = row0.createCell(1);
Cell second03 = row0.createCell(2);
Cell second04 = row0.createCell(3);
Cell second05 = row0.createCell(4);
Cell second06 = row0.createCell(5);
second05.setCellValue("计"+(hang-index-1)+"卷");
total += hang-index-1;
first01.setCellStyle(style);
second02.setCellStyle(style);
second03.setCellStyle(style);
second04.setCellStyle(style);
second05.setCellStyle(style);
second06.setCellStyle(style);
index = hang;
HSSFCell cell_66 = row0.createCell(6);
cell_66.setCellStyle(style);
if (sum % 1 == 0) {//当为整数时，如400.0
int sumInt = (int) sum;
second06.setCellValue(sumInt);
second06.setCellStyle(style);
} else {
second06.setCellValue(sum);
second06.setCellStyle(style);
}
/**
* ------------------小计end------------------------
*/
sum = 0;//小计完 ，开始清零
HSSFRow row01 = sheet.createRow((short) (hang));
hang++;
//系统序号
HSSFCell cell_9 = row01.createCell(0);
cell_9.setCellValue("");
cell_9.setCellStyle(style);
//订购单号
HSSFCell cell_3 = row01.createCell(1);
cell_3.setCellValue("");
cell_3.setCellStyle(style);
//材质
HSSFCell cell_2 = row01.createCell(2);
cell_2.setCellValue(list.get(i).getImaal0042());
cell_2.setCellStyle(style);
//规格
HSSFCell cell_0 = row01.createCell(3);
cell_0.setCellValue(list.get(i).getImaal004());
cell_0.setCellStyle(style);
//炉号
HSSFCell cell_4 = row01.createCell(4);
cell_4.setCellValue(list.get(i).getBcaaud002());
cell_4.setCellStyle(style);
//单卷净重
HSSFCell cell_5 = row01.createCell(5);
cell_5.setCellValue(list.get(i).getBcaf016());
cell_5.setCellStyle(style);
//批号
HSSFCell cell_6 = row01.createCell(6);
cell_6.setCellValue(list.get(i).getBcaaud001());
cell_6.setCellStyle(style);
sum = sum + list.get(i).getBcaf016();
totalSum=totalSum+ list.get(i).getBcaf016();
}
}
}
/**
* ------------------小计start------------------------
*/
HSSFRow row0 = sheet.createRow((short) (hang));
hang++;
Cell first01 = row0.createCell(0);
Cell second02 = row0.createCell(1);
Cell second03 = row0.createCell(2);
Cell second04 = row0.createCell(3);
Cell second05 = row0.createCell(4);
Cell second06 = row0.createCell(5);
second05.setCellValue("计"+(hang-index-1)+"卷");
first01.setCellStyle(style);
second02.setCellStyle(style);
second03.setCellStyle(style);
second04.setCellStyle(style);
second05.setCellStyle(style);
second06.setCellStyle(style);
HSSFCell cell_66 = row0.createCell(6);
cell_66.setCellStyle(style);
if (sum % 1 == 0) {//当为整数时，如400.0
int sumInt = (int) sum;
second06.setCellValue(sumInt);
second06.setCellStyle(style);
} else {
second06.setCellValue(sum);
second06.setCellStyle(style);
}
/**
* ------------------小计end------------------------
*/
/**
* ------------------小计start------------------------
*/
HSSFRow row0002 = sheet.createRow((short) (hang));
hang++;
Cell first0001 = row0002.createCell(0);
Cell second0002 = row0002.createCell(1);
Cell second0003 = row0002.createCell(2);
Cell second0004 = row0002.createCell(3);
Cell second0005 = row0002.createCell(4);
Cell second0006 = row0002.createCell(5);
total += hang-index-1;
second0005.setCellValue("计"+(total-1)+"卷");
first0001.setCellStyle(style);
first0001.setCellValue("总计");
second0002.setCellStyle(style);
second0003.setCellStyle(style);
second0004.setCellStyle(style);
second0005.setCellStyle(style);
second0006.setCellStyle(style);
HSSFCell cell_0066 = row0002.createCell(6);
cell_0066.setCellStyle(style);
if (totalSum % 1 == 0) {//当为整数时，如400.0
int sumInt = (int) totalSum;
second0006.setCellValue(sumInt);
second0006.setCellStyle(style);
} else {
second0006.setCellValue(totalSum);
second0006.setCellStyle(style);
}
/**
* ------------------小计end------------------------
*/
/**
* 注：start----------------------
*/
HSSFCellStyle style666 = wb.createCellStyle();//设置样式
style666.setVerticalAlignment(VerticalAlignment.CENTER);
style666.setBorderTop(BorderStyle.THIN);
/**
* 设置字体
*/
HSSFFont font666 = wb.createFont();
font666.setFontName("宋体");
font666.setFontHeightInPoints((short) 11);//设置字体大小
// font.setBold(true);//加粗
style666.setFont(font666);//选择需要用到的字体格式
style666.setWrapText(true);
//合并单元格
CellRangeAddress cellRangeAddress12 = new CellRangeAddress(hang, hang, 0, 6);
sheet.addMergedRegion(cellRangeAddress12);
HSSFRow row0003 = sheet.createRow((short) (hang));
row0003.setHeight((short) (71.25 * 20));
hang++;
Cell first00001 = row0003.createCell(0);
Cell second00002 = row0003.createCell(1);
Cell second00003 = row0003.createCell(2);
Cell second00004 = row0003.createCell(3);
Cell second00005 = row0003.createCell(4);
Cell second00006 = row0003.createCell(5);
first00001.setCellStyle(style666);
second00002.setCellStyle(style666);
second00003.setCellStyle(style666);
second00004.setCellStyle(style666);
second00005.setCellStyle(style666);
second00006.setCellStyle(style666);
HSSFCell cell_00066 = row0003.createCell(6);
cell_00066.setCellStyle(style666);
/**
* 注：end----------------------
*/
HSSFCellStyle style6667 = wb.createCellStyle();//设置样式
style6667.setAlignment(HorizontalAlignment.CENTER);//水平居中
style6667.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
/**
* 设置字体
*/
HSSFFont font6667 = wb.createFont();
font6667.setFontName("宋体");
font6667.setFontHeightInPoints((short) 11);//设置字体大小
// font.setBold(true);//加粗
style6667.setFont(font6667);//选择需要用到的字体格式
style6667.setWrapText(true);
HSSFRow row0004 = sheet.createRow((short) (hang));
hang++;
Cell first000001 = row0004.createCell(0);
first000001.setCellValue("制表：");
first000001.setCellStyle(style6667);
Cell second000002 = row0004.createCell(1);
second000002.setCellStyle(style6667);
Cell second000003 = row0004.createCell(2);
Cell second000004 = row0004.createCell(3);
Cell second000005 = row0004.createCell(4);
Cell second000006 = row0004.createCell(5);
Cell second000007 = row0004.createCell(6);
HSSFRow row0005 = sheet.createRow((short) (hang));
hang++;
Cell first0000001 = row0005.createCell(0);
first0000001.setCellValue("日期：");
first0000001.setCellStyle(style6667);
Cell second0000002 = row0005.createCell(1);
second0000002.setCellStyle(style6667);
second0000002.setCellValue(xmdk026.replace("-","/"));
Cell second0000003 = row0005.createCell(2);
Cell second0000004 = row0005.createCell(3);
Cell second0000005 = row0005.createCell(4);
Cell second0000006 = row0005.createCell(5);
Cell second0000007 = row0005.createCell(6);
/**
* -------------------------------------结束搬砖分割线--------------------------------------------------------------
*/
/**
* ------------------小计end------------------------
*/
out.flush();
wb.write(out);
out.close();
// 下载文件路径
String path = request.getServletContext().getRealPath(
"/excel/");
// 构建File
File file = new File(path + File.separator + filename);
// ok表示Http协议中的状态 200
ResponseEntity.BodyBuilder builder = ResponseEntity.ok();
// 内容长度
builder.contentLength(file.length());
// application/octet-stream ： 二进制流数据（最常见的文件下载）。
builder.contentType(MediaType.APPLICATION_OCTET_STREAM);
// 使用URLDecoder.decode对文件名进行解码
filename = URLEncoder.encode(filename, "UTF-8");
// 设置实际的响应文件名，告诉浏览器文件要用于【下载】、【保存】attachment 以附件形式
// 不同的浏览器，处理方式不同，要根据浏览器版本进行区别判断
if (userAgent.indexOf("MSIE") > 0) {
// 如果是IE，只需要用UTF-8字符集进行URL编码即可
builder.header("Content-Disposition", "attachment; filename=" + filename);
} else {
// 而FireFox、Chrome等浏览器，则需要说明编码的字符集
// 注意filename后面有个*号，在UTF-8后面有两个单引号！
builder.header("Content-Disposition", "attachment; filename*=UTF-8''" + filename);
}
return builder.body(FileUtils.readFileToByteArray(file));
}
133
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
