参照博客 :https://blog.csdn.net/HaHa_Sir/article/details/105231975
和https://www.cnblogs.com/weiyi1314/p/11686018.html
导入Excel
可以直接使用WorkbookFactory创建对象(但是超多6万多行会报错,没测试)
Workbook workbook = WorkbookFactory.create(inputStream);
导入的类
package com.orient.tdm201.vehicleCheck.business;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* @author xks
* @date 2020/5/22 9:01
* @description :
*/
public class ExcelImport {
private ExcelImport INSTANSE = new ExcelImport();
/**
* excel 2003 suffix
*/
private static final String EXCEL_XLS_SUFFIX = ".xls" ;
/**
* excel 2007 或以上 suffix
*/
private static final String EXCEL_XLSX_SUFFIX = ".xlsx";
/**
* 分隔符 "."
*/
public static final String POINT = ".";
/**
* description: 读取excel数据
* @param file
* @return List<List<Object>>
* @version v1.0
* @author w
* @date 2020年3月31日 下午3:36:39
*/
public static List<List<List<Object>>> importFile (File file) throws Exception{
if(file == null) {
return null ;
}
return readXls2(new FileInputStream(file));
// if(file.getName().endsWith(EXCEL_XLS_SUFFIX)) {
// return readXls(new FileInputStream(file));
// }
// if(file.getName().endsWith(EXCEL_XLSX_SUFFIX)) {
// return readXlsx(new FileInputStream(file));
// }
//
// throw new RuntimeException("文件不对,必须是excel文件,后缀名以:"+EXCEL_XLS_SUFFIX + " 或者 "+ EXCEL_XLSX_SUFFIX);
}
/**
* description: 导入excel --- 支持web
* @param
* @param
* @throws Exception
* @return List<List<Object>>
* @version v1.0
* @author w
* @date 2020年3月31日 下午4:51:01
*/
public static List<List<List<Object>>> importFile (MultipartFile multipartFile) throws Exception{
if(multipartFile == null) {
return null ;
}
/*if(multipartFile.getOriginalFilename().endsWith(EXCEL_XLS_SUFFIX)) {
return readXls(multipartFile.getInputStream());
}
if(multipartFile.getOriginalFilename().endsWith(EXCEL_XLSX_SUFFIX)) {
return readXlsx(multipartFile.getInputStream());
}*/
return readXls2(multipartFile.getInputStream());
// throw new RuntimeException("文件不对,必须是excel文件,后缀名以:"+EXCEL_XLS_SUFFIX + " 或者 "+ EXCEL_XLSX_SUFFIX);
}
/**
* 测试 workBookFactory 读取excel
* @param inputStream
* @return
* @throws Exception
*/
private static List<List<List<Object>>> readXls2(InputStream inputStream) throws Exception {
List<List<List<Object>>> list = new ArrayList<>();
// 读取excel
// HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
Workbook workbook = WorkbookFactory.create(inputStream);
// 获取sheet 页数量
int sheets = workbook.getNumberOfSheets();
for(int num = 0 ; num < sheets ; num++ ) {
Sheet sheet = workbook.getSheetAt(num);
List<List<Object>> list1= new ArrayList<>();
if(null == sheet) {
continue ;
}
// sheet 页的总行数
int rows = sheet.getLastRowNum();
// startRow 开始读取的行数 --- 第二行开始读
for( int startRow = 1 ;startRow <= rows ; startRow ++) {
Row row = sheet.getRow(startRow);
List<Object> rowList = new ArrayList<>();
if(null != row) {
// row 行中的 单元格总个数
short cells = row.getLastCellNum();
for(int x = 0 ; x <= cells ; x++) {
Cell cell = row.getCell(x);
if(null == cell) {
rowList.add("");
}else {
rowList.add(getXlsxValue2(cell));
}
}
list1.add(rowList);
}
}
list.add(list1);
}
return list;
}
/**
* 测试 workBookFactory 获得cell的值
* @param cell
* @return
*/
private static Object getXlsxValue2(Cell cell) {
Object cellValue = null;
if (cell != null) {
//判断cell类型
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC: {
cellValue = String.valueOf(cell.getNumericCellValue());
break;
}
case Cell.CELL_TYPE_FORMULA: {
//判断cell是否为日期格式
if (DateUtil.isCellDateFormatted(cell)) {
//转换为日期格式YYYY-mm-dd
cellValue = cell.getDateCellValue();
} else {
//数字
cellValue = String.valueOf(cell.getNumericCellValue());
}
break;
}
case Cell.CELL_TYPE_STRING: {
cellValue = cell.getRichStringCellValue().getString();
break;
}
default:
cellValue = "";
}
} else {
cellValue = "";
}
return cellValue;
}
/**
* description: 读取03版excel
* @param
* @return List<List<Object>>
* @version v1.0
* @author w
* @date 2020年3月31日 下午3:38:44
*/
private static List<List<Object>> readXls(InputStream inputStream) throws Exception {
List<List<Object>> list = new ArrayList<>();
// 读取excel
HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
// 获取sheet 页数量
int sheets = workbook.getNumberOfSheets();
for(int num = 0 ; num < sheets ; num++ ) {
HSSFSheet sheet = workbook.getSheetAt(num);
if(null == sheet) {
continue ;
}
// sheet 页的总行数
int rows = sheet.getLastRowNum();
// startRow 开始读取的行数 --- 第二行开始读
for( int startRow = 1 ;startRow <= rows ; startRow ++) {
HSSFRow row = sheet.getRow(startRow);
List<Object> rowList = new ArrayList<>();
if(null != row) {
// row 行中的 单元格总个数
short cells = row.getLastCellNum();
for(int x = 0 ; x <= cells ; x++) {
HSSFCell cell = row.getCell(x);
if(null == cell) {
rowList.add("");
}else {
rowList.add(getXlsValue(cell));
}
}
list.add(rowList);
}
}
}
return list;
}
/**
* description: 获取 03 版 excel数据
* @param cell
* @return String
* @version v1.0
* @author w
* @date 2020年3月31日 下午3:54:14
*/
private static String getXlsValue(HSSFCell cell) {
if ( cell.getCellType() == cell.CELL_TYPE_BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) {
String cellValue = "";
if(HSSFDateUtil.isCellDateFormatted(cell)){
Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
cellValue = new SimpleDateFormat("yyyy/MM/dd").format(date);
}else{
DecimalFormat df = new DecimalFormat("#.##");
cellValue = df.format(cell.getNumericCellValue());
String strArr = cellValue.substring(cellValue.lastIndexOf(POINT)+1,cellValue.length());
if(strArr.equals("00")){
cellValue = cellValue.substring(0, cellValue.lastIndexOf(POINT));
}
}
return cellValue;
} else {
// 其他类型的值,统一设置为 string
// http://blog.csdn.net/ysughw/article/details/9288307
cell.setCellType(Cell.CELL_TYPE_STRING);
return String.valueOf(cell.getStringCellValue());
}
}
/**
* description: 读取07或以上版本的 excel
* @param
* @throws Exception
* @return List<List<Object>>
* @version v1.0
* @author w
* @date 2020年3月31日 下午4:01:25
*/
private static List<List<Object>> readXlsx(InputStream inputStream) throws Exception {
List<List<Object>> list = new ArrayList<>();
// 读取excel ,封装到 XSSFWorkbook 对象
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
int sheets = workbook.getNumberOfSheets();
for(int num = 0 ;num < sheets ; num++) {
XSSFSheet sheet = workbook.getSheetAt(num);
if(null == sheet) {
continue ;
}
// 获取sheet页的总行数
int rows = sheet.getLastRowNum();
for(int startRow = 1 ; startRow <= rows ; startRow++ ) {
// startRow 开始读取的行数, 从第二行开始读取
XSSFRow row = sheet.getRow(startRow);
List<Object> rowList = new ArrayList<>();
if(null != row) {
// 获取行总单元格个数
short cells = row.getLastCellNum();
for(int x = 0 ; x < cells ; x++) {
XSSFCell cell = row.getCell(x);
if(cell == null) {
rowList.add("");
}else {
rowList.add(getXlsxValue(cell));
}
}
list.add(rowList);
}
}
}
return list;
}
/**
* description: 获取07或以上版本 excel 数据
* @param cell
* @return Object
* @version v1.0
* @author w
* @date 2020年3月31日 下午4:09:03
*/
private static Object getXlsxValue(XSSFCell cell) {
if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
String cellValue = "";
if (DateUtil.isCellDateFormatted(cell)) {
Date date = DateUtil.getJavaDate(cell.getNumericCellValue());
cellValue = new SimpleDateFormat("yyyy/MM/dd").format(date);
} else {
DecimalFormat df = new DecimalFormat("#.##");
cellValue = df.format(cell.getNumericCellValue());
String strArr = cellValue.substring(cellValue.lastIndexOf(POINT) + 1, cellValue.length());
if (strArr.equals("00")) {
cellValue = cellValue.substring(0, cellValue.lastIndexOf(POINT));
}
}
return cellValue;
} else {
// 其他类型的值,统一设置为 string
// http://blog.csdn.net/ysughw/article/details/9288307
//cell.setCellType(Cell.CELL_TYPE_STRING);
return String.valueOf(cell.getStringCellValue());
}
}
}
测试导入类代码
package com.orient.tdm201.vehicleCheck.business;
import java.io.File;
import java.util.List;
/**
-
@author xks
-
@date 2020/5/22 9:06
-
@description : 导入Excel c测试
*/
public class TestImportExcel {
public static void main(String[] args) {
String path = “D:\test3.xlsx” ;
File file = new File(path);
try {
List<List<List>> importFile = ExcelImport.importFile(file);
System.out.println(importFile);
} catch (Exception e) {
e.printStackTrace();
}}
}
导出Excel
导出工具类
package com.orient.tdm201.vehicleCheck.business;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import java.io.OutputStream;
import java.util.List;
/**
* @author xks
* @date 2020/5/21 22:17
* @description :
*/
public class ExportExcelUtilsTest {
public void exportExcel(Workbook workbook, int sheetNum,
String sheetTitle, String[] headers, List<List<String>> result,
OutputStream out) throws Exception {
// 生成一个表格
Sheet sheet = workbook.createSheet();
workbook.setSheetName(sheetNum, sheetTitle);
// 设置表格默认列宽度为20个字节
sheet.setDefaultColumnWidth((short) 20);
// 生成一个样式
CellStyle style = workbook.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
style.setFillBackgroundColor(HSSFColor.WHITE.index);
style.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index);
// 生成一个字体
Font font = workbook.createFont();
font.setColor(HSSFColor.BLACK.index);
font.setFontHeightInPoints((short) 12);
/* // 设置字体
Font font = workbook.createFont();
font.setFontHeightInPoints((short) 20); // 字体高度
font.setColor(Font.COLOR_RED); // 字体颜色
font.setFontName("黑体"); // 字体
font.setBoldweight(Font.BOLDWEIGHT_BOLD); // 宽度
font.setItalic(true); // 是否使用斜体*/
// font.setStrikeout(true); //是否使用划线
// 把字体应用到当前的样式
style.setFont(font);
// 指定当单元格内容显示不下时自动换行
style.setWrapText(true);
// 产生表格标题行
Row row = sheet.createRow(0);
for (int i = 0; i < headers.length; i++) {
Cell 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) {
Cell cell = row.createCell((short) cellIndex);
cell.setCellValue(str.toString());
cellIndex++;
}
index++;
}
}
}
}
导出测试类
package com.orient.tdm201.vehicleCheck.business;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
/**
* @author xks
* @date 2020/5/21 22:11
* @description : 导出Excel 测试
*/
public class TestExcel {
public static void main(String[] args) {
try {
File f = new File("d:" + File.separator + "test5.xls");
OutputStream out = new FileOutputStream(f);//导出本地
//OutputStream out = response.getOutputStream();//输出客户端
List<List<String>> data1 = new ArrayList<List<String>>();
List<List<String>> data2 = new ArrayList<List<String>>();
List<List<String>> data3 = new ArrayList<List<String>>();
for (int i = 1; i < 5; i++) {
List rowData = new ArrayList();
rowData.add(String.valueOf(i));
rowData.add("上海东霖柏鸿");
data1.add(rowData);
}
for (int i = 1; i < 5; i++) {
List rowData = new ArrayList();
rowData.add(String.valueOf(i));
rowData.add("深圳东霖柏鸿");
data2.add(rowData);
}
for (int i = 1; i < 5; i++) {
List rowData = new ArrayList();
rowData.add(String.valueOf(i));
rowData.add("广州东霖柏鸿");
data3.add(rowData);
}
String[] headers1 = {"ID1", "用户名1"};
String[] headers2 = {"ID2", "用户名2"};
String[] headers3 = {"ID3", "用户名3"};
ExportExcelUtilsTest eeu = new ExportExcelUtilsTest();//工具类写法在下面
HSSFWorkbook workbook = new HSSFWorkbook();
//使用WorkbookFactory 将excel也编译了 参考:https://blog.csdn.net/Chilies/article/details/80986322
// Workboo/k workbook = WorkbookFactory.create()
eeu.exportExcel(workbook, 0, "上海", headers1, data1, out);
eeu.exportExcel(workbook, 1, "深圳", headers2, data2, out);
eeu.exportExcel(workbook, 2, "广州", headers3, data3, out);
//原理就是将所有的数据一起写入,然后再关闭输入流。
workbook.write(out);
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}