excel下载

项目里用的excel下载功能.记下以后有用.

主要是一个xlsCreator

public class MyXlsCreator {@SuppressWarnings("unused")private static Logger logger = Logger.getLogger(MyXlsCreator.class);private SimpleDateFormat sdf=new SimpleDateFormat("yyyy/MM/dd");private HSSFWorkbook wb;private CellStyle alignCenter;private CellStyle alignRight;private CellStyle dateStyle;private CellStyle topBorder;private CellStyle totalTopBorder;private CellStyle money;private CellStyle fontStyle;private CellStyle bigTitleStyle;private MySheet[] mySheets;public MyXlsCreator(MySheet ...mySheets){ this.wb= new HSSFWorkbook();this.mySheets=mySheets;HSSFFont font = wb.createFont();font.setFontName("宋体");    font.setFontHeightInPoints((short) 12);// 设置字体大小alignCenter=wb.createCellStyle();//居中对齐的样式alignCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中对齐的样式alignCenter.setFont(font);alignRight=wb.createCellStyle();alignRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT);alignRight.setFont(font);dateStyle=wb.createCellStyle();HSSFDataFormat format = wb.createDataFormat();  dateStyle.setDataFormat(format.getFormat("yyyy/MM/dd"));dateStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);dateStyle.setFont(font);topBorder=wb.createCellStyle();topBorder.setBorderTop(HSSFCellStyle.BORDER_THICK);topBorder.setFont(font);totalTopBorder=wb.createCellStyle();totalTopBorder.setBorderTop(HSSFCellStyle.BORDER_THICK);totalTopBorder.setAlignment(HSSFCellStyle.ALIGN_RIGHT);totalTopBorder.setDataFormat(format.getFormat("#,##0.00"));totalTopBorder.setFont(font);money=wb.createCellStyle();money.setDataFormat(format.getFormat("#,##0.00"));money.setAlignment(HSSFCellStyle.ALIGN_RIGHT);money.setFont(font);fontStyle=wb.createCellStyle();fontStyle.setFont(font);HSSFFont bigTitleFont = wb.createFont();bigTitleFont.setFontName("宋体");bigTitleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);bigTitleFont.setFontHeightInPoints((short)22);bigTitleStyle=wb.createCellStyle();bigTitleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);bigTitleStyle.setFont(bigTitleFont);init();}@SuppressWarnings("deprecation")private void init(){for(MySheet each:mySheets){List<MyTab> tabs=each.getTabs();HSSFSheet sheet=wb.createSheet(each.getSheetName());sheet.setDefaultColumnWidth(15);int col=6;//默认列数try {//找出第一个表的列数col=each.getTabs().get(0).getHeads().length;} catch (Exception e) {}int totalrow=0;if(each.getBigTitle()!=null){HSSFRow row=sheet.createRow(totalrow);sheet.addMergedRegion(new Region(totalrow,(short)0,totalrow,(short)(col-1))); row.setHeightInPoints(26);Cell cell=row.createCell(0);cell.setCellValue(each.getBigTitle());cell.setCellStyle(bigTitleStyle);totalrow++;}for(MyTab eachTab:tabs){//写标题if(eachTab.getTitleList()!=null){for(int ti=0;ti<eachTab.getTitleList().length;ti++){HSSFRow row=sheet.createRow(totalrow);sheet.addMergedRegion(new Region(totalrow,(short)0,totalrow,(short)(col-1))); totalrow++;Cell cell=row.createCell(0);cell.setCellValue(eachTab.getTitleList()[ti]);cell.setCellStyle(fontStyle);}}if(eachTab.getHeads()!=null){//写表头HSSFRow row=sheet.createRow(totalrow);totalrow++;for(int hi=0;hi<eachTab.getHeads().length;hi++){HSSFCell cell=row.createCell(hi);cell.setCellStyle(alignCenter);cell.setCellValue(eachTab.getHeads()[hi]);}}if(eachTab.getItems()!=null){//写数据for(Object[] rowData:eachTab.getItems()){HSSFRow row1=sheet.createRow(totalrow);totalrow++;for(int di=0;di<rowData.length;di++){Object data=rowData[di];if(data==null){continue;}Cell cell=row1.createCell(di);if(data instanceof Integer){cell.setCellValue((Integer)data);cell.setCellStyle(alignCenter);}if(data instanceof Double){cell.setCellValue((Double)data);cell.setCellStyle(money);}if(data instanceof String){cell.setCellValue((String)data);cell.setCellStyle(alignCenter);}if(data instanceof Date){Date date = null;try {date = sdf.parse(sdf.format((Date)data));} catch (ParseException e) {// TODO Auto-generated catch blocke.printStackTrace();}cell.setCellValue(date);cell.setCellStyle(dateStyle);}}}}}}}public void write(String path) throws IOException{File outPutFile=new File(path);if(!outPutFile.exists()){outPutFile.createNewFile();}FileOutputStream os = new FileOutputStream(outPutFile);      wb.write(os);      os.close(); }public void write(OutputStream outputStream) throws IOException{wb.write(outputStream);outputStream.close();}}

  具体实现的时候用于创建sheet,在xlsCreator构造函数中调用,初始化表

public MySheet getPersonalInfo(){MySheet mySheet= new MySheet();mySheet.setSheetName("个人信息");MyTab tab=new MyTab();tab.setTitleList(new String[]{我的个人信息});tab.setHeads(new String[]{"姓名","手机号码","身份证号码","年龄","性别"});Object[][] datas=new Object[xls.size()][5];if(xls.size()>0){    for(int i=0;i<xls.size();i++){         datas[i][0]="吴星";datas[i][1]="135****2682";datas[i][2]="360***3412";datas[i][3]="18";datas[i][4]="男";             }}tab.setItems(datas);        mySheet.addTab(tab);        return mySheet;}public class MySheet {@SuppressWarnings("unused")private static Logger logger = Logger.getLogger(MySheet.class);private String sheetName;private String bigTitle;private List<MyTab> tabs;public MySheet(){tabs=new ArrayList<MyTab>();}public String getSheetName() {return sheetName;}public void setSheetName(String sheetName) {this.sheetName = sheetName;}public String getBigTitle() {return bigTitle;}public void setBigTitle(String bigTitle) {this.bigTitle = bigTitle;}public List<MyTab> getTabs() {return tabs;}public void setTabs(List<MyTab> tabs) {this.tabs = tabs;}public void addTab(MyTab tab){tabs.add(tab);}public void addTabs(Collection<MyTab> tabs){this.tabs.addAll(tabs);}}

demo 如下

public void DLInvestDetail(HttpServletResponse response){MyXlsCreator creator=new MyXlsCreator(xlsServiceImpl.getPersonalInfo());String fileName = sdf.format(new Date())+"个人信息详情.xls";try {fileName = URLEncoder.encode(fileName, "utf-8");} catch (UnsupportedEncodingException e1) {logger.error(e1.getMessage(),e1.fillInStackTrace());}response.setContentType("application/x-download;charset=utf-8");  response.addHeader("Content-Disposition","attachment;filename=" + fileName);   OutputStream outputStream;try {outputStream = response.getOutputStream();creator.write(outputStream);} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}}