POI操作Excel文件
编程技术 / houtizong 发布于 3年前 80
1. ExcelBean.java
package com.wyy.snail.core.util;import java.util.ArrayList;import java.util.List;/** * Excel数据bean * @author penghy * @date 2014-02-24 */public class ExcelBean<T> {private String excelName; //Excel文件名称private String sheetName; //sheet名称private List<Column> columns; //excel列名称private List<T> rowDatas; //excel数据 public String getExcelName() {return excelName;}public void setExcelName(String excelName) {this.excelName = excelName;}public String getSheetName() {return sheetName;}public void setSheetName(String sheetName) {this.sheetName = sheetName;}public List<Column> getColumns() {if(columns == null){columns = new ArrayList<Column>();}return columns;}public void setColumns(List<Column> columns) {this.columns = columns;}public void addColumn(Column column){if(columns == null){columns = new ArrayList<Column>();}columns.add(column);}public void addColumn(String name, String explain){Column column = new Column(name, explain);this.addColumn(column);}public List<T> getRowDatas() {return rowDatas;}public void setRowDatas(List<T> rowDatas) {this.rowDatas = rowDatas;}}class Column{String name; //列名称String explain; //列对应的解释public Column(String name, String explain) {this.name = name;this.explain = explain;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getExplain() {return explain;}public void setExplain(String explain) {this.explain = explain;}}
2. ExcelUtils.java
package com.wyy.snail.core.util;import java.io.BufferedInputStream;import java.io.BufferedOutputStream;import java.io.ByteArrayInputStream;import java.io.ByteArrayOutputStream;import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.io.InputStream;import java.lang.reflect.Field;import java.net.URLEncoder;import java.text.DecimalFormat;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Calendar;import java.util.Date;import java.util.HashMap;import java.util.List;import java.util.Map;import javax.servlet.http.HttpServletResponse;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFDateUtil;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.springframework.util.ReflectionUtils;import org.springframework.util.StringUtils;import com.snail.base.utils.DateUtils;/** * Excel操作工具类, 需要依赖以下jar * <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.9</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-scratchpad</artifactId> <version>3.9</version> <type>jar</type> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>3.9</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.9</version> </dependency> * @author penghy * @date 2014-02-24 */public abstract class ExcelUtils {private static final int HEADER_DATA_INDEX = 0;private static final int ROW_DATA_INDEX = 1;private static final String DEFAULT_EXCEL_NAME = "workbook.xls";private static final String DEFAULT_SHEET_NAME = "sheet1";/** * 读取Excel内容，兼容excel2003,excele2007版本 * @param filePath * @return */public static List<Map<Integer,String>> readExcel(String filePath){FileInputStream in = null;try {in = new FileInputStream(new File(filePath));} catch (FileNotFoundException e) {e.printStackTrace();}Workbook wb = createWorkbook(in);return readExcel(wb);}/** * 读取Excel内容，兼容excel2003,excele2007版本 * @param in * @param isExcel2003 * @return */public static List<Map<Integer,String>> readExcel(InputStream in){Workbook wb = createWorkbook(in);return readExcel(wb);}/** * 读取Excel内容 * @param wb * @return */public static List<Map<Integer,String>> readExcel(Workbook wb){List<Map<Integer,String>> dataList = new ArrayList<Map<Integer,String>>(); Sheet sheet = wb.getSheetAt(0); int rows = sheet.getPhysicalNumberOfRows();for(int rowIndex=0; rowIndex< rows; rowIndex++){Row row = sheet.getRow(rowIndex);int cells = row.getPhysicalNumberOfCells();Map<Integer,String> cellMap = new HashMap<Integer,String>(cells);for(int cellIndex=0; cellIndex<cells; cellIndex++){Cell cell = row.getCell(cellIndex);cellMap.put(cellIndex, getStringCellValue(cell));}dataList.add(cellMap);}return dataList;}/** * 获取单元格数据内容为字符串类型的数据 * @param cell Excel单元格 * @return String 单元格数据内容 */ private static String getStringCellValue(Cell cell) { String strCell = ""; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: strCell = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { SimpleDateFormat dateformat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); Date dt = HSSFDateUtil.getJavaDate(cell.getNumericCellValue()); strCell = dateformat.format(dt); }else{ DecimalFormat df = new DecimalFormat("0"); strCell = df.format(cell.getNumericCellValue()); } break; case HSSFCell.CELL_TYPE_BOOLEAN: strCell = String.valueOf(cell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_BLANK: strCell = ""; break; default: strCell = ""; break; } return strCell; }/** * 创建Workbook * @param in * @param isExcel2003 * @return */public static Workbook createWorkbook(InputStream in){if(in == null){throw new IllegalArgumentException("File InputStream is null, please check!");}Workbook wb = null;try {wb = WorkbookFactory.create(in);} catch (InvalidFormatException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();} return wb;}/** * 创建excel * @param excelBean * @return InputStream */public static <T> InputStream createExcel(ExcelBean<T> excelBean){ //创建excel工作簿 Workbook wb = new HSSFWorkbook(); //创建第一个sheet Sheet sheet = wb.createSheet(StringUtils.hasText(excelBean.getSheetName()) ? excelBean.getSheetName() : DEFAULT_SHEET_NAME); //设置excel header标题 setHeaderData(sheet, excelBean.getColumns()); //设置excel内容数据 setRowData(sheet, excelBean); //转化excel文件到输入流 InputStream is = null; ByteArrayOutputStream os = new ByteArrayOutputStream(); try { wb.write(os); is = new ByteArrayInputStream(os.toByteArray()); } catch (IOException e) { e.printStackTrace(); } finally{ try {os.close();} catch (IOException e) {e.printStackTrace();} } return is;}/** * 创建并且下载excel * @param excelBean * @param response */public static <T> void createAndDowloadExcel(ExcelBean<T> excelBean, HttpServletResponse response){ BufferedInputStream bis = null; BufferedOutputStream bos = null; try { bis = new BufferedInputStream(createExcel(excelBean)); bos = new BufferedOutputStream(response.getOutputStream());// 取得输出流 response.reset();// 清空输出流 String filename = StringUtils.hasText(excelBean.getExcelName()) ? excelBean.getExcelName()+".xls" : DEFAULT_EXCEL_NAME; response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(filename, "UTF-8"));// 设定输出文件头 response.setContentType("application/x-download"); byte[] buffer = new byte[8192]; int len = 0; while ((len = bis.read(buffer, 0, buffer.length)) != -1) { bos.write(buffer, 0, len); } }catch (IOException e) { e.printStackTrace(); }finally{ try {bis.close();} catch (IOException e) {e.printStackTrace();} try {bos.close();} catch (IOException e) {e.printStackTrace();} }}/** * 设置excel header * @param sheet * @param columns */private static void setHeaderData(Sheet sheet, List<Column> columns){ //设置excel的header Row row = sheet.createRow(HEADER_DATA_INDEX); for(int i=0; i< columns.size(); i++){ Column column = columns.get(i); row.createCell(i).setCellValue(column.getExplain()); }}/** * 设置excel内容 * @param sheet * @param excelBean */private static <T> void setRowData(Sheet sheet, ExcelBean<T> excelBean){int rowIndex = ROW_DATA_INDEX;List<T> rowDatas = excelBean.getRowDatas();for(T obj : rowDatas){ Row row = sheet.createRow(rowIndex++); //输出excel内容 List<Column> columns = excelBean.getColumns(); for(int i=0; i< columns.size(); i++) { Column column = columns.get(i); Object value = getFieldValue(column.getName(),obj); Cell cell = row.createCell(i); setCellValue(cell, value); }}}/** * 获取属性名称对应的值 * @param fieldName * @param obj * @return */private static <T> Object getFieldValue(String fieldName, T obj){Field field = ReflectionUtils.findField(obj.getClass(), fieldName);if(field == null){return null;}field.setAccessible(true);return ReflectionUtils.getField(field, obj);}/** * 设置cell的内容 * @param cell * @param value */private static void setCellValue(Cell cell, Object value){if(value == null){cell.setCellValue("");return;}Class<?> cls = value.getClass();if(cls.equals(Byte.class) || cls.equals(Short.class) || cls.equals(Integer.class) || cls.equals(Long.class) || cls.equals(Float.class) || cls.equals(Double.class)){cell.setCellValue(Double.parseDouble(value.toString()));}else if(cls.equals(Boolean.class)){cell.setCellValue(Boolean.parseBoolean(value.toString()));}else if(cls.equals(Date.class)){cell.setCellValue(DateUtils.getDatetimeToString((Date)value));}else if(cls.equals(Calendar.class)){cell.setCellValue(DateUtils.getDatetimeToString(((Calendar)value).getTime()));}else{cell.setCellValue(value.toString());}} public static void main(String[] args){ //从excel读取数据 List<Map<Integer,String>> list = readExcel("d:/test.xlsx"); for(Map<Integer,String> m : list){ System.out.println(m.get(0)+" "+m.get(1)+","+m.get(2)); } }}
上一篇：Java生成随机验证码
请勿发布不友善或者负能量的内容。与人为善，比聪明更重要！
技术博客集 - 网站简介：
前后端技术：
后端基于Hyperf2.1框架开发,前端使用Bootstrap可视化布局系统生成
网站主要作用：
1.编程技术分享及讨论交流，内置聊天系统;
2.测试交流框架问题，比如：Hyperf、Laravel、TP、beego;
3.本站数据是基于大数据采集等爬虫技术为基础助力分享知识，如有侵权请发邮件到站长邮箱，站长会尽快处理;
4.站长邮箱：[email protected];
文章归档
文章标签
友情链接
