jxl操作excel.docx

用jxl读取excel的数据，由于excel数据在录入时的各种原因，数据后面都有空格，而且读出来以后（也许是编码原因），数据口面不是出 现?就是出现一个不知所谓的乱码符，不要考虑用替换，因为替换只有在你的项目编码方式和内存中excel数据编码方式一样的时候才能替换，否则你连保 存都会提示编码问题而保存不了。直接用subSequence(0, cellContent.length()-1) 就可以了同时提醒一下，读取出来的数据时Cell类型的话，直接getContent是可以得到内容的，但具体内容最好依靠下面的方法获Java代码 1. if(cell.getType()=CellType.LABEL)2. LabelCelllabelCell=(LabelCell)cell;3. StringcellContent=labelCell.getString();4. cellContent=(String)cellContent.subSequence(0,cellContent.length()-1);5. column_contentscols=cellContent;6. else7. if(cell.getType()=CellType.NUMBER)8. /number的话不用去空格就可以，我测试是这样9. NumberCellnumberCell=(NumberCell)cell;10. StringcellContent=numberCell.getContents();11. column_contentscols=cellContent;12. else13. if(cell.getType()=CellType.DATE)14. DateCelldateCell=(DateCell)cell;15. DatedateDemo=dateCell.getDate();16. StringcellContent=dateDemo.toString();17. column_contentscols=cellContent;18. package com.study.poi;import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.io.InputStream;import java.text.DecimalFormat;import java.text.ParseException;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.Iterator;import java.util.List;import org.apache.poi.hssf.usermodel.HSSFDateUtil;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import com.study.entity.Emp;public class PoiExcelTest public static void main(String args) Class clazz=new ClassInteger.class,String.class,String.class,Integer.class,Date.class,Double.class,Double.class,Integer.class; List list=null; DecimalFormat df=new DecimalFormat(0.00); try list = readExcel(d:+File.separator+test.xls,clazz); catch (ParseException e) e.printStackTrace(); for (Iterator iter=list.iterator(); iter.hasNext();) Emp emp=iter.next(); System.out.println(Emp:+emp.getEmpno()+t+emp.getEname()+t+df.format(emp.getSal()+t+df.format(emp.getComm()+t+new SimpleDateFormat(yyyy-MM-dd).format(emp.getHiredate()+t+emp.getDeptno(); private static int version2003=2003; private static int version2007=2003; private static int version=version2003; private static Workbook wb; private static Sheet sheet; private static Row row; private static Cell cell; public static List readExcel(String excelFilePath,Class clazz) throws ParseException List list=new ArrayList(); Emp emp; version=(excelFilePath.endsWith(.xls)?version2003:version2007); if(version=2003) try InputStream stream=new FileInputStream(new File(excelFilePath); wb=new HSSFWorkbook(stream); catch (FileNotFoundException e) e.printStackTrace(); catch (IOException e) e.printStackTrace(); else if(version=2007) try wb=new XSSFWorkbook(excelFilePath); catch (IOException e) e.printStackTrace(); sheet=wb.getSheetAt(0); int rows=sheet.getLastRowNum(); int cells=sheet.getRow(0).getPhysicalNumberOfCells(); for (int i = 0; i rows; i+) row=sheet.getRow(i+1); emp=new Emp(); for (int j = 0; j cells; j+) cell=row.getCell(j); Object obj=getCellValue(cell, clazzj); switch (j) case 0: emp.setEmpno(Integer)obj); break; case 1: emp.setEname(String)obj); break; case 2: emp.setJob(String)obj); break; case 3: emp.setMgr(Integer)obj); break; case 4: emp.setHiredate(new SimpleDateFormat(yyyy-MM-dd).parse(obj.toString(); break; case 5: emp.setSal(Double)obj); break; case 6: emp.setComm(Double)obj); break; case 7: emp.setDeptno(Integer)obj); break; default: break; list.add(emp); return list; public static Object getCellValue(Cell cell,Class clazz) String name=clazz.getSimpleName(); if(String.equals(name) return cell.getStringCellValue(); else if(Double.equals(name) try double valued=cell.getNumericCellValue(); return valued; catch (Exception e) return 0.0; else if(Integer.equals(name) try int valuei=(int)cell.getNumericCellValue(); return valuei; catch (Exception e) return 0; else if(Date.equals(name) if(HSSFDateUtil.isCellDateFormatted(cell) Date date=cell.getDateCellValue(); if(date=null) return new SimpleDateFormat(yyyy-MM-dd).format(new Date(); else return new SimpleDateFormat(yyyy-MM-dd).format(date); return null; 1. publicclassSummaryHSSF2. 3. publicstaticvoidmain(Stringargs)throwsIOException4. /创建Workbook对象（这一个对象代表着对应的一个Excel文件）5. /HSSFWorkbook表示以xls为后缀名的文件6. Workbookwb=newHSSFWorkbook();7. /获得CreationHelper对象,这个应该是一个帮助类8. CreationHelperhelper=wb.getCreationHelper();9. /创建Sheet并给名字(表示Excel的一个Sheet)10. Sheetsheet1=wb.createSheet(HSSF_Sheet_1);11. Sheetsheet2=wb.createSheet(HSSF_Sheet_2);12. /Row表示一行Cell表示一列13. Rowrow=null;14. Cellcell=null;15. for(inti=0;i60;i=i+2)16. /获得这个sheet的第i行17. row=sheet1.createRow(i);18. /设置行长度自动19. /row.setHeight(short)500);20. row.setHeightInPoints(20);21. /row.setZeroHeight(true);22. for(intj=0;j25;j+)23. /设置每个sheet每一行的宽度,自动,根据需求自行确定24. sheet1.autoSizeColumn(j+1,true);25. /创建一个基本的样式26. CellStylecellStyle=SummaryHSSF.createStyleCell(wb);27. /获得这一行的每j列28. cell=row.createCell(j);29. if(j=0)30. /设置文字在单元格里面的位置31. cellStyle=SummaryHSSF.setCellStyleAlignment(cellStyle,CellStyle.ALIGN_CENTER,CellStyle.VERTICAL_CENTER);32. /先创建字体样式,并把这个样式加到单元格的字体里面33. cellStyle.setFont(createFonts(wb);34. /把这个样式加到单元格里面35. cell.setCellStyle(cellStyle);36. /给单元格设值37. cell.setCellValue(true);38. elseif(j=1)39. /设置文字在单元格里面的位置40. cellStyle=SummaryHSSF.setCellStyleAlignment(cellStyle,CellStyle.ALIGN_CENTER,CellStyle.VERTICAL_CENTER);41. /设置这个样式的格式(Format)42. cellStyle=SummaryHSSF.setCellFormat(helper,cellStyle,#,#0.0000);43. /先创建字体样式,并把这个样式加到单元格的字体里面44. cellStyle.setFont(createFonts(wb);45. /把这个样式加到单元格里面46. cell.setCellStyle(cellStyle);47. /给单元格设值48. cell.setCellValue(newDouble(2008.2008);49. elseif(j=2)50. cellStyle=SummaryHSSF.setCellStyleAlignment(cellStyle,CellStyle.ALIGN_CENTER,CellStyle.VERTICAL_CENTER);51. cellStyle.setFont(createFonts(wb);52. cell.setCellStyle(cellStyle);53. cell.setCellValue(helper.createRichTextString(RichString+i+j);54. elseif(j=3)55. cellStyle=SummaryHSSF.setCellStyleAlignment(cellStyle,CellStyle.ALIGN_CENTER,CellStyle.VERTICAL_CENTER);56. cellStyle=SummaryHSSF.setCellFormat(helper,cellStyle,MM-yyyy-dd);57. cell.setCellStyle(cellStyle);58. cell.setCellValue(newDate();59. elseif(j=24)60. cellStyle=SummaryHSSF.setCellStyleAlignment(cellStyle,CellStyle.ALIGN_CENTER,CellStyle.VERTICAL_CENTER);61. cellStyle.setFont(createFonts(wb);62. /设置公式63. cell.setCellFormula(SUM(E+(i+1)+:X+(i+1)+);64. else65. cellStyle=SummaryHSSF.setCellStyleAlignment(cellStyle,CellStyle.ALIGN_CENTER,CellStyle.VERTICAL_CENTER);66. cellStyle=SummaryHSSF.setFillBackgroundColors(cellStyle,IndexedColors.ORANGE.getIndex(),IndexedColors.ORANGE.getIndex(),CellStyle.SOLID_FOREGROUND);67. cell.setCellStyle(cellStyle);68. cell.setCellValue(1);69. 70. 71. 72. /输出73. OutputStreamos=newFileOutputStream(newFile(c:/SummaryHSSF.xls);74. wb.write(os);75. os.close();76. 77. /*78. *边框79. *paramwb80. *return81. */82. publicstaticCellStylecreateStyleCell(Workbookwb)83. CellStylecellStyle=wb.createCellStyle();84. /设置一个单元格边框颜色85. cellStyle.setBorderBottom(CellStyle.BORDER_THIN);86. cellStyle.setBorderTop(CellStyle.BORDER_THIN);87. cellStyle.setBorderLeft(CellStyle.BORDER_THIN);88. cellStyle.setBorderRight(CellStyle.BORDER_THIN);89. /设置一个单元格边框颜色90. cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex();91. cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex();92. cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex();93. cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex();94. returncellStyle;95. 96. /*97. *设置文字在单元格里面的位置98. *CellStyle.ALIGN_CENTER99. *CellStyle.VERTICAL_CENTER100. *paramcellStyle101. *paramhalign102. *paramvalign103. *return104. */105. publicstaticCellStylesetCellStyleAlignment(CellStylecellStyle,shorthalign,shortvalign)106. /设置上下107. cellStyle.setAlignment(CellStyle.ALIGN_CENTER);108. /设置左右109. cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);110. returncellStyle;111. 112. /*113. *格式化单元格114. *如#,#0.00,m/d/yy去HSSFDataFormat或XSSFDataFormat里面找115. *paramcellStyle116. *paramfmt117. *return118. */119. publicstaticCellStylesetCellFormat(CreationHelperhelper,CellStylecellStyle,Stringfmt)120. /还可以用其它方法创建format121. cellStyle.setDataFormat(helper.createDataFormat().getFormat(fmt);122. returncellStyle;123. 124. /*125. *前景和背景填充的着色126. *paramcellStyle127. *parambgIndexedColors.ORANGE.getIndex();128. *paramfgIndexedColors.ORANGE.getIndex();129. *paramfpCellStyle.SOLID_FOREGROUND130. *return131. */132. publicstaticCellStylesetFillBackgroundColors(CellStylecellStyle,shortbg,shortfg,shortfp)133. /cellStyle.setFillBackgroundColor(bg);134. cellStyle.setFillForegroundColor(fg);135. cellStyle.setFillPattern(fp);136. returncellStyle;137. 138. /*139. *设置字体140. *paramwb141. *return142. */143. publicstaticFontcreateFonts(Workbookwb)144. /创建Font对象145. Fontfont=wb.createFont();146. /设置字体147. font.setFontName(黑体);148. /着色149. font.setColor(HSSFColor.BLUE.index);150. /斜体151. font.setItalic(true);152. /字体大小153. font.setFontHeight(short)300);154. returnfont;155. 156. 读取Excel文件 Java代码 1. publicclassReadExcel2. publicstaticvoidmain(Stringargs)throwsException3. InputStreamis=newFileInputStream(newFile(c:/SummaryHSSF.xls);4. /根据输入流创建Workbook对象5. Workbookwb=WorkbookFactory.create(is);6. /get到Sheet对象7. Sheetsheet=wb.getSheetAt(0);8. /这个必须用接口9. for(Rowrow:sheet)10. for(Cellcell:row)11. /cell.getCellType是获得cell里面保存的值的type12. /如Cell.CELL_TYPE_STRING13. switch(cell.getCellType()14. caseCell.CELL_TYPE_BOOLEAN:15. /得到Boolean对象的方法16. System.out.print(cell.getBooleanCellValue()+);17. break;18. caseCell.CELL_TYPE_NUMERIC:19. /先看是否是日期格式20. if(DateUtil.isCellDateFormatted(cell)21. /读取日期格式22. System.out.print(cell.getDateCellValue()+);23. else24. /读取数字25. System.out.print(cell.getNumericCellValue()+);26. 27. break;28. caseCell.CELL_TYPE_FORMULA:29. /读取公式30. System.out.print(cell.getCellFormula()+);31. break;32. caseCell.CELL_TYPE_STRING:33. /读取String34. System.out.print(cell.getRichStringCellValue().toString()+);35. break;36. 37. 38. System.out.println();39. 40. 41. 还有一种传统的读法 Java代码 1. Sheetsheet=wb.getSheetAt(0);2. for(Iteratorrit=sheet.rowIterator();rit.hasNext();)3. Rowrow=(Row)rit.next();4. for(Iteratorcit=row.cellIterator();cit.hasNext();)5. Cellcell=(Cell)cit.next();6. /Dosomethinghere7. 8. 9. HSSFSheetsheet=wb.getSheetAt(0);10. for(Iteratorrit=(Iterator)sheet.rowIterator();rit.hasNext();)11. HSSFRowrow=rit.next();12. for(Iteratorcit=(Iterator)row.cellIterator();cit.hasNext();)13. HSSFCellcell=cit.next();14. /Dosomethinghere15. 16. 1.jxl 生成报表Java代码 1. packageexcel;2. 3. importjava.io.FileOutputStream;4. importjava.io.OutputStream;5. importjava.text.SimpleDateFormat;6. importjava.util.Date;7. 8. importjxl.Workbook;9. importjxl.format.Alignment;10. importjxl.format.Border;11. importjxl.format.BorderLineStyle;12. importjxl.format.CellFormat;13. importjxl.write.Label;14. importjxl.write.WritableCellFormat;15. importjxl.write.WritableFont;16. importjxl.write.WritableSheet;17. importjxl.write.WritableWorkbook;18. /*19. *20. *jxlCreate.java21. *copyrightCopyright:2009-201222. *creator周辉23. *create-timeMar9,20101:35:19PM24. *revision$Id:*25. */26. publicclassjxlCreate27. 28. /*29. *paramargs30. */31. publicstaticvoidmain(Stringargs)32. /准备设置excel工作表