java 表格导出至Excel

基本操作步骤：

1、用HSSFWorkbook打开或者创建“Excel文件对象”

2、用HSSFWorkbook对象返回或者创建Sheet对象

3、用Sheet对象返回行对象，用行对象得到Cell对象

4、对Cell对象读写。

例子：

//创建HSSFWorkbook对象

HSSFWorkbook wb = new HSSFWorkbook();

//创建HSSFSheet对象

HSSFSheet sheet = wb.createSheet("sheet0");

//创建HSSFRow对象

HSSFRow row = sheet.createRow(0);

//创建HSSFCell对象

HSSFCell cell=row.createCell(0);

//设置单元格的值

cell.setCellValue("单元格中的中文");

//输出Excel文件

FileOutputStream output=new FileOutputStream("d:\\workbook.xls");

wkb.write(output);

output.flush();

package com.sesxh.hsmmp.common.util;import java.io.File;import java.io.FileInputStream;import java.lang.reflect.Field;import java.lang.reflect.Method;import java.util.Date;import java.util.List;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.util.HSSFColor;import org.apache.poi.ss.usermodel.HorizontalAlignment;import org.apache.poi.ss.usermodel.VerticalAlignment;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.xssf.usermodel.XSSFCell;import org.apache.poi.xssf.usermodel.XSSFCellStyle;import org.apache.poi.xssf.usermodel.XSSFDataFormat;import org.apache.poi.xssf.usermodel.XSSFFont;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import com.sesxh.common.exception.BusinessException;/** * @Description: Excel操作类  */public class ToExcelUtil {/** * @Description: 使用反射将List存储到Excel中，仅支持.xls（即excel2003之前的版本） * @param list * @param strings * @return * @throws Exception * @return HSSFWorkbook * @author sunmd * @date 2017年1月4日 上午11:20:32 */public static HSSFWorkbook listToExcel(List<?> list, String... strings)throws Exception {if (list.size() <= 0) {throw new BusinessException("数据不能为空");}HSSFWorkbook excel = new HSSFWorkbook();HSSFSheet sheet = excel.createSheet();/* * 设置第一列需要展示的东西 */int cellLoc = 0; // 因为serialVersionUID的存在会打乱row.createCell的顺序，所以设置这个变量HSSFRow row = sheet.createRow(0);if (strings != null && strings.length > 0) {for (int i = 0; i < strings.length; ++i) {HSSFCell cell = row.createCell(i);cell.setCellValue(strings[i]);}} else {Object object = list.get(0);Class<?> class1 = object.getClass();Field[] fields = class1.getDeclaredFields();for (int i = 0; i < fields.length; ++i) {Field field = fields[i];String fname = field.getName();if (fname.equals("serialVersionUID")) {continue;}HSSFCell cell = row.createCell(cellLoc++);cell.setCellValue(fname);}}/* * 组装Excel表格 */for (int i = 0; i < list.size(); ++i) {Object object = list.get(i);Class<?> class1 = object.getClass();Field[] fields = class1.getDeclaredFields();HSSFRow row2 = sheet.createRow(i + 1);cellLoc = 0;for (int k = 0; k < fields.length; ++k) {String fname = fields[k].getName();if (fname.equals("serialVersionUID")) {continue;}Method method = class1.getMethod("get" + change(fname));Object result = method.invoke(object);HSSFCell cell = row2.createCell(cellLoc++);if (result instanceof Integer) {cell.setCellValue((Integer) result);} else if (result instanceof Date) {cell.setCellValue((Date) result);} else {cell.setCellValue((String) result);}}}return excel;}/** * @Description: 支持.xlsx（即excel2007后的文件） * @param list * @param strings * @return * @throws Exception * @return XSSFWorkbook *  *  */public static XSSFWorkbook listToExcelBetter(List<?> list,String... strings) throws Exception {if (list.size() <= 0) {throw new BusinessException("数据不能为空");}XSSFWorkbook excel = new XSSFWorkbook();XSSFSheet sheet = excel.createSheet();//设置默认的行高和列宽//sheet.setDefaultColumnWidth(40);//sheet.setDefaultRowHeightInPoints(20);/* * 设置第一列需要展示的东西 */int cellLoc = 0; // 因为serialVersionUID的存在会打乱row.createCell的顺序，所以设置这个变量XSSFRow row = sheet.createRow(0);if (strings != null && strings.length > 0) {for (int i = 0; i < strings.length; ++i) {sheet.setColumnWidth(i, 20 * 256);XSSFCell cell = row.createCell(i);//设置样式XSSFCellStyle xSSFCellStyle= excel.createCellStyle();xSSFCellStyle.setAlignment(HorizontalAlignment.LEFT);//水平居左xSSFCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中// 生成一个字体XSSFFont font = excel.createFont();font.setFontHeightInPoints((short) 15);font.setColor(HSSFColor.BLACK.index);font.setBold(true);font.setFontName("宋体");// 把字体 应用到当前样式xSSFCellStyle.setFont(font);cell.setCellStyle(xSSFCellStyle);cell.setCellValue(strings[i]);}} else {Object object = list.get(0);Class<?> class1 = object.getClass();Field[] fields = class1.getDeclaredFields();for (int i = 0; i < fields.length; ++i) {Field field = fields[i];String fname = field.getName();if (fname.equals("serialVersionUID")) {continue;}XSSFCell cell = row.createCell(cellLoc++);//设置样式XSSFCellStyle xSSFCellStyle= excel.createCellStyle();xSSFCellStyle.setAlignment(HorizontalAlignment.LEFT);//水平居左xSSFCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中// 生成一个字体XSSFFont font = excel.createFont();font.setFontHeightInPoints((short) 15);font.setColor(HSSFColor.BLACK.index);font.setBold(true);font.setFontName("宋体");// 把字体 应用到当前样式xSSFCellStyle.setFont(font);cell.setCellValue(fname);}}/* * 组装Excel表格 */for (int i = 0; i < list.size(); ++i) {Object object = list.get(i);Class<?> class1 = object.getClass();Field[] fields = class1.getDeclaredFields();XSSFRow row2 = sheet.createRow(i + 1);cellLoc = 0;for (int k = 0; k < fields.length; ++k) {String fname = fields[k].getName();if (fname.equals("serialVersionUID")) {continue;}Method method = class1.getMethod("get" + change(fname));Object result = method.invoke(object);XSSFCell cell = row2.createCell(cellLoc++);XSSFCellStyle cellstyle = excel.createCellStyle();//时间操作类XSSFDataFormat format= excel.createDataFormat();//设置样式cellstyle.setAlignment(HorizontalAlignment.LEFT);//水平居左cellstyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中if (result instanceof Integer) {cell.setCellStyle(cellstyle);cell.setCellValue((Integer) result);} else if (result instanceof Date) {cellstyle.setDataFormat(format.getFormat("yyyy-MM-dd HH:mm:ss"));;cell.setCellStyle(cellstyle);cell.setCellValue((Date) result);} else {cell.setCellStyle(cellstyle);cell.setCellValue((String) result);}}}return excel;}/** * @Description: 根据模版生成Excel文件（.xls） * @param list * @param file * @param startRow * @return * @throws Exception * @return HSSFWorkbook *  *  */@SuppressWarnings("resource")public static XSSFWorkbook toExcelByTemplate(List<?> list, File templateFile,int startRow, String time, String author) throws Exception {FileInputStream fileInputStream = new FileInputStream(templateFile);XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fileInputStream);XSSFCell cell = null;xssfWorkbook = new ToExcelUtil().setupStyle(xssfWorkbook, time, author);XSSFSheet sheet = xssfWorkbook.getSheetAt(0);int cellLoc;for (int i = 0; i < list.size(); ++i) {Object object = list.get(i);Class<?> class1 = object.getClass();Field[] fields = class1.getDeclaredFields();XSSFRow row2 = sheet.createRow(startRow++);cellLoc = 0;for (int k = 0; k < fields.length; ++k) {String fname = fields[k].getName();if (fname.equals("serialVersionUID")) {continue;}Method method = class1.getMethod("get" + change(fname));Object result = method.invoke(object);cell = row2.createCell(cellLoc++);if (result instanceof Integer) {cell.setCellValue((Integer) result);} else if (result instanceof Date) {cell.setCellValue((Date) result);} else {cell.setCellValue((String) result);}}}return xssfWorkbook;}/** * @Description: 将字符串的第一个字母转变成大写 * @param string * @return * @return String *  *  */public static String change(String string) {if (string == null || string.length() <= 0) {return null;} else {char temp = Character.toUpperCase(string.charAt(0));String string2 = temp + string.substring(1);return string2;}}/** * @Description: 设置报表格式 * @param xssfWorkbook * @param time * @param author * @return * @return XSSFWorkbook    *  *  */public  XSSFWorkbook setupStyle(XSSFWorkbook xssfWorkbook, String time, String author) {XSSFSheet sheet = xssfWorkbook.getSheetAt(0);XSSFRow row = sheet.createRow(3);XSSFFont font = xssfWorkbook.createFont();font.setBold(true);XSSFCell cell = row.createCell(0);cell.setCellValue(time);XSSFCellStyle cellStyle = xssfWorkbook.createCellStyle();cellStyle.setFont(font);cellStyle.setAlignment(HorizontalAlignment.LEFT);cell.setCellStyle(cellStyle);cell = row.createCell(3);cell.setCellValue(author);XSSFCellStyle cellStyle2 = xssfWorkbook.createCellStyle();cellStyle2.setFont(font);cellStyle2.setAlignment(HorizontalAlignment.RIGHT);cell.setCellStyle(cellStyle2);CellRangeAddress cellRangeAddress = new CellRangeAddress(3, 3, 0, 2);sheet.addMergedRegion(cellRangeAddress);cellRangeAddress = new CellRangeAddress(3, 3, 3, 6);sheet.addMergedRegion(cellRangeAddress);return xssfWorkbook;}  /** * @Description:生成带样式的Excel  * @param title:标题 * @param titles:第一行中文列 * @param list:数据集合 * @param sheettitle:左下角sheet页的名字 * @param rowheight:默认行高 * @param columnwidth:默认列宽 * @return */@SuppressWarnings("deprecation")public static HSSFWorkbook getstyleExcel(String title,String[] titles,List<?> list,String sheettitle,int rowheight,int columnwidth,String[] zj,String[] jlr)throws Exception{//创建HSSFWorkbook对象，即生成一个Eecel文档HSSFWorkbook excel = new HSSFWorkbook();//创建sheet页,可以创建多个sheet页HSSFSheet sheet = excel.createSheet(sheettitle);//设置默认的行高和列宽sheet.setDefaultRowHeightInPoints(rowheight);sheet.setDefaultColumnWidth(columnwidth);//数据行开始的行数int index=0;//生成标题if(!"".equals(title)){//生成标题    getTitleRow(excel,sheet,title,titles.length);    //生成第一行getRowOne(excel,sheet,titles,list,1);index=2;}else{//生成第一行getRowOne(excel,sheet,titles,list,0);index=1;}//创建数据行for (int i = 0; i < list.size(); ++i) {Object object = list.get(i);Class<?> class1 = object.getClass();Field[] fields = class1.getDeclaredFields();HSSFRow row2 = sheet.createRow(i + index);//设置数据展示行的行高row2.setHeightInPoints(20);int cellLoc = 0;for (int k = 0; k < fields.length; ++k) {String fname = fields[k].getName();if (fname.equals("serialVersionUID")) {continue;}Method method = class1.getMethod("get" + change(fname));Object result = method.invoke(object);HSSFCell cell = row2.createCell(cellLoc++);HSSFCellStyle cellstyle = excel.createCellStyle();//设置样式cellstyle.setAlignment(HorizontalAlignment.LEFT);//水平居左cellstyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中//设置边框cellstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框    cellstyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框    cellstyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框    cellstyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框  cell.setCellStyle(cellstyle);if (result instanceof Integer) {cell.setCellValue((Integer) result);} else if (result instanceof Date) {cell.setCellValue((Date) result);} else {cell.setCellValue((String) result);}}}//创造总计行和净利润行if(zj.length>0){HSSFRow rowsum = sheet.createRow(list.size()+2);//设置数据展示行的行高rowsum.setHeightInPoints(20);for(int s=0;s<zj.length;s++){HSSFCell cell = rowsum.createCell(s);HSSFCellStyle cellstyle = excel.createCellStyle();//设置样式cellstyle.setAlignment(HorizontalAlignment.LEFT);//水平居左cellstyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中//cellstyle.setFillBackgroundColor(HSSFColor.YELLOW.index);//cellstyle.setFillForegroundColor(HSSFColor.YELLOW.index);//cellstyle.setFillPattern(HSSFCellStyle.SPARSE_DOTS);//设置边框cellstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框    cellstyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框    cellstyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框    cellstyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框  cell.setCellStyle(cellstyle);cell.setCellValue(zj[s]);}}if(jlr.length>0){HSSFRow rowsumjlr = sheet.createRow(list.size()+3);//设置数据展示行的行高rowsumjlr.setHeightInPoints(20);for(int j=0;j<jlr.length;j++){HSSFCell cell = rowsumjlr.createCell(j);HSSFCellStyle cellstyle = excel.createCellStyle();//设置样式cellstyle.setAlignment(HorizontalAlignment.LEFT);//水平居左cellstyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中//cellstyle.setFillBackgroundColor(HSSFColor.YELLOW.index);//cellstyle.setFillForegroundColor(HSSFColor.YELLOW.index);//cellstyle.setFillPattern(HSSFCellStyle.SPARSE_DOTS);//设置边框cellstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框    cellstyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框    cellstyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框    cellstyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框  cell.setCellStyle(cellstyle);cell.setCellValue(jlr[j]);}}return excel;}/** * @Description:标题行  * @param excel * @param sheet * @param title * @param sum：合并的列数 * @return */@SuppressWarnings("deprecation")public static void getTitleRow(HSSFWorkbook excel,HSSFSheet sheet ,String title,int sum){//标题行HSSFRow row = sheet.createRow(0);row.setHeightInPoints(40);//单元格的样式HSSFCellStyle _0CellStyle = excel.createCellStyle();_0CellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中_0CellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中//设置背景色_0CellStyle.setFillBackgroundColor(HSSFColor.LIGHT_BLUE.index);_0CellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);_0CellStyle.setFillPattern(HSSFCellStyle.SPARSE_DOTS);//设置边框_0CellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框    _0CellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框    _0CellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框    _0CellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框  // 生成一个字体HSSFFont font = excel.createFont();font.setFontHeightInPoints((short) 20);font.setColor(HSSFColor.BLACK.index);font.setBold(true);font.setFontName("宋体");// 把字体 应用到当前样式_0CellStyle.setFont(font);//创建标题单元格,合并单元格int startRowNo = 0;int endRowNo = 0;int startCellNo = 0;int endCellNo = sum-1;sheet.addMergedRegion(new CellRangeAddress(startRowNo, endRowNo, startCellNo, endCellNo));HSSFCell cell = row.createCell(0);cell.setCellValue(title);cell.setCellStyle(_0CellStyle);}/** * @Description:第一行 标题行 * @param sheet * @param titles * @param list * @param sum * @return */@SuppressWarnings("deprecation")public static void getRowOne(HSSFWorkbook excel,HSSFSheet sheet ,String[] titles,List<?> list,int sum){//第一行int cellLoc = 0; // 因为serialVersionUID的存在会打乱row.createCell的顺序，所以设置这个变量HSSFRow row = sheet.createRow(sum);//设置样式HSSFCellStyle cellStyle = excel.createCellStyle();cellStyle.setAlignment(HorizontalAlignment.LEFT);//水平居左cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中//设置背景色cellStyle.setFillBackgroundColor(HSSFColor.ORANGE.index);cellStyle.setFillForegroundColor(HSSFColor.ORANGE.index);cellStyle.setFillPattern(HSSFCellStyle.SPARSE_DOTS);//设置边框cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框    cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框    cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框    cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框  // 生成一个字体HSSFFont font = excel.createFont();font.setFontHeightInPoints((short) 15);font.setColor(HSSFColor.BLACK.index);font.setBold(true);font.setFontName("宋体");// 把字体 应用到当前样式cellStyle.setFont(font);if (titles != null && titles.length > 0) {for (int i = 0; i < titles.length; ++i) {HSSFCell cell = row.createCell(i);//设置样式cell.setCellStyle(cellStyle);cell.setCellValue(titles[i]);}} else {Object object = list.get(0);Class<?> class1 = object.getClass();Field[] fields = class1.getDeclaredFields();for (int i = 0; i < fields.length; ++i) {Field field = fields[i];String fname = field.getName();if (fname.equals("serialVersionUID")) {continue;}HSSFCell cell = row.createCell(cellLoc++);//设置样式cell.setCellStyle(cellStyle);cell.setCellValue(fname);}}}}