java使用poi生成excel的步骤
作者:歪头儿在帝都 时间:2021-08-25 07:03:45
使用poi生成excel通常包含一下几个步骤
创建一个工作簿
创建一个sheet
创建一个Row对象
创建一个cell对象(1个row+1个cell构成一个单元格)
设置单元格内容
设置单元格样式. 字体 字体大小 是否加粗
保存
关闭流对象
生成一个工作簿
2010以上格式使用XSSFWorkBook对象, 2003格式使用HSSFWorkBook对象, 其他对象操作基本一样.
生成2003格式
public void test1() {
HSSFWorkbook workbook = new HSSFWorkbook();
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
Font font = workbook.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 12);
cellStyle.setFont(font);
HSSFSheet sheet = workbook.createSheet("Sheet1");
//设置单元格宽度
sheet.setColumnWidth(0, 30 * 256);
sheet.setColumnWidth(1, 30 * 256);
sheet.setColumnWidth(2, 30 * 256);
Row row0 = sheet.createRow(0);
Cell cell0 = row0.createCell(0);
cell0.setCellValue("序号");
cell0.setCellStyle(cellStyle);
Cell cell1 = row0.createCell(1);
cell1.setCellValue("姓名");
Cell cell2 = row0.createCell(2);
cell2.setCellValue("成绩");
OutputStream os = null;
try {
os = new FileOutputStream("d:\\测试生成2003.xls");
workbook.write(os);
os.close();
} catch (Exception e) {
e.printStackTrace();
}
}
生成2010以上格式
@Test
public void test2() {
XSSFWorkbook workbook = new XSSFWorkbook();
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
Font font = workbook.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 12);
cellStyle.setFont(font);
XSSFSheet sheet = workbook.createSheet("Sheet1");
Row row0 = sheet.createRow(0);
Cell cell0 = row0.createCell(0);
cell0.setCellValue("序号");
cell0.setCellStyle(cellStyle);
Cell cell1 = row0.createCell(1);
cell1.setCellValue("姓名");
Cell cell2 = row0.createCell(2);
cell2.setCellValue("成绩");
OutputStream os = null;
try {
os = new FileOutputStream("d:\\测试生成2010.xlsx");
workbook.write(os);
os.close();
} catch (Exception e) {
e.printStackTrace();
}
}
合并单元格
合并单元格在生成excel中算常见的一个场景, 通常先合并单元, 单元格内容居中,并设置单元格边框.
poi合并单元格使用CellRangeAddress类, 构造函数包括4个参数firstRow, lastRow, firstCol, lastCol根据自己需要传入行和列.
public CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol) {
}
合并单元格后设置边框poi已提供了RegionUtil静态类, 可直接使用.
CellRangeAddress region = new CellRangeAddress(0, 0, 0, 2);
sheet.addMergedRegion(region);
RegionUtil.setBorderBottom(BorderStyle.THIN, region, sheet);
RegionUtil.setBorderLeft(BorderStyle.THIN, region, sheet);
RegionUtil.setBorderTop(BorderStyle.THIN, region, sheet);
RegionUtil.setBorderRight(BorderStyle.THIN, region, sheet);
设置单元格样式
左右居中 上下居中 自动换行
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setWrapText(true);
使用SpringMVC/SpringBoot导出excel
@Controller
@GetMapping("/excel2003")
public void excel2003(HttpServletResponse httpServletResponse){
try {
//2010格式设置
//response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
//2003格式设置
response.setContentType("application/vnd.ms-excel");
httpServletResponse.addHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode("学生成绩单.xls", "utf-8"));
ServletOutputStream outputStream = httpServletResponse.getOutputStream();
HSSFWorkbook workbook = new HSSFWorkbook();
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
Font font = workbook.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 12);
cellStyle.setFont(font);
HSSFSheet sheet = workbook.createSheet("Sheet1");
Row row0 = sheet.createRow(0);
Cell cell0 = row0.createCell(0);
cell0.setCellValue("序号");
cell0.setCellStyle(cellStyle);
Cell cell1 = row0.createCell(1);
cell1.setCellValue("姓名");
Cell cell2 = row0.createCell(2);
cell2.setCellValue("成绩");
workbook.write(outputStream);
} catch (Exception e) {
e.printStackTrace();
}
}
来源:https://www.cnblogs.com/sword-successful/p/16152450.html
标签:java,生成,excel
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
javaweb页面附件、图片下载及打开(实现方法)
2023-11-25 05:33:44
解决java 分割字符串成数组时,小圆点不能直接进行分割的问题
2023-11-05 03:13:24
Java实现文件和base64流的相互转换功能示例
2023-11-18 07:45:01
Java值传递之swap()方法不能交换的解决
2023-11-12 20:54:50
![](https://img.aspxhome.com/file/2023/4/59594_0s.jpg)
java中JVM中如何存取数据和相关信息详解
2023-08-10 03:49:46
![](https://img.aspxhome.com/file/2023/8/58048_0s.jpg)
Spring Boot学习入门之AOP处理请求详解
2023-11-27 10:55:17
Spring整合MyBatis图示过程解析
2023-11-13 11:45:09
![](https://img.aspxhome.com/file/2023/8/59188_0s.png)
Java代码实现酒店管理系统
2023-08-13 13:09:23
![](https://img.aspxhome.com/file/2023/5/58095_0s.jpg)
java判断http地址是否连通(示例代码)
2023-08-05 03:24:05
java实现列表、集合与数组之间转化的方法
2023-11-29 10:17:38
java中Hashmap的get方法使用
2023-10-29 13:10:05
![](https://img.aspxhome.com/file/2023/2/58602_0s.png)
java必学必会之线程(2)
2023-11-09 10:22:35
![](https://img.aspxhome.com/file/2023/2/58742_0s.png)
基于eclipse.ini内存设置的问题详解
2021-08-25 02:56:55
C++实现的O(n)复杂度内查找第K大数算法示例
2023-06-30 15:51:13
android 6.0 写入SD卡的权限申请实例讲解
2023-07-27 03:12:37
java isInterrupted()判断线程的实例讲解
2023-07-21 01:45:53
聊聊Java的switch为什么不支持long
2023-08-24 17:35:14
Java数据结构之链表详解
2023-10-20 09:14:08
![](https://img.aspxhome.com/file/2023/7/58587_0s.png)
如何优雅的处理Spring Boot异常信息详解
2023-11-29 09:50:02
![](https://img.aspxhome.com/file/2023/6/60536_0s.jpg)
SpringCloud Gateway使用详解
2023-11-27 02:54:36