Workbook

最新推荐文章于 2020-11-24 16:12:24 发布

转载最新推荐文章于 2020-11-24 16:12:24 发布 · 107 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://my.oschina.net/u/2274056/blog/677956

2019独角兽企业重金招聘Python工程师标准>>>

package tf56.goodstaxiAdmin.util;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.json.JSONArray;
import org.json.JSONObject;

/**
* excel 生成工具
* @author lenovo
*
*/
public class ExcelUtils {

   /**
   *
   * @param data 数据
   * @param header 标题
   * @param itemnames 标题对应的数据字段名
   * @return
   */
   public static Workbook createWorkbook(JSONArray data,String[] header,String []itemnames,String sheetname){
       Workbook workbook = null;
       try {
           // XSSFWork 2007 HSSWorkbook 2003
           workbook = new HSSFWorkbook();
       }catch(Exception e) {
           System.out.println("It cause Error on CREATING excel workbook: ");
           e.printStackTrace();
       }
       if(workbook != null) {
           Sheet sheet = workbook.createSheet(sheetname);
           CellStyle style = getStyle(workbook);
           Row row0 = sheet.createRow(0);
           for(int i = 0; i < header.length; i++) {
               Cell cell_1 = row0.createCell(i, Cell.CELL_TYPE_STRING);
               cell_1.setCellStyle(style);
               cell_1.setCellValue(header[i]);
               sheet.autoSizeColumn(i);
           }
           if(data == null || data.length() == 0) {
               Row row = sheet.createRow(1);
               Cell cell = row.createCell(0, Cell.CELL_TYPE_STRING);
               cell.setCellValue("没有数据");
           } else {
               for (int rowNum = 1; rowNum <= data.length(); rowNum++) {
                   Row row = sheet.createRow(rowNum);
                   JSONObject item = data.getJSONObject(rowNum-1);
                   for(int i=0;i<itemnames.length;i++) {
                       Cell cell = row.createCell(i, Cell.CELL_TYPE_STRING);
                       String value = "";
                       if(!item.isNull(itemnames[i])) {
value = item.get(itemnames[i]) + "";
                       }
                       cell.setCellValue(value);
                   }

               }
           }

       }
       return workbook;
   }

   private static CellStyle getStyle(Workbook workbook){
   CellStyle style = workbook.createCellStyle();
   style.setAlignment(CellStyle.ALIGN_CENTER);
   style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
   // 设置单元格字体
   Font headerFont = workbook.createFont(); // 字体
   headerFont.setFontHeightInPoints((short)14);
   headerFont.setColor(HSSFColor.RED.index);
   headerFont.setFontName("宋体");
   style.setFont(headerFont);
   style.setWrapText(true);

   // 设置单元格边框及颜色
   style.setBorderBottom((short)1);
   style.setBorderLeft((short)1);
   style.setBorderRight((short)1);
   style.setBorderTop((short)1);
   style.setWrapText(true);
   return style;
   }
}

Workbook workbook = ExcelUtils.createWorkbook(data, header, itemnames, "交易列表信息");

FileOutputStream fileOut = null;
try {
/*OutputStream os = response.getOutputStream();
workbook.write(os);
response.flushBuffer();*/
String relativelyPath=this.getResponseHelper().getRequest().getSession().getServletContext().getRealPath("");
File file = new File(relativelyPath+"/temp");
if(!file.exists()){
file.mkdir();
}
file = new File(relativelyPath+"/temp/trade.xls");
if(file.exists()){
file.delete();
}
file.createNewFile();

fileOut = new FileOutputStream(file);
workbook.write(fileOut);
this.zip(relativelyPath+"/temp.zip", new File(relativelyPath+"/temp"));