问题描述
首先创建一个实体importjava.io.Serializable;publicclassDataPojoimplementsSerializable{/****/privatestaticfinallongserialVersionUID=6230244466642061219L;/***序号*/privateStringdataId;/***公司中文名*/privateStringcnName;/***开户帐号*/privateStringbankAccount;/***结算总额*/privateStringsettleFee;publicStringgetDataId(){returndataId;}publicvoidsetDataId(StringdataId){this.dataId=dataId;}publicStringgetCnName(){returncnName;}publicvoidsetCnName(StringcnName){this.cnName=cnName;}publicStringgetBankAccount(){returnbankAccount;}publicvoidsetBankAccount(StringbankAccount){this.bankAccount=bankAccount;}publicStringgetSettleFee(){returnsettleFee;}publicvoidsetSettleFee(StringsettleFee){this.settleFee=settleFee;}}读取excel的一个类importjava.io.*;importjava.util.ArrayList;importjava.util.List;importjxl.*;publicclassReadExcel{publicstaticList<DataPojo>readExcel(){List<DataPojo>list=newArrayList<DataPojo>();try{Workbookbook=Workbook.getWorkbook(newFile("1000条测试数据.xls"));//获得第一个工作表对象Sheetsheet=book.getSheet(0);intcols=sheet.getColumns();//获取excel列数introws=sheet.getRows();//获取excel行数//循环打印excel中的数据//如果excel中有标题如果不用读取标题的时候则根据要读取的行开始//在开发中一搬我们都会给用户一个固定的模板上传数据//所以excel中的哪一列是什么数据我们很清楚for(inti=2;i<rows;i++){DataPojopojo=newDataPojo();Cellcell1=sheet.getCell(0,i);StringdataId=cell1.getContents();pojo.setDataId(dataId);Cellcell2=sheet.getCell(1,i);StringcnName=cell2.getContents();pojo.setCnName(cnName);Cellcell3=sheet.getCell(2,i);StringbankAccount=cell3.getContents();pojo.setBankAccount(bankAccount);Cellcell4=sheet.getCell(3,i);StringsettleFee=cell4.getContents();pojo.setSettleFee(settleFee);list.add(pojo);}book.close();}catch(Exceptione){System.out.println(e);}returnlist;}publicstaticvoidmain(Stringargs[]){readExcel();}}导出的类importjxl.format.Border;importjxl.format.BorderLineStyle;importjxl.format.Colour;importjxl.format.UnderlineStyle;importjxl.write.WritableCellFormat;importjxl.write.WritableFont;importjxl.write.WritableWorkbook;importjxl.write.WriteException;importjava.io.*;importjava.util.List;importjxl.*;importjxl.write.*;publicclassCreateExcel{publicstaticvoidmain(Stringargs[]){List<DataPojo>list=ReadExcel.readExcel();createExcel(list);}publicstaticvoidcreateExcel(List<DataPojo>list){try{Filefile=newFile("测试1.xls");//打开文件WritableWorkbookbook=Workbook.createWorkbook(file);//生成名为“第一页”的工作表,参数0表示这是第一页WritableSheetsheet=book.createSheet("第一页",0);//在label对象的构造子中指名单元格位置是第一列第一行(0,0)//以及单元格内容为testLabellabel0=newLabel(0,0,"2010年09月结算总额单",setTitle(20));Labellabel1=newLabel(0,1,"序号",setTitle(13));Labellabel2=newLabel(1,1,"公司中文名",setTitle(13));Labellabel3=newLabel(2,1,"开户帐号",setTitle(13));Labellabel4=newLabel(3,1,"结算总额(元)",setTitle(13));//将定义好的单元格添加到工作表中sheet.addCell(label0);//合并单元格//从col1列到col2列从row1行到row2行//mergeCells(intcol1,introw1,intcol2,introw2)sheet.mergeCells(0,0,3,0);sheet.addCell(label1);sheet.addCell(label2);sheet.addCell(label3);sheet.addCell(label4);//循环将数据录入到excel中for(inti=0;i<list.size();i++){DataPojodataPojo=list.get(i);Labellab0=newLabel(0,i+2,dataPojo.getDataId(),cellFormat(i));Labellab1=newLabel(1,i+2,dataPojo.getCnName(),cellFormat(i));Labellab2=newLabel(2,i+2,dataPojo.getBankAccount(),cellFormat(i));Labellab3=newLabel(3,i+2,dataPojo.getSettleFee(),cellFormat(i));sheet.addCell(lab0);sheet.addCell(lab1);sheet.addCell(lab2);sheet.addCell(lab3);//sheet.setRowView(i+2,1000);//设置高度一般都不用设置高度//设置宽度第一个参数是第几列第二个参数是宽度sheet.setColumnView(0,20);sheet.setColumnView(1,25);sheet.setColumnView(2,20);sheet.setColumnView(3,20);}//写入数据并关闭文件book.write();book.close();System.out.println("生成的文件路径:"+file.getCanonicalPath());//System.out.println(file.getAbsolutePath());}catch(Exceptione){System.out.println(e);}}/***设置背景色**@parampoint*@return*@throwsWriteException*/publicstaticWritableCellFormatcellFormat(intdataId)throwsWriteException{Colourcolor=null;if(dataId%2==0){color=Colour.WHITE;}else{color=Colour.ROSE;}//设置字体颜色WritableFontfont=newjxl.write.WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK);WritableCellFormatcellFormat=newjxl.write.WritableCellFormat(font);//设置单元格背景颜色cellFormat.setBackground(color);cellFormat.setBorder(Border.ALL,BorderLineStyle.THIN);//增加边框returncellFormat;}publicstaticWritableCellFormatsetTitle(intfontSize)throwsWriteException{Colourcolor=Colour.GRAY_25;//设置字体颜色WritableFontfont=newjxl.write.WritableFont(WritableFont.ARIAL,fontSize,WritableFont.NO_BOLD,false,UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK);WritableCellFormatcellFormat=newjxl.write.WritableCellFormat(font);//设置单元格背景颜色cellFormat.setBackground(color);//cellFormat.setShrinkToFit(true);cellFormat.setBorder(Border.ALL,BorderLineStyle.THIN);//增加边框returncellFormat;}}更新的很简单importjava.io.*;importjxl.*;importjxl.write.*;publicclassUpdateExcel{publicstaticvoidmain(Stringargs[]){updateExcel();}publicstaticvoidupdateExcel(){try{//excel获得文件Workbookwb=Workbook.getWorkbook(newFile("测试.xls"));//打开一个文件的副本,并且指定数据写回到原文件WritableWorkbookbook=Workbook.createWorkbook(newFile("测试.xls"),wb);//添加一个工作表WritableSheetsheet=book.createSheet("第二页",1);sheet.addCell(newLabel(0,0,"第二页的测试数据"));book.write();book.close();}catch(Exceptione){System.out.println(e);}}}
解决方案
解决方案二:
你这是总结帖?
解决方案三:
标题再说下用的是jxl的解析包
解决方案四:
感谢楼主提供该总结,还是不错的!