java数据生成excel_java中让数据生成excle文件并且支持下载

packagecom.ai.loc.util;importjava.io.File;importjava.io.IOException;importjava.util.ArrayList;importjava.util.HashMap;importjava.util.List;importjava.util.Map;importjxl.Workbook;importjxl.format.Alignment;importjxl.format.Border;importjxl.format.BorderLineStyle;importjxl.format.Colour;importjxl.format.VerticalAlignment;importjxl.write.Label;importjxl.write.NumberFormats;importjxl.write.WritableCellFormat;importjxl.write.WritableFont;importjxl.write.WritableSheet;importjxl.write.WritableWorkbook;importjxl.write.WriteException;public classMakeExcel {private static WritableCellFormat titleFormat = null;private static WritableCellFormat bodyFormat = null;private static WritableCellFormat noteFormat = null;private static WritableCellFormat floatFormat = null;private static WritableCellFormat intFormat = null;private static boolean init = false;publicMakeExcel() {

}public void init() throwsWriteException {

WritableFont font1, font2, font3, font4;

font1= new WritableFont(WritableFont.ARIAL, 9, WritableFont.BOLD,false);

titleFormat= newWritableCellFormat(font1);

titleFormat.setBackground(Colour.ORANGE);

titleFormat.setBorder(Border.ALL, BorderLineStyle.THIN);

titleFormat.setAlignment(Alignment.CENTRE);

font2= new WritableFont(WritableFont.ARIAL, 9, WritableFont.BOLD,false);

noteFormat= newWritableCellFormat(font2);

noteFormat.setBackground(Colour.ORANGE);

noteFormat.setBorder(Border.ALL, BorderLineStyle.THIN);

noteFormat.setAlignment(Alignment.CENTRE);

noteFormat.setVerticalAlignment(VerticalAlignment.CENTRE);

noteFormat.setWrap(true);

font3= new WritableFont(WritableFont.ARIAL, 9, WritableFont.NO_BOLD,false);

bodyFormat= newWritableCellFormat(font3);

bodyFormat.setBackground(Colour.LIGHT_GREEN);

bodyFormat.setBorder(Border.ALL, BorderLineStyle.THIN);

font4= new WritableFont(WritableFont.ARIAL, 9, WritableFont.NO_BOLD,false);

floatFormat= newWritableCellFormat(font4, NumberFormats.FLOAT);

floatFormat.setBackground(Colour.LIGHT_GREEN);

floatFormat.setBorder(Border.ALL, BorderLineStyle.THIN);//Arial���壬9�ţ��Ǵ��壬��Ԫ����ɫ�����ֱ߿�

font4 = new WritableFont(WritableFont.ARIAL, 9, WritableFont.NO_BOLD,false);

intFormat= newWritableCellFormat(font4, NumberFormats.INTEGER);

intFormat.setBackground(Colour.LIGHT_GREEN);

intFormat.setBorder(Border.ALL, BorderLineStyle.THIN);

init= true;

}

//入参是一个list,一条数据存一个map对象，map对象中存列和值得对应关系，destFile当然就是要存的文件信息。 headList很重要，它是列的展示，当然和数据的列要对应不然找不到对应的地方存储。public static void CreateExcelFile(List> list, File destFile,ListheadList,String message)throwsWriteException, IOException {int sizeAll=list.size();int zheng=sizeAll/65534;int yu=sizeAll%65534;int sheetSize=1;int flagList=1;if(sizeAll<=65534){

sheetSize=1;

}else{if(yu>0){

sheetSize=zheng+1;

}else{

sheetSize=zheng;

}

}if (init == false)newMakeExcel().init();

WritableWorkbook book= null;

book=Workbook.createWorkbook(destFile);if(list.size()==0){

book.write();

}else{for(int j=0;j

System.out.println("*************************sheet"+j+"***************************");

WritableSheet sheet= book.createSheet(destFile.getName().replace(".xls", "")+j, j);

WritableFont BoldFont=new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);

WritableCellFormat wcf_center= newWritableCellFormat(BoldFont);

wcf_center.setBorder(Border.ALL, BorderLineStyle.THIN);//线条

wcf_center.setVerticalAlignment(VerticalAlignment.CENTRE); //文字垂直对齐

wcf_center.setAlignment(Alignment.CENTRE); //文字水平对齐

wcf_center.setWrap(false); //文字是否换行

for(int i=0;i

sheet.setColumnView(i,60);

}

sheet.mergeCells(0, 0, headList.size()-1, 0);

sheet.addCell(new Label(0, 0, message, wcf_center));

index= 0;for(String name :headList){

sheet.addCell(new Label(index, 1, name,wcf_center));

index++;

}int i=0;int t=2;while(flagList<=list.size()){

index= 0;if(i<65534){for(String name :headList){

sheet.addCell(new Label(index, t, list.get(flagList-1).get(name)+""));

index++;

}

i++;

t++;

flagList++;

}else{break;

}

}

}

}

book.write();if (book != null)

book.close();

}public static voidmain(String[] args) {

List> list=new ArrayList<>();for(int i=0;i<195534;i++){

Map map=new HashMap<>();

map.put("a","a"+i );

map.put("b","b"+i );

map.put("c","c"+i );

map.put("d","d"+i );

list.add(map);

}

List ll=new ArrayList<>();

ll.add("a");

ll.add("b");

ll.add("c");

ll.add("d");try{

CreateExcelFile(list,new File("d:/a.xls"),ll,"hahaha");

}catch(WriteException e) {//TODO Auto-generated catch block

e.printStackTrace();

}catch(IOException e) {// //TODO Auto-generated catch block

e.printStackTrace();

}

}

}