用poi来导出数据到excel文档

  1 package cn.com.dyg.work.common.utils;
  2 
  3 import org.apache.poi.hssf.usermodel.HSSFRichTextString;
  4 import org.apache.poi.hssf.usermodel.HSSFWorkbookFactory;
  5 import org.apache.poi.ss.usermodel.*;
  6 import org.apache.poi.ss.util.CellRangeAddress;
  7 import org.apache.poi.xssf.usermodel.XSSFRichTextString;
  8 import org.apache.poi.xssf.usermodel.XSSFWorkbookFactory;
  9 
 10 import java.util.List;
 11 
 12 /**
 13  * 导出Excel公共方法
 14  *
 15  * @author yxb
 16  * @version 1.0
 17  */
 18 public class ExportExcelTool {
 19 
 20     //显示的导出表的标题
 21     private String title;
 22     //导出表的列名
 23     private String[] rowName;
 24 
 25     private List<Object[]> dataList;
 26     private boolean xssf;
 27 
 28     private String[] fieldNames;
 29 
 30     //构造方法，传入要导出的数据
 31     public ExportExcelTool(String title, String[] rowName, String[] fieldNames, List<Object[]> dataList, boolean xssf) {
 32         this.dataList = dataList;
 33         this.rowName = rowName;
 34         this.title = title;
 35         this.xssf = xssf;
 36         this.fieldNames = fieldNames;
 37     }
 38 
 39     /**
 40      * Create a new empty Workbook, either XSSF or HSSF depending
 41      * on the parameter
 42      *
 43      * @return The created workbook
 44      */
 45     public Workbook create() {
 46         if (xssf) {
 47             return XSSFWorkbookFactory.createWorkbook();
 48         } else {
 49             return HSSFWorkbookFactory.createWorkbook();
 50         }
 51     }
 52 
 53 
 54     /*
 55      * 导出数据
 56      * */
 57     public Workbook export() {
 58         try {
 59             Workbook workbook = create();                        // 创建工作簿对象
 60             Sheet sheet = workbook.createSheet(title);                     // 创建工作表
 61 
 62             // 产生表格标题行
 63             Row rowm = sheet.createRow(0);
 64             Cell cellTiltle = rowm.createCell(0);
 65 
 66             rowm.setHeight((short) (25 * 35)); //设置高度
 67 
 68             //sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面  - 可扩展】
 69 
 70             sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (rowName.length - 1)));
 71             cellTiltle.setCellValue(title);
 72 
 73 
 74             // 定义所需列数
 75             int fieldRow = fieldNames.length;
 76             Row fieldName = sheet.createRow(1);                // 在索引2的位置创建行(最顶端的行开始的第二行)
 77 
 78             fieldName.setHeight((short) (25 * 25)); //设置高度
 79 
 80             // 将列头设置到sheet的单元格中
 81             for (int n = 0; n < fieldRow; n++) {
 82                 Cell cellRowName = fieldName.createCell(n);                //创建列头对应个数的单元格
 83                 RichTextString text;
 84                 if (xssf)
 85                     text = new XSSFRichTextString(fieldNames[n]);
 86                 else
 87                     text = new HSSFRichTextString(fieldNames[n]);
 88                 cellRowName.setCellValue(text);                                    //设置列头单元格的值
 89             }
 90 
 91             // 定义所需列数
 92             int columnNum = rowName.length;
 93             Row rowRowName = sheet.createRow(2);                // 在索引2的位置创建行(最顶端的行开始的第二行)
 94 
 95             rowRowName.setHeight((short) (25 * 25)); //设置高度
 96 
 97             // 将列头设置到sheet的单元格中
 98             for (int n = 0; n < columnNum; n++) {
 99                 Cell cellRowName = rowRowName.createCell(n);                //创建列头对应个数的单元格
100                 RichTextString text;
101                 if (xssf)
102                     text = new XSSFRichTextString(rowName[n]);
103                 else
104                     text = new HSSFRichTextString(rowName[n]);
105                 cellRowName.setCellValue(text);                                    //设置列头单元格的值
106             }
107 
108 
109             //将查询出的数据设置到sheet对应的单元格中
110             for (int i = 0; i < dataList.size(); i++) {
111 
112                 Object[] obj = dataList.get(i);//遍历每个对象
113                 Row row = sheet.createRow(i + 3);//创建所需的行数
114 
115                 row.setHeight((short) (25 * 20)); //设置高度
116 
117                 for (int j = 0; j < obj.length; j++) {
118                     Cell cell;   //设置单元格的数据类型
119                     cell = row.createCell(j, CellType.STRING);
120                     if (!"".equals(obj[j]) && obj[j] != null) {
121                         cell.setCellValue(obj[j].toString());                        //设置单元格的值
122                     }
123                 }
124             }
125             //让列宽随着导出的列长自动适应
126             for (int colNum = 0; colNum < columnNum; colNum++) {
127                 int columnWidth = sheet.getColumnWidth(colNum) / 256;
128                 for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
129                     Row currentRow;
130                     //当前行未被使用过
131                     if (sheet.getRow(rowNum) == null) {
132                         currentRow = sheet.createRow(rowNum);
133                     } else {
134                         currentRow = sheet.getRow(rowNum);
135                     }
136                     if (currentRow.getCell(colNum) != null) {
137                         Cell currentCell = currentRow.getCell(colNum);
138                         if (currentCell.getCellType() == CellType.STRING) {
139                             int length = currentCell.getStringCellValue().getBytes().length;
140                             if (columnWidth < length) {
141                                 columnWidth = length;
142                             }
143                         }
144                     }
145                 }
146                 if (colNum == 0) {
147                     sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
148                 } else {
149                     sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
150                 }
151 
152 
153             }
154 //
155 //            if (workbook != null) {
156 //                try {
157 //                    String fileName = "C:\\Users\\yangxiaobo\\Desktop/" + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()).toString();
158 //                    if (xssf)
159 //                        fileName += ".xlsx";
160 //                    else
161 //                        fileName += ".xls";
162 //                    FileOutputStream out = new FileOutputStream(fileName);
163 //                    workbook.write(out);
164 //                    out.close();
165 //                } catch (IOException e) {
166 //                    e.printStackTrace();
167 //                }
168 //            }
169 
170 
171 //            String fileName = "" + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()).toString();
172 //            if (xssf)
173 //                fileName += ".xlsx";
174 //            else
175 //                fileName += ".xls";
176             return workbook;
177         } catch (Exception e) {
178             e.printStackTrace();
179         }
180 
181         return null;
182     }
183 }