package hyss.util.common;import java.io.IOException;import java.io.OutputStream;import java.net.URLEncoder;import java.util.ArrayList;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.HSSFRichTextString;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.util.CellRangeAddress;/** * Excel導出工具類 * @author ts */public class ExportExcelUtil { //顯示的導出表的標題 private String title; //導出表的列名 private String[] rowName ;  private List<Object[]> dataList = new ArrayList<Object[]>(); // HttpServletResponse response;  //構造方法，傳入要導出的數據 public ExportExcelUtil(String title,String[] rowName,List<Object[]> dataList){  this.dataList = dataList;  this.rowName = rowName;  this.title = title; }    /*  * 導出數據  * */ public void export() throws Exception{  try{   HSSFWorkbook workbook = new HSSFWorkbook();      // 創建工作簿對象   HSSFSheet sheet = workbook.createSheet(title);      // 創建工作表      // 產生表格標題行   HSSFRow rowm = sheet.createRow(0);   HSSFCell cellTiltle = rowm.createCell(0);   rowm.setHeightInPoints(25);           //設置標題行默認行高      //sheet樣式定義【getColumnTopStyle()/getStyle()均為自定義方法 - 在下面 - 可擴展】   HSSFCellStyle columnTitleStyle = this.getTitleTopStyle(workbook);//獲取標題行樣式   HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook); //獲取列頭樣式對象   HSSFCellStyle style = this.getStyle(workbook);     //單元格樣式對象      sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length-1)));    cellTiltle.setCellStyle(columnTitleStyle);   cellTiltle.setCellValue(title);      // 定義所需列數   int columnNum = rowName.length;   HSSFRow rowRowName = sheet.createRow(2);    // 在索引2的位置創建行(最頂端的行開始的第二行)   rowRowName.setHeightInPoints(25);       //將列頭設置默認行高   // 將列頭設置到sheet的單元格中   for(int n=0;n<columnNum;n++){    HSSFCell cellRowName = rowRowName.createCell(n);    //創建列頭對應個數的單元格    cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);    //設置列頭單元格的數據類型    HSSFRichTextString text = new HSSFRichTextString(rowName[n]);    cellRowName.setCellValue(text);         //設置列頭單元格的值    cellRowName.setCellStyle(columnTopStyle);      //設置列頭單元格樣式   }      //將查詢出的數據設置到sheet對應的單元格中   for(int i=0;i<dataList.size();i++){        Object[] obj = dataList.get(i);//遍歷每個對象    HSSFRow row = sheet.createRow(i+3);//創建所需的行數    row.setHeightInPoints(20); //將創建出的行設置默認行高    for(int j=0; j<obj.length; j++){     HSSFCell cell = null; //設置單元格的數據類型     if(j == 0){      cell = row.createCell(j,HSSFCell.CELL_TYPE_NUMERIC);      cell.setCellValue(i+1);      }else{      cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);      if(!"".equals(obj[j]) && obj[j] != null){       cell.setCellValue(obj[j].toString());      //設置單元格的值      }     }     cell.setCellStyle(style);         //設置單元格樣式    }   }   //讓列寬隨著導出的列長自動適應   for (int colNum = 0; colNum < columnNum; colNum++) {    int columnWidth = sheet.getColumnWidth(colNum) / 256;    for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {     HSSFRow currentRow;     //當前行未被使用過     if (sheet.getRow(rowNum) == null) {      currentRow = sheet.createRow(rowNum);     } else {      currentRow = sheet.getRow(rowNum);     }     if (currentRow.getCell(colNum) != null) {      HSSFCell currentCell = currentRow.getCell(colNum);      if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {       int length = currentCell.getStringCellValue().getBytes().length;       if (columnWidth < length) {        columnWidth = length;       }      }     }    }    if(colNum == 0){     sheet.setColumnWidth(colNum, (columnWidth-2) * 256);         }else{     sheet.setColumnWidth(colNum, (columnWidth+4) * 256);    }   }      if(workbook !=null){    try    {     String fileName = title + DateTime.getSystemDateTime("yyyy-MM-dd") + ".xls";　　　　　　　　　　　　　//因 response已經封裝成工具類所以下面這段代碼注釋掉//     response =ServletActionContext.getResponse();//     response.setContentType("APPLICATION/OCTET-STREAM");//     response.setHeader("Content-Disposition", headStr);//     OutputStream out = response.getOutputStream();          //解決中文亂碼      Struts2Util.getResponse().setCharacterEncoding("UTF-8");     //解決中文亂碼     Struts2Util.getResponse().setHeader("Content-Disposition", "attachment;filename*=UTF-8''" + URLEncoder.encode(fileName,"UTF-8"));     //文件下載     Struts2Util.getResponse().setContentType("APPLICATION/OCTET-STREAM");     OutputStream out = Struts2Util.getResponse().getOutputStream();     workbook.write(out);    }    catch (IOException e)    {     e.printStackTrace();    }   }  }catch(Exception e){   e.printStackTrace();  }   }  /*   * 設置標題樣式  */   public HSSFCellStyle getTitleTopStyle(HSSFWorkbook workbook) {      // 設置字體   HSSFFont font = workbook.createFont();   //設置字體大小   font.setFontHeightInPoints((short)24);   //字體加粗   font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);   //設置字體名字    font.setFontName("宋體");   //設置樣式;    HSSFCellStyle style = workbook.createCellStyle();   //設置底邊框;    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);   //設置底邊框顏色;    style.setBottomBorderColor(HSSFColor.BLACK.index);   //設置左邊框;    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);   //設置左邊框顏色;    style.setLeftBorderColor(HSSFColor.BLACK.index);   //設置右邊框;    style.setBorderRight(HSSFCellStyle.BORDER_THIN);   //設置右邊框顏色;    style.setRightBorderColor(HSSFColor.BLACK.index);   //設置頂邊框;    style.setBorderTop(HSSFCellStyle.BORDER_THIN);   //設置頂邊框顏色;    style.setTopBorderColor(HSSFColor.BLACK.index);   //在樣式用應用設置的字體;    style.setFont(font);   //設置自動換行;    style.setWrapText(false);   //設置水平對齊的樣式為居中對齊;    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);   //設置垂直對齊的樣式為居中對齊;    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);      return style;     }   /*   * 列頭單元格樣式  */   public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {      // 設置字體   HSSFFont font = workbook.createFont();   //設置字體大小   font.setFontHeightInPoints((short)11);   //字體加粗   font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);   //設置字體名字    font.setFontName("宋體");   //設置樣式;    HSSFCellStyle style = workbook.createCellStyle();   //設置底邊框;    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);   //設置底邊框顏色;    style.setBottomBorderColor(HSSFColor.BLACK.index);   //設置左邊框;    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);   //設置左邊框顏色;    style.setLeftBorderColor(HSSFColor.BLACK.index);   //設置右邊框;    style.setBorderRight(HSSFCellStyle.BORDER_THIN);   //設置右邊框顏色;    style.setRightBorderColor(HSSFColor.BLACK.index);   //設置頂邊框;    style.setBorderTop(HSSFCellStyle.BORDER_THIN);   //設置頂邊框顏色;    style.setTopBorderColor(HSSFColor.BLACK.index);   //在樣式用應用設置的字體;    style.setFont(font);   //設置自動換行;    style.setWrapText(false);   //設置水平對齊的樣式為居中對齊;    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);   //設置垂直對齊的樣式為居中對齊;    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);      return style;     }    /*   * 列數據信息單元格樣式  */   public HSSFCellStyle getStyle(HSSFWorkbook workbook) {   // 設置字體   HSSFFont font = workbook.createFont();   //設置字體大小   //font.setFontHeightInPoints((short)10);   //字體加粗   //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);   //設置字體名字    font.setFontName("宋體");   //設置樣式;    HSSFCellStyle style = workbook.createCellStyle();   //設置底邊框;    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);   //設置底邊框顏色;    style.setBottomBorderColor(HSSFColor.BLACK.index);   //設置左邊框;    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);   //設置左邊框顏色;    style.setLeftBorderColor(HSSFColor.BLACK.index);   //設置右邊框;    style.setBorderRight(HSSFCellStyle.BORDER_THIN);   //設置右邊框顏色;    style.setRightBorderColor(HSSFColor.BLACK.index);   //設置頂邊框;    style.setBorderTop(HSSFCellStyle.BORDER_THIN);   //設置頂邊框顏色;    style.setTopBorderColor(HSSFColor.BLACK.index);   //在樣式用應用設置的字體;    style.setFont(font);   //設置自動換行;    style.setWrapText(false);   //設置水平對齊的樣式為居中對齊; //   style.setAlignment(HSSFCellStyle.ALIGN_CENTER);   //設置垂直對齊的樣式為居中對齊; //   style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);         return style;    } }

/** 實現方法*/public void exportExcel() throws Exception{  String  String[] rowsName = new String[]{"序號","列頭1","列頭2","列頭3","列頭4","列頭5"};  List<Object[]> dataList = new ArrayList<Object[]>();  Object[] objs = null;  for (int i = 0; i < 10; i++) {   objs = new Object[rowsName.length];   objs[0] = i;   objs[1] = "測試1";   objs[2] = "測試2";   objs[3] = "測試3";   objs[4] = "測試4";   objs[5] = "測試5";   dataList.add(objs);  }  ExportExcelUtil ex = new ExportExcelUtil(title, rowsName, dataList);  ex.export();   }