背景:
web应用经常需要上传文件，有时候需要解析出excel中的数据，如果excel的格式没有问题，那就可以直接解析数据入库。
工具选择:
目前jxl和poi可以解析excel，jxl很早就停止维护了，只支持excel-2003也就是xls格式的文件；
poi可支持xls和xlsx格式的文件，经过考察，poi的功能强大很多，所以选择这个工具解析excel。文件上传在之前的一个专题有所提及。
需要如下jar包，jar包见附件，也可在官网下载。
注意：
1. 不支持单元格合并的情况，默认表格格式规范，格式规范指：
从第一行第一列开始，第一行为表头，后面全都是数据，每一个列代表一个字段。没有中途断掉的行或者列，允许单元格为空。
2. 代码初步运用了重构中的方法抽取(extract method)和方法对象抽取(extract method object)技术，代码行数前后没有太大的变化，但是结构明显要清楚一些。
只不过初步使用，有些抽取不是特别直观，写文件的两个方法没有使用重构技术，此外在单元格类型匹配，也还有深挖的空间，希望读者根据自己的需要，自行定制。
关于重构技术和解析excel的api，请参考《重构改善既有代码的设计》以及poi的源代码
package com.test;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* Created by tm on 2016/4/11.
*
* @author tm
* 使用POI解析和生成excel，暂不支持单元格合并情况，默认是标准格式的excel
* 实测同等的数据量（3张sheet超过2w条），xlsx的速度要远慢于xls的解析速度。
* 同等数据量：
* xls的解析，如果只取表头，不超过500毫秒；如果取整个数据，时间不超过2000毫秒；
* 而xlsx的解析，如果只取表头，需要2000毫秒左右，如果取整个数据，则在3000毫秒以上。
* 以上耗时不同系统不同硬件有不同结果，仅作为相对比较结果。
* 这个类提供三个功能：
* 1.获得表格的表头所有列,这个功能可以酌情去掉,因为获得表格的所有数据也就获得了所有列.
* 2.获得表格的所有数据
* 3.传入数据写出来
*/
public class ExcelUtil {
public static final String SUFFIX_XLS = "xls";
public static final String SUFFIX_XLSX = "xlsx";
public static final String NO_FILE_NULL = "NO FILE NULL";
public static final String NO_FILE_EMPTY = "NO FILE EMPTY";
/**
* 获得表格的列
* @param fileName
* @return
* @throws RuntimeException
*/
public static List<Map<String, Object>> getTableSheetCol(String fileName) throws RuntimeException{
if(!valid(fileName))
return null;
else{
if(validExcelFormat(fileName,SUFFIX_XLS)){
//是excel2003？
return getXlsSheetItem(fileName);
}
if(validExcelFormat(fileName, SUFFIX_XLSX)){
//是excel2007以后
return getXlsxSheetItem(fileName);
}else{
print("nonsupport file format");
print("fileFormat : " + getFileNameSuffix(fileName));
throw new RuntimeException("nonsupport file format, please check input fileName again");
}
}
}
/**
* getXlsSheetItem ： 读取xls格式文件的所有sheet表的所有列名集合。
*
* @param fileName 文件名
* 默认表格格式规范，列名全部为字符串。
*/
private static List<Map<String, Object>> getXlsSheetItem(String fileName) {
TableObject tableObject = new TableObject(fileName).invoke();
HSSFWorkbook book = tableObject.getHssfBook();
List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
for (int i = 0; i < tableObject.getSheet_number(); i++) {
Sheet sheet = book.getSheetAt(i);
if (ifSheetNullOrEmpty(sheet)) continue;
Row row = sheet.getRow(0);
if (ifRowNullOrEmpty(row)) continue;
result.add(packageColsWithSheetName(book, i, row));
}
return result;
}
/**
* getXlsxSheetItem ： 读取xlsx格式文件的所有sheet表的所有列名集合。
*
* @param fileName 文件名
* 默认表格格式规范，列名全部为字符串。
* 方法还可以进一步化简，可参考 getExcelData 进一步整合。
*/
private static List<Map<String, Object>> getXlsxSheetItem(String fileName) {
TableObject tableObject = new TableObject(fileName).invoke();
XSSFWorkbook book = tableObject.getXssfBook();
List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
for (int i = 0; i < tableObject.getSheet_number(); i++) {
Sheet sheet = book.getSheetAt(i);
if (ifSheetNullOrEmpty(sheet)) continue;
Row row = sheet.getRow(0);
if (ifRowNullOrEmpty(row)) continue;
result.add(packageColsWithSheetName(book, i, row));
}
return result;
}
private static Map<String, Object> packageColsWithSheetName(Workbook book, int i, Row row) {
Map<String, Object> map = new HashMap<String, Object>();
String sheet_name = book.getSheetName(i);
List<String> cols = getCols(row);
map.put("sheet_name", sheet_name);
map.put("cols", cols);
return map;
}
private static List<String> getCols(Row row) {
List<String> cols = new ArrayList<String>();
System.out.println(row.getLastCellNum());
for (int j = 0; j < row.getLastCellNum(); j++) {
Object obj = row.getCell(j);
cols.add(obj == null ? "" : obj.toString());
}
return cols;
}
private static boolean ifRowNullOrEmpty(Row row) {
if (row == null || row.getLastCellNum() == 0 || row.getCell(0) == null) {
return true;
}
return false;
}
private static boolean ifSheetNullOrEmpty(Sheet sheet) {
if (sheet == null || sheet.getLastRowNum() == 0) {
return true;
}
return false;
}
/**
* 获取表格的全部数据
* @param fileName 文件名
* @return
* @throws RuntimeException
*/
public static Map<String, List<Map<String, Object>>> getTableSheetData(String fileName) throws RuntimeException{
if(!valid(fileName)) {
print("文件名校验失败");
return null;
}
else{
return getExcelData(fileName);
}
}
/**
* 根据文件后缀格式，确定调用哪种Workbook，此处运用了多态，具体的解析操作都用的是接口。
* @param fileName
* @return
*/
private static Map<String, List<Map<String, Object>>> getExcelData(String fileName) {
TableObject tableObject = new TableObject(fileName).invoke();
Workbook book = null;
if(validExcelFormat(fileName,SUFFIX_XLS)){
//是excel2003？
book = tableObject.getHssfBook();
}
else if(validExcelFormat(fileName, SUFFIX_XLSX)){
//是excel2007以后
book = tableObject.getXssfBook();
}else{
print("nonsupport file format");
throw new RuntimeException("nonsupport file format, please check input fileName again");
}
Map<String, List<Map<String, Object>>> result = new HashMap<String, List<Map<String, Object>>>();
for (int i = 0; i < tableObject.getSheet_number(); i++) {
List<Map<String, Object>> sheet_data = new ArrayList<Map<String, Object>>();
Sheet sheet = book.getSheetAt(i);
if (ifSheetNullOrEmpty(sheet)) continue;
String sheet_name = book.getSheetName(i);
System.out.println(sheet.getLastRowNum());
for (int j = 1; j <= sheet.getLastRowNum(); j++) {
Row row = sheet.getRow(j);
if (ifRowNullOrEmpty(row)) continue;
Map<String, Object> record = new HashMap<String, Object>();
Row first = sheet.getRow(0);
getRowData(row, record, first);
sheet_data.add(record);
}
result.put(sheet_name, sheet_data);
}
return result;
} /**
* 此处有个点要注意,getLastCellNum,下标是从1开始,有多少列,这里就是这个值.而getLastRowNum,下标是从0开始,也就是21行的表格,这里获得的值是20.用户可自行验证.
* @param row 该行记录
* @param record 返回值
* @param first 表头
*/
private static void getRowData(Row row, Map<String, Object> record, Row first) {
for (int k = 0; k < row.getLastCellNum(); k++) {
String value;
if (row.getCell(k) == null) {
value = "";
} else {
value = parseDate(row.getCell(k));
if (value.endsWith("00:00:00")) {
value = value.substring(0, value.lastIndexOf("00:00:00"));
}
}
record.put(first.getCell(k).toString(), value);
}
}
/**
* 判断单元格格式，转化日期格式,日期在poi里保存的是数字,所以这里要转化一下.
* @param cell 单元格
* @return
*/
private static String parseDate(Cell cell) {
String result = "";
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:// 数字类型
if (DateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
SimpleDateFormat sdf = null;
if (cell.getCellStyle().getDataFormat() == HSSFDataFormat
.getBuiltinFormat("yyyy/MM/dd")) {
sdf = new SimpleDateFormat("yyyy/MM/dd");
} else {// 日期
sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
}
Date date = cell.getDateCellValue();
result = sdf.format(date);
} else if (cell.getCellStyle().getDataFormat() == 58) {
// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
double value = cell.getNumericCellValue();
Date date = org.apache.poi.ss.usermodel.DateUtil
.getJavaDate(value);
result = sdf.format(date);
} else {
double value = cell.getNumericCellValue();
CellStyle style = cell.getCellStyle();
DecimalFormat format = new DecimalFormat();
String temp = style.getDataFormatString();
// 单元格设置成常规
if (temp.equals("General")) {
format.applyPattern("#");
}
result = format.format(value);
}
break;
case Cell.CELL_TYPE_STRING:// String类型
result = cell.getRichStringCellValue().toString();
break;
case Cell.CELL_TYPE_BLANK:
result = "";
break;
default:
result = "";
break;
}
return result;
}
/**
* ifXls：判断是否为 xls 文件
*
* @param fileName 包含后缀的文件名
*/
private static boolean validExcelFormat(String fileName,String type) {
if(getOS().contains("win")){
return valid(fileName) && getFileNameSuffix(fileName).equalsIgnoreCase(type);
}else if(getOS().contains("linux")){
return valid(fileName) && getFileNameSuffix(fileName).equals(type);
}else{
print("System OS is not windows or linux");
throw new RuntimeException("System OS is not windows or linux , if you use this class in another sys,please implement yourself");
}
}
private static String getOS() { return System.getProperty("os.name").toLowerCase(); }
private static void print(String msg) {
System.out.println(msg);
}
private static String getFileNameSuffix(String fileName) {
return fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());
}
/**
* valid :判断文件是否存在
*
* @param fileName fileName 文件名。
* 区分为文件名为null，或者为空。
*/
private static boolean valid(String fileName) {
return !ifNull(fileName) && !ifEmpty(fileName);
}
private static boolean ifEmpty(String fileName) {
if (fileName.equals("")) {
print(NO_FILE_EMPTY);
return true;
}
return false;
}
private static boolean ifNull(String fileName) {
if (fileName == null) {
print(NO_FILE_NULL);
return true;
}
return false;
}
/**
* 将excel文件写出成txt格式
* 此处没有指定输出格式
* @param filePath 输入文件路径
* @param destFilePath 输出文件路径
* @throws Exception
*/
public static void writeExcel2Txt(String filePath,String destFilePath) throws Exception {
BufferedWriter bufferedWriter = null;
try {
FileWriter fw = new FileWriter(destFilePath);
bufferedWriter = new BufferedWriter(fw);
} catch (IOException e) {
e.printStackTrace();
return;
}
Map<String, List<Map<String, Object>>> data = getTableSheetData(filePath);
System.out.println(data.toString());
if(data==null){
throw new Exception("解析数据失败");
}
for (int i = 0; i < data.size(); i++) {
try {
bufferedWriter.write(DataTransformUtil.mapGetKeyList(data).get(i));
System.out.println(DataTransformUtil.mapGetKeyList(data).get(i));
bufferedWriter.newLine();
bufferedWriter.write("===========================================================");
bufferedWriter.newLine();
} catch (IOException e) {
e.printStackTrace();
}
System.out.println("==========================================");
List<Map<String, Object>> sheet = data.get(DataTransformUtil.mapGetKeyList(data).get(i));
for (Map map : sheet) {
System.out.println(map.toString());
bufferedWriter.write(map.toString());
bufferedWriter.newLine();
}
System.out.println();
bufferedWriter.newLine();
}
bufferedWriter.flush();
bufferedWriter.close();
}
/***
* writeXlsxFile：将指定数据集生成为excel文件。
*
* @param data 一个包含record的List集合
* @param name 文件名【带后缀】
* @param path 路径名
* 只是简单输出，尚未设置样式，单元格格式也未深究。
*/
public static String writeXlsxFile(List<Map<String, Object>> data, String path, String name) {
// 创建Excel的工作书册 Workbook,对应到一个excel文档
Workbook book = null;
if(validExcelFormat(name,SUFFIX_XLS)){
book = new HSSFWorkbook();
}else if(validExcelFormat(name,SUFFIX_XLSX)){
book = new XSSFWorkbook();
}else {
print("nonsupport file format");
throw new RuntimeException("nonsupport file format, please check input fileName again");
}
// 创建Excel的工作sheet,对应到一个excel文档的tab
Sheet sheet = book.createSheet("sheet1");
sheet.setColumnWidth(0, 4000);
sheet.setColumnWidth(1, 3500);
// 创建Excel的sheet的一行
int c = data.get(0).size();
List cols = DataTransformUtil.mapGetKeyList(data.get(0));
Row head = sheet.createRow(0);
for (int j = 0; j < cols.size(); j++) {
Cell cell = head.createCell(j, 1);
cell.setCellValue(cols.get(j).toString());
}
for (int i = 0; i < data.size(); i++) {
Row row = sheet.createRow(i + 1);
for (int j = 0; j < c; j++) {
Cell cell = row.createCell(j, 1);
cell.setCellValue(data.get(i).get(cols.get(j).toString()).toString());
}
}
FileOutputStream os = null;
try {
os = new FileOutputStream(new File(new File(path), name));
book.write(os);
os.close();
} catch (Exception e) {
e.printStackTrace();
}
return name ;
}
public static void main(String[] args) throws Exception {
// String str="[{'name':'zhang3','age':33,'gender':'f'}," +
// "{'name':'li4','age':44,'gender':'m'}," +
// "{'name':'wang5','age':55,'gender':'m'}," +
// "{'name':'wang5','age':55,'gender':'m'}," +
// "{'name':'wang5','age':55,'gender':'m'}]";
// List<Map<String,Object>> list=DataTransformUtil.nestJsonArrayString2List(str);
// System.out.println(list.toString());
// writeXlsxFile(list,"F:\\","test");
long start = System.currentTimeMillis();
System.out.println("start ：" + System.currentTimeMillis());
// writeExcel2Txt("F:\\152657884.xls","F:\\123.txt");
//System.out.println(getTableSheetCol("F:\\需要本地开发功能列表.xlsx"));
System.out.println(getTableSheetData("F:\\需要本地开发功能列表.xlsx"));
}
/**
* extract method object
*/
private static class TableObject {
private String fileName;
private XSSFWorkbook xssfBook;
private HSSFWorkbook hssfBook;
private int sheet_number;
public TableObject(String fileName) {
this.fileName = fileName;
}
public XSSFWorkbook getXssfBook() {
return xssfBook;
}
public HSSFWorkbook getHssfBook() {
return hssfBook;
}
public int getSheet_number() {
return sheet_number;
}
public TableObject invoke() {
xssfBook = null;
hssfBook = null;
sheet_number = 0;
try {
FileInputStream is = new FileInputStream(new File(fileName));
if(validExcelFormat(fileName,SUFFIX_XLS)){
hssfBook = new HSSFWorkbook(is);
sheet_number = hssfBook.getNumberOfSheets();
}else if(validExcelFormat(fileName,SUFFIX_XLSX)){
xssfBook = new XSSFWorkbook(is);
sheet_number = xssfBook.getNumberOfSheets();
}
} catch (IOException e) {
e.printStackTrace();
}
return this;
}
}
}
发现有个WorkBookFactory的api方法,可以直接拿到对应的HSSFWorkbook或XSSFWorkbook
所以说,研究清楚api才是正道啊……
大牛博客传送门：http://blog.csdn.net/lovesomnus/article/details/23843549