javaPOI导入导出Excel工具类,主要适用于读取单行单列,根据自己需要修改代码即可。
首先引入maven依赖:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.8</version>
</dependency>
工具类代码:
package com.yss.db.util;
import com.itextpdf.text.log.SysoCounter;
import com.yss.base.common.exception.BaseException;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.util.*;
public class ExportUtils {
/**
* 导入读取单元个内容
* 返回如果需要转换类型,请判断是否为空,或者空字符串
*
* @return
*/
// 读Excel数据
public static List<ArrayList<String>> readExcel(MultipartFile file)
throws BaseException {
//获取文件名
String fileName = file.getOriginalFilename();
if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
throw new BaseException("上传文件格式不正确");
}
Workbook workbook = null;
InputStream is = null;
try {
is = file.getInputStream();
if (fileName.endsWith("xlsx")) {
workbook = new XSSFWorkbook(is);
}
if (fileName.endsWith("xls")) {
workbook = new HSSFWorkbook(is);
}
} catch (Exception e) {
throw new BaseException("导入失败!");
}
List<ArrayList<String>> excelData = null;
// 读取第一个工作簿
Sheet sheet = workbook.getSheetAt(0);
// sheet.protectSheet("111111"); //设置表单保护密码
int endRowNum = sheet.getLastRowNum();
if (endRowNum > 2000) {
throw new BaseException("导入失败,最多只能导入2000行数据!");
}
try {
Row titleRow = sheet.getRow(0);
// 获取sheet的行数
int rowCount = sheet.getPhysicalNumberOfRows();
// 循环行
excelData = new ArrayList<ArrayList<String>>();
int sumValue = 0;
nullrow:
for (int i = 0; i < rowCount; i++) {
Row row = sheet.getRow(i);
if (i == 0) {
sumValue = row.getLastCellNum();
}
// int cells = row.getLastCellNum();
// int cells = row.getPhysicalNumberOfCells();
ArrayList<String> rowDataArr = new ArrayList<String>();
for (int j = 0; j < sumValue; j++) {
Cell cell = row.getCell(j);
String stringCellValue = getStringCellValue(cell);
rowDataArr.add(stringCellValue);
}
excelData.add(rowDataArr);
}
} catch (Exception e) {
if ("导入失败,请使用本系统导出的Excel文件导入!".equals(e.getMessage())) {
throw new BaseException(e.getMessage());
} else {
throw new BaseException("导入失败,请检查文件是否有误!");
}
}
return excelData;
}
/**
* 读取单元格内容,支持获取函数内容
*
* @param cell
* @return
*/
public static String getStringCellValue(Cell cell) {
String strCell = "";
if (cell == null) {
return strCell;
}
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
strCell = cell.getRichStringCellValue().getString().trim();
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
strCell = DateUtils.format(cell.getDateCellValue(), "yyyy-MM-dd HH:mm:ss");
} else {
BigDecimal numberic = new BigDecimal(cell.getNumericCellValue());
strCell = numberic.toPlainString();
}
break;
case Cell.CELL_TYPE_BOOLEAN:
strCell = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
evaluator.evaluateFormulaCell(cell);
CellValue cellValue = evaluator.evaluate(cell);
if (StringUtils.isBlank(cellValue.getStringValue())) {
strCell = String.valueOf(cellValue.getStringValue());
} else {
strCell = String.valueOf(cellValue.getNumberValue());
}
strCell = String.valueOf(cellValue.getNumberValue());
break;
default:
strCell = "";
}
return strCell;
}
/**
* @headerList 所要导出excel表头
* @注释 请按照顺序传入相关信息
*/
public static List excelHeaderList(String strHeader) {
//设置表头集为list<map<String,String>>
List headerList = new ArrayList();
int j = 8;
// String strHeader = "订单编号,收款方,发放账户,充值金额,服务费率,服务费,订单总金额,订单状态,下单时间,";
String[] split = strHeader.split(",");
for (int i = 0; i < split.length; i++) {
Map<String, String> header = new LinkedHashMap<String, String>();
header.put("header" + i, split[i]);
headerList.add(header);
}
return headerList;
}
/**
* @allList 导出数据集map
* @headerList 所要导出excel表头
* @fileName 导出文件名
* @注释 请按照顺序传入相关信息
*/
@SuppressWarnings({"unused", "resource"})
public static void exportingReport(List<Map<String, String>> allList,
List<Map<String, String>> headerList,
String fileName,
HttpServletResponse response) {
String cnumber = "";
// 第一步,创建一个webbook,对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
// 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
String title = "";
HSSFSheet sheet = wb.createSheet();
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow row = sheet.createRow((int) 0);
// 第四步,创建单元格,并设置值表头 设置表头居中
// HSSFCellStyle style = wb.createCellStyle();
// style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
Font font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
CellStyle createCellStyle = wb.createCellStyle();
createCellStyle.setAlignment(CellStyle.ALIGN_CENTER);// 水平居中
createCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直居中
createCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
createCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
createCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
createCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
createCellStyle.setFont(font);
// 只要在已有的用户管理界面输出个 包含“员工编号” 、“姓名”、“开户行”、“卡号 ”
// 这四个字段的excel表就行。然后我填好后导入。
row.setHeightInPoints(25);
createCellStyle.setFillForegroundColor(IndexedColors.TEAL.getIndex());
createCellStyle.setFillForegroundColor(HSSFColor.LIME.index);
//createCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);//背景色
HSSFCell cell = null;
//获取表头
for (int i = 0; i < headerList.size(); i++) {
Map<String, String> map = headerList.get(i);
cell = row.createCell(i);
cell.setCellValue(map.get("header" + i));
cell.setCellStyle(createCellStyle);
sheet.autoSizeColumn(i);
sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);
}
// 第五步,写入实体数据 实际应用中这些数据从数据库得到,
Font font1 = wb.createFont();
CellStyle createCellStyle1 = wb.createCellStyle();
createCellStyle1.setAlignment(CellStyle.ALIGN_CENTER);// 水平居中
createCellStyle1.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直居中
createCellStyle1.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
createCellStyle1.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
createCellStyle1.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
createCellStyle1.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
createCellStyle1.setFont(font1);
//判断数据集是否为空
if (null != allList) {
for (int i = 0; i < allList.size(); i++) {
row = sheet.createRow((int) i + 1);
Map<String, String> map = allList.get(i);
int j = 0;
for (Map.Entry<String, String> entry : map.entrySet()) {
// 第四步,创建单元格,并设置值
// 订单号
HSSFCell cells = row.createCell(j);
cells.setCellStyle(createCellStyle1);
cells.setCellValue(entry.getValue());
row.setHeightInPoints(25);
sheet.autoSizeColumn(j);
sheet.setColumnWidth(j, sheet.getColumnWidth(j) * 17 / 10);
// sheet.setColumnWidth(j, 10 * 512);
j++;
}
}
}
// 第六步,将文件存到指定位置
// try {
// FileOutputStream fout = new FileOutputStream("D:/"+fileName+".xls");
// wb.write(fout);
// fout.close();
// } catch (Exception e) {
// e.printStackTrace();
// }
try {
// 设置response的Header
response.setContentType("application/-excel;charset=utf-8");
String name = java.net.URLEncoder.encode(fileName + ".xls", "utf-8");
response.addHeader("Content-Disposition", "attachment; filename="
+ name + "; filename*=utf-8''" + name);
// response.addHeader("Content-Length", "" + response.);
OutputStream toClient = new BufferedOutputStream(
response.getOutputStream());
wb.write(toClient);
toClient.flush();
toClient.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}