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導(dǎo)出工具類 * @author ts */public class ExportExcelUtil { //顯示的導(dǎo)出表的標(biāo)題 private String title; //導(dǎo)出表的列名 private String[] rowName ;  private List<Object[]> dataList = new ArrayList<Object[]>(); // HttpServletResponse response;  //構(gòu)造方法，傳入要導(dǎo)出的數(shù)據(jù) public ExportExcelUtil(String title,String[] rowName,List<Object[]> dataList){  this.dataList = dataList;  this.rowName = rowName;  this.title = title; }    /*  * 導(dǎo)出數(shù)據(jù)  * */ public void export() throws Exception{  try{   HSSFWorkbook workbook = new HSSFWorkbook();      // 創(chuàng)建工作簿對(duì)象   HSSFSheet sheet = workbook.createSheet(title);      // 創(chuàng)建工作表      // 產(chǎn)生表格標(biāo)題行   HSSFRow rowm = sheet.createRow(0);   HSSFCell cellTiltle = rowm.createCell(0);   rowm.setHeightInPoints(25);           //設(shè)置標(biāo)題行默認(rèn)行高      //sheet樣式定義【getColumnTopStyle()/getStyle()均為自定義方法 - 在下面 - 可擴(kuò)展】   HSSFCellStyle columnTitleStyle = this.getTitleTopStyle(workbook);//獲取標(biāo)題行樣式   HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook); //獲取列頭樣式對(duì)象   HSSFCellStyle style = this.getStyle(workbook);     //單元格樣式對(duì)象      sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length-1)));    cellTiltle.setCellStyle(columnTitleStyle);   cellTiltle.setCellValue(title);      // 定義所需列數(shù)   int columnNum = rowName.length;   HSSFRow rowRowName = sheet.createRow(2);    // 在索引2的位置創(chuàng)建行(最頂端的行開始的第二行)   rowRowName.setHeightInPoints(25);       //將列頭設(shè)置默認(rèn)行高   // 將列頭設(shè)置到sheet的單元格中   for(int n=0;n<columnNum;n++){    HSSFCell cellRowName = rowRowName.createCell(n);    //創(chuàng)建列頭對(duì)應(yīng)個(gè)數(shù)的單元格    cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);    //設(shè)置列頭單元格的數(shù)據(jù)類型    HSSFRichTextString text = new HSSFRichTextString(rowName[n]);    cellRowName.setCellValue(text);         //設(shè)置列頭單元格的值    cellRowName.setCellStyle(columnTopStyle);      //設(shè)置列頭單元格樣式   }      //將查詢出的數(shù)據(jù)設(shè)置到sheet對(duì)應(yīng)的單元格中   for(int i=0;i<dataList.size();i++){        Object[] obj = dataList.get(i);//遍歷每個(gè)對(duì)象    HSSFRow row = sheet.createRow(i+3);//創(chuàng)建所需的行數(shù)    row.setHeightInPoints(20); //將創(chuàng)建出的行設(shè)置默認(rèn)行高    for(int j=0; j<obj.length; j++){     HSSFCell cell = null; //設(shè)置單元格的數(shù)據(jù)類型     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());      //設(shè)置單元格的值      }     }     cell.setCellStyle(style);         //設(shè)置單元格樣式    }   }   //讓列寬隨著導(dǎo)出的列長自動(dòng)適應(yīng)   for (int colNum = 0; colNum < columnNum; colNum++) {    int columnWidth = sheet.getColumnWidth(colNum) / 256;    for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {     HSSFRow currentRow;     //當(dāng)前行未被使用過     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已經(jīng)封裝成工具類所以下面這段代碼注釋掉//     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();  }   }  /*   * 設(shè)置標(biāo)題樣式  */   public HSSFCellStyle getTitleTopStyle(HSSFWorkbook workbook) {      // 設(shè)置字體   HSSFFont font = workbook.createFont();   //設(shè)置字體大小   font.setFontHeightInPoints((short)24);   //字體加粗   font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);   //設(shè)置字體名字    font.setFontName("宋體");   //設(shè)置樣式;    HSSFCellStyle style = workbook.createCellStyle();   //設(shè)置底邊框;    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);   //設(shè)置底邊框顏色;    style.setBottomBorderColor(HSSFColor.BLACK.index);   //設(shè)置左邊框;    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);   //設(shè)置左邊框顏色;    style.setLeftBorderColor(HSSFColor.BLACK.index);   //設(shè)置右邊框;    style.setBorderRight(HSSFCellStyle.BORDER_THIN);   //設(shè)置右邊框顏色;    style.setRightBorderColor(HSSFColor.BLACK.index);   //設(shè)置頂邊框;    style.setBorderTop(HSSFCellStyle.BORDER_THIN);   //設(shè)置頂邊框顏色;    style.setTopBorderColor(HSSFColor.BLACK.index);   //在樣式用應(yīng)用設(shè)置的字體;    style.setFont(font);   //設(shè)置自動(dòng)換行;    style.setWrapText(false);   //設(shè)置水平對(duì)齊的樣式為居中對(duì)齊;    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);   //設(shè)置垂直對(duì)齊的樣式為居中對(duì)齊;    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);      return style;     }   /*   * 列頭單元格樣式  */   public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {      // 設(shè)置字體   HSSFFont font = workbook.createFont();   //設(shè)置字體大小   font.setFontHeightInPoints((short)11);   //字體加粗   font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);   //設(shè)置字體名字    font.setFontName("宋體");   //設(shè)置樣式;    HSSFCellStyle style = workbook.createCellStyle();   //設(shè)置底邊框;    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);   //設(shè)置底邊框顏色;    style.setBottomBorderColor(HSSFColor.BLACK.index);   //設(shè)置左邊框;    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);   //設(shè)置左邊框顏色;    style.setLeftBorderColor(HSSFColor.BLACK.index);   //設(shè)置右邊框;    style.setBorderRight(HSSFCellStyle.BORDER_THIN);   //設(shè)置右邊框顏色;    style.setRightBorderColor(HSSFColor.BLACK.index);   //設(shè)置頂邊框;    style.setBorderTop(HSSFCellStyle.BORDER_THIN);   //設(shè)置頂邊框顏色;    style.setTopBorderColor(HSSFColor.BLACK.index);   //在樣式用應(yīng)用設(shè)置的字體;    style.setFont(font);   //設(shè)置自動(dòng)換行;    style.setWrapText(false);   //設(shè)置水平對(duì)齊的樣式為居中對(duì)齊;    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);   //設(shè)置垂直對(duì)齊的樣式為居中對(duì)齊;    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);      return style;     }    /*   * 列數(shù)據(jù)信息單元格樣式  */   public HSSFCellStyle getStyle(HSSFWorkbook workbook) {   // 設(shè)置字體   HSSFFont font = workbook.createFont();   //設(shè)置字體大小   //font.setFontHeightInPoints((short)10);   //字體加粗   //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);   //設(shè)置字體名字    font.setFontName("宋體");   //設(shè)置樣式;    HSSFCellStyle style = workbook.createCellStyle();   //設(shè)置底邊框;    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);   //設(shè)置底邊框顏色;    style.setBottomBorderColor(HSSFColor.BLACK.index);   //設(shè)置左邊框;    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);   //設(shè)置左邊框顏色;    style.setLeftBorderColor(HSSFColor.BLACK.index);   //設(shè)置右邊框;    style.setBorderRight(HSSFCellStyle.BORDER_THIN);   //設(shè)置右邊框顏色;    style.setRightBorderColor(HSSFColor.BLACK.index);   //設(shè)置頂邊框;    style.setBorderTop(HSSFCellStyle.BORDER_THIN);   //設(shè)置頂邊框顏色;    style.setTopBorderColor(HSSFColor.BLACK.index);   //在樣式用應(yīng)用設(shè)置的字體;    style.setFont(font);   //設(shè)置自動(dòng)換行;    style.setWrapText(false);   //設(shè)置水平對(duì)齊的樣式為居中對(duì)齊; //   style.setAlignment(HSSFCellStyle.ALIGN_CENTER);   //設(shè)置垂直對(duì)齊的樣式為居中對(duì)齊; //   style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);         return style;    } }

/** 實(shí)現(xiàn)方法*/public void exportExcel() throws Exception{  String  String[] rowsName = new String[]{"序號(hào)","列頭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] = "測(cè)試1";   objs[2] = "測(cè)試2";   objs[3] = "測(cè)試3";   objs[4] = "測(cè)試4";   objs[5] = "測(cè)試5";   dataList.add(objs);  }  ExportExcelUtil ex = new ExportExcelUtil(title, rowsName, dataList);  ex.export();   }