Excel导入与导出
这两天看了看项目中Excel导入导出功能，在此整理了一下相关代码，方便以后使用，项目结构如下：该项目为web项目，主要有两个包，里面分别包含导入和导出的工具类和demo，可本地运行，也可在tomca上运行模拟真实的web请求。源码下载地址：https://download.****.net/download/diweikang/11006639
导入
public class ReadExcel {
/**
* 读取 office 2003 excel
*
* @throws IOException
* @throws FileNotFoundException
*/
public static List<List<Object>> read2003Excel(InputStream in) throws IOException {
List<List<Object>> list = new LinkedList<List<Object>>();
HSSFWorkbook hwb = new HSSFWorkbook(in);
HSSFSheet sheet = hwb.getSheetAt(0);
Object value = null;
HSSFRow row = null;
HSSFCell cell = null;
int counter = 0;
for (int i = sheet.getFirstRowNum(); counter < sheet.getPhysicalNumberOfRows(); i++) {
row = sheet.getRow(i);
if (row == null) {
continue;
} else {
counter++;
}
List<Object> linked = new LinkedList<Object>();
for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
cell = row.getCell(j);
if (cell == null) {
continue;
}
DecimalFormat df = new DecimalFormat("0");// 格式化 number String
// 字符
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
switch (cell.getCellType()) {
case XSSFCell.CELL_TYPE_STRING:
System.out.println(i + "行" + j + " 列 is String type");
value = cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_NUMERIC:
System.out.println(
i + "行" + j + " 列 is Number type ; DateFormt:" + cell.getCellStyle().getDataFormatString());
if ("@".equals(cell.getCellStyle().getDataFormatString())) {
value = df.format(cell.getNumericCellValue());
} else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
value = nf.format(cell.getNumericCellValue());
} else {
value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
}
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
System.out.println(i + "行" + j + " 列 is Boolean type");
value = cell.getBooleanCellValue();
break;
case XSSFCell.CELL_TYPE_BLANK:
System.out.println(i + "行" + j + " 列 is Blank type");
value = "";
break;
default:
System.out.println(i + "行" + j + " 列 is default type");
value = cell.toString();
}
if (value == null || "".equals(value)) {
continue;
}
linked.add(value);
}
list.add(linked);
}
return list;
}
/**
* 读取Office 2007 excel
*/
public static List<List<Object>> read2007Excel(InputStream in) throws IOException {
List<List<Object>> list = new LinkedList<List<Object>>();
// 构造 XSSFWorkbook 对象，strPath 传入文件路径
XSSFWorkbook xwb = new XSSFWorkbook(in);
// 读取第一章表格内容
XSSFSheet sheet = xwb.getSheetAt(0);
Object value = null;
XSSFRow row = null;
XSSFCell cell = null;
int counter = 0;
for (int i = sheet.getFirstRowNum(); counter < sheet.getPhysicalNumberOfRows(); i++) {
row = sheet.getRow(i);
if (row == null) {
continue;
} else {
counter++;
}
List<Object> linked = new LinkedList<Object>();
for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
cell = row.getCell(j);
if (cell == null) {
continue;
}
DecimalFormat df = new DecimalFormat("0");// 格式化 number String
// 字符
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
switch (cell.getCellType()) {
case XSSFCell.CELL_TYPE_STRING:
System.out.println(i + "行" + j + " 列 is String type");
value = cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_NUMERIC:
System.out.println(
i + "行" + j + " 列 is Number type ; DateFormt:" + cell.getCellStyle().getDataFormatString());
if ("@".equals(cell.getCellStyle().getDataFormatString())) {
value = df.format(cell.getNumericCellValue());
} else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
value = nf.format(cell.getNumericCellValue());
} else {
value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
}
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
System.out.println(i + "行" + j + " 列 is Boolean type");
value = cell.getBooleanCellValue();
break;
case XSSFCell.CELL_TYPE_BLANK:
System.out.println(i + "行" + j + " 列 is Blank type");
value = "";
break;
default:
System.out.println(i + "行" + j + " 列 is default type");
value = cell.toString();
}
if (value == null || "".equals(value)) {
continue;
}
linked.add(value);
}
list.add(linked);
}
return list;
}
public static void main(String[] args) {
try {
File file = new File("D:\\home\\export2007_a.xlsx");
String fileName = file.getName();
String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName.substring(fileName.lastIndexOf(".") + 1);
if ("xls".equals(extension)) {
List<List<Object>> list = read2003Excel(new FileInputStream(file));
System.out.println(list);
} else if ("xlsx".equals(extension)) {
List<List<Object>> list = read2007Excel(new FileInputStream(file));
System.out.println(list);
} else {
throw new IOException("不支持的文件类型");
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
导出
此处指提供Excel2007导出代码，Excel2003导出可下载源码
/**
* 利用开源组件POI3.0.2动态导出EXCEL文档
*
* 应用泛型，代表任意一个符合javabean风格的类
* 注意这里为了简单起见，boolean型的属性xxx的get器方式为getXxx(),而不是isXxx() byte[]表jpg格式的图片数据
*/
public class ExportExcel2007<T> {
public static final String FILE_SEPARATOR = System.getProperties().getProperty("file.separator");
public void createExcelAppendData(XSSFWorkbook workbook, AbstractExcelEnum[] excelEnums, List<?> dataset)
throws Exception {
createExcelAppendData("测试POI导出EXCEL文档", workbook, excelEnums, dataset);
}
/**
* 这是一个通用的方法，将放置在JAVA集合中并且符合一定条件的数据以EXCEL的形式输出到指定IO设备上
*
* 该方法利用反射将javabean指定的的属性导出到Excel
*
* @param title
* 表格标题名
* @param workbook
* 工作薄
* @param excelEnums
* 枚举值：包含字段、字段名（标题）
* @param dateList
* 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
* javabean属性的数据类型有基本数据类型及String,Date
*
*/
public void createExcelAppendData(String title, XSSFWorkbook workbook, AbstractExcelEnum[] excelEnums,
List<?> dateList) throws Exception {
XSSFSheet sheet = null;
sheet = workbook.createSheet(title);
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth((short) 15);
int initRowNum = sheet.getLastRowNum();
// 原来无数据，则先写表头
if (initRowNum == 0) {
// 表头格式
XSSFCellStyle headCellStyle = createExcelHeadStyle(workbook);
createTabHeader(sheet, excelEnums, headCellStyle);
}
appendDataToSheet(workbook, sheet, dateList, excelEnums);
}
/**
* 定义表头单元格样式
*
* @param workbook
* @return
*/
private static XSSFCellStyle createExcelHeadStyle(XSSFWorkbook workbook) {
// 生成一个样式
XSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
style.setBorderRight(XSSFCellStyle.BORDER_THIN);
style.setBorderTop(XSSFCellStyle.BORDER_THIN);
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
// 生成一个字体
XSSFFont font = workbook.createFont();
font.setColor(HSSFColor.VIOLET.index);
font.setFontHeightInPoints((short) 12);
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
return style;
}
/**
* 定义表头单元格样式
*
* @param workbook
* @return
*/
private static XSSFCellStyle createExcelBodyStyle(XSSFWorkbook workbook) {
// 生成并设置另一个样式
XSSFCellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
style.setBorderRight(XSSFCellStyle.BORDER_THIN);
style.setBorderTop(XSSFCellStyle.BORDER_THIN);
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
// 生成另一个字体
XSSFFont font = workbook.createFont();
font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
// 把字体应用到当前的样式
style.setFont(font);
return style;
}
/**
* 创建表头
*
* @param sheet
* @param enums
* @param headCellStyle
*/
private static void createTabHeader(XSSFSheet sheet, AbstractExcelEnum[] enums, XSSFCellStyle cellStyle) {
XSSFRow row = sheet.createRow(0);
row.setHeight((short) (1.5 * 256)); // 设置行高
for (int i = 0; i < enums.length; i++) {
XSSFCell cell = row.createCell(i);
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(cellStyle);
cell.setCellValue(enums[i].getColumn());
}
}
/**
* 将list的值写入sheet
*
* @param list
* @param excelEnums
* @return
*/
public void appendDataToSheet(XSSFWorkbook wb, XSSFSheet sheet, List<?> dataset, AbstractExcelEnum[] excelEnums)
throws Exception {
// 单元格样式
XSSFCellStyle cellStyle = wb.createCellStyle();
int initRow = sheet.getLastRowNum();
if (CollectionUtils.isNotEmpty(dataset)) {
for (int i = initRow; i < dataset.size() + initRow; i++) {
// 创建每行的数据
XSSFRow row = sheet.createRow(i + 1);
Object vo = dataset.get(i - initRow);
for (int j = 0; j < excelEnums.length; j++) {
XSSFCell cell = row.createCell(j);
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(cellStyle);
AbstractExcelEnum excelEnum = excelEnums[j];
String fileName = excelEnum.getField();
Object value = PropertyUtils.getProperty(vo, fileName);
if (null != value) {
// 如果是Date类型
if (value.getClass().isAssignableFrom(Date.class)) {
String dateStyle = StringUtils.isEmpty(excelEnum.getDateFormatStyle()) ? "yyyy-MM-dd"
: excelEnum.getDateFormatStyle();
cell.setCellValue(new SimpleDateFormat(dateStyle).format((Date) value));
} else {
String valTrim = value.toString().trim();
if (!"".equals(valTrim)) {
// 如果返回的值可用枚举做转换
if (null != excelEnum.getClazz()) {
String cconvertedValue = convertValue(excelEnum, valTrim);
if (StringUtils.isNotBlank(cconvertedValue)) {
cell.setCellValue(cconvertedValue);
} else {
cell.setCellValue("");
}
} else {
cell.setCellValue(valTrim);
}
} else {
cell.setCellValue("");
}
}
} else {
cell.setCellValue("");
}
}
}
}
}
/**
* 根据编码获取枚举中对应的值
*
* @param excelEnum
* @param valTrim
* @return
* @throws Exception
*/
private static String convertValue(AbstractExcelEnum excelEnum, String valTrim) throws Exception {
Class<?> clazz = excelEnum.getClazz();
if (!clazz.isEnum()) {
throw new IllegalArgumentException("参数类型必须是枚举类型");
}
Method method = clazz.getMethod("values");
Object obj = method.invoke(null);
Object[] enumArr = (Object[]) obj;
for (Object object : enumArr) {
String va = PropertyUtils.getProperty(object, excelEnum.getCodeFieldName()).toString();
if (valTrim.equals(va)) {
return PropertyUtils.getProperty(object, excelEnum.getNameFieldName()).toString();
}
}
return null;
}
public static void testExportExcelByColumn(String imagesPath, String docsPath) {
try {
// 测试学生
List<Student> dataset = new ArrayList<Student>();
dataset.add(new Student(10000001, "张三", 20, true, new Date()));
dataset.add(new Student(20000002, "李四", 24, false, new Date()));
dataset.add(new Student(30000003, "王五", 22, true, new Date()));
ExportExcel2007<Student> ex = new ExportExcel2007<Student>();
XSSFWorkbook workbook = new XSSFWorkbook();
ex.createExcelAppendData(workbook, StudentExcelEnum.values(), dataset);
System.out.println("excel导出成功！");
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
}Servlet代码
public class Excel2007Servlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public static final String FILE_SEPARATOR = System.getProperties().getProperty("file.separator");
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String fileName = "export2007中文_" + System.currentTimeMillis() + ".xlsx";
fileName = new String(fileName.getBytes("GBK"), "ISO8859-1");
response.setContentType("application/x-msdownload");
response.setCharacterEncoding("UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
OutputStream out = response.getOutputStream();
try {
// 测试学生
List<Student> dataset = new ArrayList<Student>();
dataset.add(new Student(10000001, "张三", 20, true, new Date()));
dataset.add(new Student(20000002, "李四", 24, false, new Date()));
dataset.add(new Student(30000003, "王五", 22, true, new Date()));
ExportExcel2007<Student> ex = new ExportExcel2007<Student>();
XSSFWorkbook workbook = new XSSFWorkbook();
ex.createExcelAppendData(workbook, StudentExcelEnum.values(), dataset);
workbook.write(out);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (out != null) {
out.flush();
out.close();
}
}
}
}参考：https://blog.****.net/evangel_z/article/details/7312050