java中用POI方式实现EXCEL导入/导出
JAR
<!--poi-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17-beta1</version>
</dependency>
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>2.4.0</version>
</dependency>
工具类
package com.gt.hydrologicdata.utils;
import com.gt.hydrologicdata.enmus.ResultEnum;
import com.gt.hydrologicdata.entity.TableItemIndex;
import com.gt.hydrologicdata.handlers.CustomException;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.ObjectUtils;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* @program: HydrologicData
* @description:文件导入
* @author: KJH
* @create: 2019-09-16 11:36
*/
public class FileUtils {
public static ArrayList<Map<String, Object>> fileImport(MultipartFile file) throws CustomException {
ArrayList<Map<String, Object>> data = new ArrayList<>(); // 数据存储
if (file.isEmpty()) {
throw new CustomException(ResultEnum.FILE_NULL_ERROR);
}
String fileName = file.getOriginalFilename();
//判断文件是否是excel文件
if (!fileName.endsWith("xls") && !fileName.endsWith("xlsx")) {
throw new CustomException(ResultEnum.FILE_NOT_EXCEL);
}
Workbook workBook = getWorkBook(file);
if (null != workBook) {
// int numberOfSheets = workBook.getNumberOfSheets();//有多少张表
// if(numberOfSheets != 1) throw new CustomException(ResultEnum.FILE_EXCEL_NO_ONE_TABLE);
Sheet sheet = workBook.getSheetAt(0);
int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();//行数
if (physicalNumberOfRows > 0) {
//整合表格数据
Row rowHead = sheet.getRow(0);//第一行
int colums = rowHead.getPhysicalNumberOfCells();//列数
List<String> headList = new ArrayList<>();
for (int k = 0; k < colums; k++) {
String head = rowHead.getCell(k).getStringCellValue().toUpperCase();
//非法字符處理
headList.add(head);
}
for (int j = 0; j < physicalNumberOfRows; j++) {
if (j == 0) continue;//标题行
Map<String, Object> map = new HashMap<>();
Row row = sheet.getRow(j);
for (int k = 0; k < colums; k++) {
Cell cell = row.getCell(k);
String value = getStringValueFromCell(cell);
map.put(headList.get(k), value);
}
data.add(map);
}
}
}
return data;
}
public static Workbook getWorkBook(MultipartFile file) throws CustomException {
//获得文件名
String fileName = file.getOriginalFilename();
//创建Workbook工作薄对象，表示整个excel
Workbook workbook = null;
//获取excel文件的io流
InputStream is = null;
try {
is = file.getInputStream();
//根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
if (fileName.endsWith("xls")) {
//2003
workbook = new HSSFWorkbook(is);
} else if (fileName.endsWith("xlsx")) {
//2007
workbook = new XSSFWorkbook(is);
}
} catch (IOException e) {
e.printStackTrace();
throw new CustomException(ResultEnum.FILE_NOT_getWorkBook);
}
return workbook;
}
//表格单元格数据
private static String getStringValueFromCell(Cell cell) {
SimpleDateFormat sFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
DecimalFormat decimalFormat = new DecimalFormat("#.########");
String cellValue = null;
if (cell == null) {
return cellValue;
}
switch (cell.getCellTypeEnum()) {
case STRING: //String
cellValue = cell.getStringCellValue();
break;
case BOOLEAN://Boolean
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case NUMERIC://数值类型 - 整数，小数，时间
double v = cell.getNumericCellValue();
if (HSSFDateUtil.isCellDateFormatted(cell)) {//时间类型
Date date = HSSFDateUtil.getJavaDate(v);
cellValue = sFormat.format(date);
} else {
cellValue = decimalFormat.format(v);
}
break;
case BLANK://空白
cellValue = null;
break;
case _NONE://未知类型
cellValue = "";
break;
case ERROR://错误单元格
cellValue = "";
break;
case FORMULA://公式
cellValue = "";
break;
}
return cellValue;
}
/**
* 文件导出
*/
public static void fileExport(HttpServletResponse response, List<Map<String, Object>> headerAndDataList,
Date startTm, Date endTm) throws Exception {
OutputStream os = null;
SXSSFWorkbook workBook = null;
if (null != headerAndDataList && headerAndDataList.size() > 0) {
try {
// 声明一个工作薄
// XSSFWorkbook和SXSSFWorkbook 当数据量超出65536条后，在使用HSSFWorkbook或XSSFWorkbook，程序会报OutOfMemoryError：Javaheap
// space;内存溢出错误。这时应该用SXSSFworkbook
workBook = new SXSSFWorkbook();
// 生成一个表格
SXSSFSheet sheet = workBook.createSheet();
sheet.createFreezePane(0, 1, 0, 1);
workBook.setSheetName(0, "sheet1");
CellStyle style = workBook.createCellStyle();
Font font = workBook.getFontAt((short) 0);
font.setCharSet(HSSFFont.DEFAULT_CHARSET);
font.setFontHeightInPoints((short) 12);//更改默认字体大小
font.setFontName("宋体");//
style.setFont(font);
int dataListNum = 1;
// int iNum = 0;
SXSSFRow titleRow = sheet.createRow(0);
//获取第一行的信息，并填充到excel第一行
List<TableItemIndex> dataListOne = (List<TableItemIndex>) headerAndDataList.get(0).get("header");
titleRow.createCell(0).setCellValue(dataListOne.get(0).getCtname());
titleRow.createCell(1).setCellValue(dataListOne.get(0).getTname());
titleRow = sheet.createRow(1);
//把查询到的数据填充到excel
for (int i = 0; i < headerAndDataList.size(); i++) {
Map<String, Object> maps = headerAndDataList.get(i);
List<TableItemIndex> headerList = (List<TableItemIndex>) maps.get("header");//表的表头数据集
List<Map<String, Object>> dataList = (List<Map<String, Object>>) maps.get("data");//表的数据集
// 创建表格标题行 第一行已存在，所以从第二行开始
if (i > 0) {
// iNum = i;
//记录上一个dataList数据的size
List<Map<String, Object>> dataListPrior =
(List<Map<String, Object>>) headerAndDataList.get(i - 1).get("data");
dataListNum = dataListPrior.size() + dataListNum + 2;//2等价于1+1，前一个1代表空一行，后一个代表新添加一行中文表名和英文表名
titleRow = sheet.createRow(dataListNum);
titleRow.createCell(0).setCellValue(headerList.get(0).getCtname());
titleRow.createCell(1).setCellValue(headerList.get(0).getTname());
//新添加一行表头
dataListNum = dataListNum + 1;
titleRow = sheet.createRow(dataListNum);
}
//添加表头
for (int j = 0; j < headerList.size(); j++) {
TableItemIndex tableItemIndex = headerList.get(j);
titleRow.createCell(j).setCellValue(tableItemIndex.getCfname());
}
//添加数据
for (int m = 0; m < dataList.size(); m++) {
int rows = i == 0 ? m + 1 : m;
if (i != 0) {
rows = dataListNum + rows;
}
Map<String, Object> map = dataList.get(m);
SXSSFRow titleRowNext = sheet.createRow(rows + 1);
for (int n = 0; n < headerList.size(); n++) {
String fname = headerList.get(n).getFname();
Object o = map.get(fname);//获取表字段英文名称
//如果o是空，调用大写获取表字段英文名称(数据库字段可能存在大小写问题)
if (ObjectUtils.isEmpty(o)) {
o = map.get(fname.toUpperCase());
}
titleRowNext.createCell(n).setCellValue(String.valueOf(o));
}
}
}
os = response.getOutputStream();
String fileName = DateUtils.getCustomDateChangeString(startTm, "yyyy年MM月dd日") + "_"
+ DateUtils.getCustomDateChangeString(endTm, "yyyy年MM月dd日");
response.reset();//清除首部的空白行
response.addHeader("Content-Disposition",
"attachment;filename=" + new String((fileName + ".xlsx").getBytes(
"UTF-8"), "ISO-8859-1"));
workBook.write(os);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (os != null) {
os.flush();
os.close();
}
} catch (IOException e) {
throw new RuntimeException("导出报表异常");
}
}
}else {
String fileName ="DATA_IS_NULL";
response.reset();//清除首部的空白行
response.addHeader("Content-Disposition",
"attachment;filename=" + new String((fileName + ".xlsx").getBytes(
"UTF-8"), "ISO-8859-1"));
}
}
}
表头字段可以自己后台自定义维护，也可以放到excel中自己获取
调用文件工具
public void fileLoad(MultipartFile file) throws Exception {
List<Map<String, Object>> mapList = FileUtils.fileImport(file,tname);
数据处理（批量/单个添加数据库）
}
版权声明：本文为博主原创文章，转载请附上博文链接！
https://blog.csdn.net/qq_29379559/article/details/105492507
![Java小案例——随机数猜测随机数猜测[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)