poi导出excel的格式：Java POI 根据模板导出Excel文件
poi导出excel的格式：Java POI 根据模板导出Excel文件
Apache POIPOI为【Poor Obfuscation Implementation】的首字母缩写，意为【可怜的模糊实现】。Java程序对Microsoft Office格式档案读和写的功能。
POI是创建和维护操作各种符合Office Open XML（OOXML）标准和微软的OLE 2复合文档格式（OLE2）的Java API。可以使用Java读取和创建、修改MS Excel文件，还可以使用Java读取和创建MS Word和MSPowerPoint文件，POI提供Java操作Excel解决方案（适用于Excel97-2008）。
Web下载功能的关注点：
具体代码：
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.16</version>
</dependency>
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.10</version>
</dependency>
//============================================================================================//
//=====根据模板，使用poi组件导出exeel文件
//============================================================================================//
try {
ClassPathResource classPathResource = new ClassPathResource("template/费用明细模板20201202.xls");
InputStream input = classPathResource.getInputStream();
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(input);
HSSFSheet hssFSheet = hssfWorkbook.getSheetAt(0);
this.fillUserBillEntryListForSheet(hssfWorkbook hssFSheet userBillEntryList);
String fileName = "费用明细_" DateUtil.Y_M_DAY.format(new Date());
fileName = URLEncoder.encode(fileName "UTF-8");
response.setHeader("Content-disposition" "attachment;filename=" fileName ".xls");
response.setContentType("application/octet-stream");
hssfWorkbook.write(response.getOutputStream());
} catch (IOException e) {
e.printStackTrace();
}
//============================================================================================//
//=====The end
//============================================================================================//
读取模板（.xls）设置样式并输出
package com.what21.poi;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import java.io.*;
public class PoiExcel2XlsTest {
public static void main(String[] args) throws IOException {
String fileStr = "d://费用明细模板20201202.xls";
InputStream input = new FileInputStream(fileStr);
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(input);
HSSFSheet hssFSheet = hssfWorkbook.getSheetAt(0);
//设置第3行
HSSFRow row3 = hssFSheet.createRow(2);
HSSFCell cell31 = row3.createCell(0);
cell31.setCellStyle(getCellStyle(hssfWorkbook));
cell31.setCellValue("11111111");
HSSFCell cell32 = row3.createCell(1);
cell32.setCellStyle(getCellStyle(hssfWorkbook));
cell32.setCellValue("22222222");
HSSFCell cell33 = row3.createCell(2);
cell33.setCellStyle(getCellStyle(hssfWorkbook));
cell33.setCellValue("33333333");
HSSFCell cell34 = row3.createCell(3);
cell34.setCellStyle(getCellStyle(hssfWorkbook));
cell34.setCellValue("44444444");
HSSFCell cell35 = row3.createCell(4);
cell35.setCellStyle(getCellStyle(hssfWorkbook));
cell35.setCellValue("55555555");
//设置第4行
HSSFRow row4 = hssFSheet.createRow(3);
row4.createCell(0).setCellValue("11111111");
row4.createCell(1).setCellValue("22222222");
row4.createCell(2).setCellValue("33333333");
row4.createCell(3).setCellValue("44444444");
row4.createCell(4).setCellValue("55555555");
String exportFileStr = "d://费用明细模板20201202.1.xls";
hssfWorkbook.write(new FileOutputStream(new File(exportFileStr)));
}
/**
* @param hssfWorkbook
* @return
*/
public static HSSFCellStyle getCellStyle(HSSFWorkbook hssfWorkbook) {
// 样式设置
HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();
// =======================================================================//
cellStyle.setBorderBottom(BorderStyle.THIN);//设置下边框
cellStyle.setBorderLeft(BorderStyle.THIN);//设置左边框
cellStyle.setBorderRight(BorderStyle.THIN);//设置右边框
cellStyle.setBorderTop(BorderStyle.THIN);//设置上边框
// =======================================================================//
// 设置边框颜色
cellStyle.setBottomBorderColor((short) 1);
// 设置每个单元格的文字居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
// =======================================================================//
//通过HSSFWorkbook创建一个HSSFFont
HSSFFont font = hssfWorkbook.createFont();
//设置一个字体的颜色
//2表示红色
//3表示绿色
//4表示蓝色
//5表示黄色
//6表示紫色
//7表示亮蓝色
//8表示黑色
//9表示白色
font.setColor((short) 8);
//设置字体的风格：仿宋_GB2312、黑体
font.setFontName("黑体");
//设置一个字体的大小 此数值的取值与Excel中的字体大小取值一样
font.setFontHeightInPoints((short) 9);
//粗体显示
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//把该字体应用到当前的样式
cellStyle.setFont(font);
// =======================================================================//
// 设置自动换行
cellStyle.setWrapText(true);
// =======================================================================//
// 设置背景色
cellStyle.setFillForegroundColor((short) 7);
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// =======================================================================//
return cellStyle;
}
}
读取模板（.xlsx）设置样式并输出
package com.what21.poi;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.xssf.usermodel.*;
import java.io.*;
public class PoiExcel2XlsxTest {
public static void main(String[] args) throws IOException {
String fileStr = "d://费用明细模板20201202.xlsx";
InputStream input = new FileInputStream(fileStr);
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(input);
XSSFSheet xssFSheet = xssfWorkbook.getSheetAt(0);
//设置第3行
XSSFRow row3 = xssFSheet.getRow(2);
XSSFCell cell31 = row3.createCell(0);
cell31.setCellStyle(getCellStyle(xssfWorkbook));
cell31.setCellValue("11111111");
XSSFCell cell32 = row3.createCell(1);
cell32.setCellStyle(getCellStyle(xssfWorkbook));
cell32.setCellValue("22222222");
XSSFCell cell33 = row3.createCell(2);
cell33.setCellStyle(getCellStyle(xssfWorkbook));
cell33.setCellValue("33333333");
XSSFCell cell34 = row3.createCell(3);
cell34.setCellStyle(getCellStyle(xssfWorkbook));
cell34.setCellValue("44444444");
XSSFCell cell35 = row3.createCell(4);
cell35.setCellStyle(getCellStyle(xssfWorkbook));
cell35.setCellValue("55555555");
//设置第4行
XSSFRow row4 = xssFSheet.getRow(3);
row4.createCell(0).setCellValue("11111111");
row4.createCell(1).setCellValue("22222222");
row4.createCell(2).setCellValue("33333333");
row4.createCell(3).setCellValue("44444444");
row4.createCell(4).setCellValue("55555555");
String exportFileStr = "d://费用明细模板20201202.1.xlsx";
xssfWorkbook.write(new FileOutputStream(new File(exportFileStr)));
}
/**
* @param xssFWorkbook
* @return
*/
public static XSSFCellStyle getCellStyle(XSSFWorkbook xssFWorkbook) {
// 样式设置
XSSFCellStyle cellStyle = xssFWorkbook.createCellStyle();
// =======================================================================//
cellStyle.setBorderBottom(BorderStyle.THIN);//设置下边框
cellStyle.setBorderLeft(BorderStyle.THIN);//设置左边框
cellStyle.setBorderRight(BorderStyle.THIN);//设置右边框
cellStyle.setBorderTop(BorderStyle.THIN);//设置上边框
// =======================================================================//
// 设置边框颜色
cellStyle.setBottomBorderColor((short) 1);
// 设置每个单元格的文字居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
// =======================================================================//
//通过XSSFWorkbook创建一个HSSFFont
XSSFFont font = xssFWorkbook.createFont();
//设置一个字体的颜色
//2表示红色
//3表示绿色
//4表示蓝色
//5表示黄色
//6表示紫色
//7表示亮蓝色
//8表示黑色
//9表示白色
font.setColor((short) 8);
//设置字体的风格：仿宋_GB2312、黑体
font.setFontName("黑体");
//设置一个字体的大小 此数值的取值与Excel中的字体大小取值一样
font.setFontHeightInPoints((short) 9);
//粗体显示
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//把该字体应用到当前的样式
cellStyle.setFont(font);
// =======================================================================//
// 设置自动换行
cellStyle.setWrapText(true);
// =======================================================================//
// 设置背景色
cellStyle.setFillForegroundColor((short) 7);
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// =======================================================================//
return cellStyle;
}
}
