前言
1、将用户信息导出为excel表格(导出数据….)
2、将Excel表中的信息录入到网站数据库(习题上传….)
开发中经常会设计到excel的处理,如导出Excel,导入Excel到数据库中!
操作Excel目前比较流行的就是 Apache POI 和 阿里巴巴的 easyExcel !
接下来说说Apache下的POI吧
<dependencies>
<!--xls(03)-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<!--xlsx(07)-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<!--日期格式化工具-->
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.10.1</version>
</dependency>
<!--test-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
</dependencies>
注意:
1、jar包需要保持版本(version)一样
2、2003 版本和 2007 版本存在兼容性的问题!03版本最多只有 65536 行!07版本没有限制
2、Excel导入
public void importExcel(@RequestParam("file") MultipartFile multipartFile) {
//MultipartFile转换成File
File file=MultipartFile2FileUtil.MultipartFile2File(multipartFile);
//需要解析的Excel文件
try {
//获取工作簿
/**
* 构造函数获取一个FileInputStream对象
* FileUtils.openInputStream 调用了 FileInputStream 的构造函数去打开一个文件(open0是一个本地方法,打开指定文件进行读取)
* 对FileInputStream中的open调用open0方法---> https://blog.csdn.net/zhlily1/article/details/109468154
*/
FileInputStream fs = FileUtils.openInputStream(file);
XSSFWorkbook workbook = new XSSFWorkbook(fs);
//获取最Excel中的sheet数量
int numberOfSheets = workbook.getNumberOfSheets();
for (int j=0;j<numberOfSheets;j++){
//获取第一个工作表
XSSFSheet hs = workbook.getSheetAt(j);
//获取Sheet的第一个行号和最后一个行号
int last = hs.getLastRowNum();
int first = hs.getFirstRowNum() + 1;
//遍历获取单元格里的信息
List<AuditContentImportBo> list = new ArrayList<>();
//注:row为行 cell为列
for (int i = first; i <= last; i++) {
XSSFRow row = hs.getRow(i);
String cell1=new String();
//可以在这里遍历row,获取当前行每列的数据
if (!StringUtil.isNullOrEmpty(cell1)) {
try {
cell1=row.getCell(1).getStringCellValue();
}catch (Exception e){
cell1=String.valueOf(row.getCell(1).getNumericCellValue());
}
}
}
String name = hs.getSheetName();
equipmentAuditContentService.importEquipmentAuditContent(name, list);
}
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//清空文件缓存,并且调用delete0关闭文件
MultipartFile2FileUtil.deleteFile(file);
}
3、MultipartFile2FileUtil工具类
public class MultipartFile2FileUtil {
/**
* MultipartFile转换为File
* @param multipartFile
* @return
* MultipartFile继承了InputStreamSource
*/
public static File MultipartFile2File(MultipartFile multipartFile){
//文件上传前的名称
String fileName = multipartFile.getOriginalFilename();
//new 一个 file
File file = new File(fileName);
OutputStream out = null;
try{
//获取文件流,以文件流的方式输出到新文件
// InputStream in = multipartFile.getInputStream();
//获取到一个file的输出流
out = new FileOutputStream(file);
//获取multipartFile的字节文件,并将字节文件遍历write到out输出流
byte[] ss = multipartFile.getBytes();
for(int i = 0; i < ss.length; i++){
out.write(ss[i]);
}
}catch(IOException e){
e.printStackTrace();
}finally {
//进行判断关闭输出流
if (out != null){
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return file;
}
public static void deleteFile(File file){
File f = new File(file.toURI());
f.delete();
}
}
4、导出Excel
public ApiResult exportAuditContent(HttpServletResponse response, @RequestBody ExportAuditContentIds exportAuditContentIds){
OutputStream outputStream = tryCatch2gGetOutput(response);
List<Entity> entitys=dao.getEntitys();
HSSFWorkbook workbook = new HSSFWorkbook();
//headers
String[] headers=new String[]{"表头1","表头2","表头3"};
int sheetNum=0;
//遍历查到的list
for (Entity entity : entitys) {
List<List<String>> result=new ArrayList<>();
//sheetName
String sheetTitle=entity.getSheetName();
for (entity exportAuditContent : auditContent.getContentName()) {
List<String> str=new ArrayList<>();
str.add(exportAuditContent.getXXX());
str.add(exportAuditContent.getXXX());
str.add(exportAuditContent.getXXX());
result.add(str);
}
ExcelForMoreSheet.exportExcel(workbook,sheetNum,sheetTitle,headers,result,outputStream);
sheetNum++;
}
try {
workbook.write(outputStream);
outputStream.close();
} catch (Exception e) {
throw new AnyException("导出出错");
}
return success("导出成功","成功");
}
5、tryCatch2gGetOutput
private OutputStream tryCatch2gGetOutput(HttpServletResponse response){
try {
OutputStream output = response.getOutputStream();
response.reset();
String filename = "title"+new SimpleDateFormat("yyyy-MM-dd").format(new Date()) +".xls";
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setHeader("Content-Disposition", "attachment;filename="
.concat(String.valueOf(URLEncoder.encode(filename, "UTF-8"))));
response.setHeader("Access-Control-Allow-Origin", "*");
response.setHeader("Cache-Control", "no-cache");
return output;
} catch (Exception e) {
throw new AnyException("导出出错");
}
}
6、Excel导出工具类
package com.troo;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
public class ExcelForMoreSheet {
/**
* @Title: exportExcel
* @Description: 导出Excel的方法
* @author: evan @ 2014-01-09
* @param workbook
* @param sheetNum (sheet的位置,0表示第一个表格中的第一个sheet)
* @param sheetTitle (sheet的名称)
* @param headers (表格的标题)
* @param result (表格的数据)
* @param out (输出流)
* @throws Exception
*/
public static void exportExcel(HSSFWorkbook workbook, int sheetNum,
String sheetTitle, String[] headers, List<List<String>> result,
OutputStream out) {
// 生成一个表格
HSSFSheet sheet = workbook.createSheet();
workbook.setSheetName(sheetNum, sheetTitle);
// 设置表格默认列宽度为20个字节
sheet.setDefaultColumnWidth((short) 20);
// 生成一个样式
HSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 生成一个字体
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.BLACK.index);
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
// 指定当单元格内容显示不下时自动换行
style.setWrapText(true);
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
for (int i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell((short) i);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text.toString());
}
// 遍历集合数据,产生数据行
if (result != null) {
int index = 1;
for (List<String> m : result) {
row = sheet.createRow(index);
int cellIndex = 0;
for (String str : m) {
HSSFCell cell = row.createCell((short) cellIndex);
if (str==null){
str="";
}
cell.setCellValue(str.toString());
cellIndex++;
}
index++;
}
}
}
}
7、Entity类
public class Entity{
private String sheetName;
private List<SubEntity> subEntitys;
}
public class ExportAuditContent {
private String param1;
private String param2;
private String param3;
}
8、03版测试
String PATH = "D:\\idea.webProject\\26";
@Test
public void testWrite03() throws Exception {
// 1、创建一个工作簿
Workbook workbook = new HSSFWorkbook();
// 2、创建一个工作表
Sheet sheet = workbook.createSheet("统计表");
// 3、创建一个行 (1,1)
Row row1 = sheet.createRow(0);
// 4、创建一个单元格
Cell cell11 = row1.createCell(0);
cell11.setCellValue("今日新增");
// (1,2)
Cell cell12 = row1.createCell(1);
cell12.setCellValue(666);
// 第二行 (2,1)
Row row2 = sheet.createRow(1);
Cell cell21 = row2.createCell(0);
cell21.setCellValue("统计时间");
// (2,2)
Cell cell22 = row2.createCell(1);
String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
cell22.setCellValue(time);
// 生成一张表(IO 流) 03 版本就是使用 xls结尾!
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "zh03.xls");
// 输出
workbook.write(fileOutputStream);
// 关闭流
fileOutputStream.close();
System.out.println("zh03 生成完毕!");
}
9、07版测试
@org.junit.Test
public void testWrite07() throws IOException {
//1、创建一个工作簿
Workbook workbook = new XSSFWorkbook();
//2、创建一个工作表
Sheet sheet = workbook.createSheet("统计表");
//3、创建一个行(1,1)
Row row1=sheet.createRow(0);
//4、创建一个单元格
Cell cell11 = row1.createCell(0);
cell11.setCellValue("今日新增");
Cell cell12 = row1.createCell(1);
cell12.setCellValue("今日无增");
//创建一个行(2,1)
Row row2 = sheet.createRow(1);
//创建一个单元格
Cell cell21 = row2.createCell(0);
cell21.setCellValue("统计时间");
Cell cell22 = row2.createCell(1);
String time = new DateTime().toString("yyyy-mm-dd HH:mm:ss");
cell22.setCellValue(time);
//生成一张表 io流 注意这里是07版本
FileOutputStream fileOutputStream = new FileOutputStream(path+"java07.xlsx");
workbook.write(fileOutputStream);
//关闭流
fileOutputStream.close();
System.out.println("07版本文件生成完毕");
}
测试成功!
10、总结:
在ApachePoi 官方提供了对操作大数据量的导入导出的工具和解决办法,操作Excel2007使用XSSF对象,可以分为三种模式:
- 用户模式:用户模式有许多封装好的方法操作简单,但创建太多的对象,非常耗内存(之前使用的方法)
- 事件模式:基于SAX方式解析XML,SAX全称Simple API for XML,它是一个接口,也是一个软件包。它是一种XML解析的替代方法,不同于DOM解析XML文档时把所有内容一次性加载到内存中的方式,它逐行扫描文档,一边扫描,一边解析。
- SXSSF对象:是用来生成海量excel数据文件,主要原理是借助临时存储空间生成excel