当前工具类归纳了简单方便操作Excel的一些常用方法(仅供参考):
需要的一些 jar 包:
poi-3.17.jar
poi-examples-3.17.jar
poi-ooxml-3.17.jar
poi-ooxml-schemas-3.17.jar
package com.sys.commons.utils;
import java.io.*;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.util.*;
import org.apache.log4j.Logger;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
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.ss.usermodel.WorkbookFactory;
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 javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
* @author jiewai
*/
public class ExcelUtils {
private static final String EXTENSION_XLS = "xls";
private static final String EXTENSION_XLSX = "xlsx";
static Logger log = Logger.getLogger(ExcelUtils.class);
/**
* 创建新的Excel文件(写出 .xlsx 格式文件)
*
* @param outFilePath
* @param outFileName
* @param str
* @param sheetName
*/
public static void creatExcels(String outFilePath, String outFileName,
Object[] str, String sheetName) {
// TODO Auto-generated constructor stub
// 第一步,创建一个webbook,对应一个Excel文件
Workbook wb = new XSSFWorkbook();
// 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
Sheet sheet = wb.createSheet(sheetName);
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
for (int i = 0; i < 1; i++) {
Row row = sheet.createRow(i);
// 第五步,写入实体数据 实际应用中这些数据从数据库得到,
for (int j = 0; j < str.length; j++) {
Cell cell = row.createCell(j);
cell.setCellValue(str[j] == null ? "" : str[j].toString()); // 设置值
}
}
// 第六步,将文件存到指定位置, 写出到本地,如果需要写出到浏览器,只需替换当前步骤
try {
FileUtils.creatFileOrPath(outFilePath, "");
FileOutputStream fout = new FileOutputStream(outFilePath + outFileName);
wb.write(fout);
fout.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 在原有文件中追加数据(写入.xlsx 格式文件)
*
* @param outFilePath
* @param outFileName
* @param str
*/
public static void addToExcels(String outFilePath, String outFileName, Object[] str) {
FileInputStream fs;
try {
fs = new FileInputStream(outFilePath + outFileName);
XSSFWorkbook wb = (XSSFWorkbook) WorkbookFactory.create(fs);
XSSFSheet sheet = wb.getSheetAt(0); // 获取到工作表,excel可能有多个工作表(当前只满足追加第一个sheet文件的方法)
XSSFRow row;
FileOutputStream out = new FileOutputStream(outFilePath
+ outFileName); // 向已存在文件中写数据
row = sheet.createRow((short) (sheet.getLastRowNum() + 1)); // 在现有行号后追加数据
for (int j = 0; j < str.length; j++) {
XSSFCell cell = row.createCell(j);
cell.setCellValue(str[j] == null ? "" : str[j].toString()); // 设置值
}
out.flush();
wb.write(out);
out.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (EncryptedDocumentException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (InvalidFormatException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 获取以表头为 key 的 map 集合 list
* @param file 上传文件
* @return
*/
public static List<Map<String, Object>> loadAllExcelData(File file) {
// 检查
preReadCheck(file.getPath());
Workbook workbook;
List<Map<String, Object>> source = null;
try {
workbook = getWorkbook(file);
if (workbook.getNumberOfSheets() <= 0)
return null;
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {// 获取每个Sheet表
Sheet sheet = workbook.getSheetAt(i);
if (sheet.getLastRowNum() <= 0)
return null;
source = loadSheet(sheet);
}
} catch (IOException e) {
e.printStackTrace();
}
return source;
}
/**
* 加载当前sheet中的所有数据
*
* @param sheet
* @return
*/
private static List<Map<String, Object>> loadSheet(Sheet sheet) {
Iterator<Row> rows = sheet.iterator();
List<Map<String, Object>> source = new ArrayList<>();
Row row = rows.next();
Map<Integer, String> headers = new HashMap<>();
int index = 0;
for (int i = 0; i < row.getLastCellNum(); i++) {
Cell cell = row.getCell(i);
String value;
if (cell != null) {
value = cell.toString();
headers.put(i, value);
}
index++;
}
DecimalFormat df = new DecimalFormat("#.#########");
while (rows.hasNext()) {
Map<String, Object> lhhead = new HashMap<>();
Row r = rows.next();
Cell cell;
for (int i = 0; i < index; i++) {
cell = r.getCell(i);
Object value;
if (cell != null) {
if (headers.get(i) != null && !"".equals(headers.get(i))) {
switch (cell.getCellType()) {
case XSSFCell.CELL_TYPE_STRING:
value = cell.getRichStringCellValue().getString().trim();
break;
case XSSFCell.CELL_TYPE_NUMERIC:
value = df.format(cell.getNumericCellValue());
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
value = String.valueOf(cell.getBooleanCellValue()).trim();
break;
case XSSFCell.CELL_TYPE_FORMULA:
value = cell.getCellFormula();
break;
default:
value = "";
}
} else {
value = null;// 当列为空
}
lhhead.put(headers.get(i), value);
}
}
source.add(lhhead);
}
return source;
}
/**
* 获取文件扩展名
*
* @param path
* @return String
* @author zhang 2015-08-17 23:26
*/
private static String getExt(String path) {
if (path == null || path.equals("") || !path.contains(".")) {
return null;
} else {
return path.substring(path.lastIndexOf(".") + 1, path.length());
}
}
/***
* <pre>
* 取得Workbook对象(xls和xlsx对象不同,不过都是Workbook的实现类)
* xls:HSSFWorkbook
* xlsx:XSSFWorkbook
* @return
* @throws IOException
* </pre>
*/
private static Workbook getWorkbook(File file) throws IOException {
Workbook workbook = null;
InputStream is = new FileInputStream(file);
if (file.getPath().endsWith(EXTENSION_XLS)) {
workbook = new HSSFWorkbook(is);
} else if (file.getPath().endsWith(EXTENSION_XLSX)) {
workbook = new XSSFWorkbook(is);
}
return workbook;
}
/**
* 文件检查
*
* @param filePath 文件路径
*/
private static void preReadCheck(String filePath) {
// 常规检查
File file = new File(filePath);
if (!file.exists()) {
log.info("");
}
if (!(filePath.endsWith(EXTENSION_XLS) || filePath
.endsWith(EXTENSION_XLSX))) {
log.info("文件不是excel" + filePath);
}
}
/**
* 将已写入的 Excel 文件下载导出到浏览器端
*
* @param fileName 文件名
* @param wb Workbook 对象
* @param request HttpServletRequest 对象
* @param response HttpServletResponse 对象
*/
public static void writeFileToClient(String fileName, Workbook wb, HttpServletRequest request, HttpServletResponse response) {
try {
OutputStream fos;
fos = response.getOutputStream();
String userAgent = request.getHeader("USER-AGENT");
if (org.apache.commons.lang.StringUtils.contains(userAgent, "Mozilla")) {
fileName = new String(fileName.getBytes(), "ISO8859-1");
} else {
fileName = URLEncoder.encode(fileName, "utf8");
}
response.setCharacterEncoding("UTF-8");
response.setContentType("multipart/form-data");
response.setHeader("Content-Disposition", "Attachment;Filename=" + fileName);
wb.write(fos);
fos.flush();
fos.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 输出 Excel 到本地本地磁盘文件夹
*
* @param outFilePath 输出路径
* @param outFileName 输出文件名
* @param wb Workbook对象
*/
public static void printExcelFileToLocal(String outFilePath, String outFileName, Workbook wb) {
try {
FileUtils.creatFileOrPath(outFilePath, "");
FileOutputStream fout = new FileOutputStream(outFilePath + File.separator + outFileName);
wb.write(fout);
fout.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}