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();
}
}
}