jxl操作excel

用jxl读取excel的数据，由于excel数据在录入时的各种原因，数据后面都有空格，而且读出来以后（也许是编码原因），数据口面不是出现"?"就是出现一个不知所谓的乱码符，不要考虑用替换，因为替换只有在你的项目编码方式和内存中excel数据编码方式一样的时候才能替换，否则你连保存都会提示编码问题而保存不了。

直接用subSequence(0, cellContent.length()-1) 就可以了同时提醒一下，读取出来的数据时Cell类型的话，直接getContent是可以得到内容的，但具体内容最好依靠下面的方法获Java代码1.if (cell.getType() == BEL) {2. LabelCell labelCell = (LabelCell) cell;3. String cellContent = labelCell.getString();4. cellContent = (String) cellContent.subSequence(0, cellContent.length()-1);5. column_contents[cols] = cellContent;6.}else7.if (cell.getType() == CellType.NUMBER) {8. //number的话不用去空格就可以，我测试是这样9. NumberCell numberCell = (NumberCell) cell;10. String cellContent = numberCell.getContents();11. column_contents[cols] = cellContent;12.}else13.if (cell.getType() == CellType.DATE) {14. DateCell dateCell = (DateCell) cell;15. Date dateDemo = dateCell.getDate();16. String cellContent = dateDemo.toString();17. column_contents[cols] = 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 ermodel.HSSFDateUtil;import ermodel.HSSFWorkbook;import ermodel.Cell;import ermodel.Row;import ermodel.Sheet;import ermodel.Workbook;import ermodel.XSSFWorkbook;import com.study.entity.Emp;public class PoiExcelTest {public static void main(String[] args) {Class[] clazz=newClass[]{Integer.class,String.class,String.class,Integer.class,Date.cl ass,Double.class,Double.class,Integer.class};List<Emp> list=null;DecimalFormat df=new DecimalFormat("0.00");try {list = readExcel("d:"+File.separator+"test.xls",clazz); } catch (ParseException e) {e.printStackTrace();}for (Iterator<Emp> iter=list.iterator(); iter.hasNext();) { Emp emp=iter.next();System.out.println("Emp:"+emp.getEmpno()+"\t"+emp.getEnam e()+"\t"+df.format(emp.getSal())+"\t"+df.format(emp.getComm())+"\t"+n ewSimpleDateFormat("yyyy-MM-dd").format(emp.getHiredate())+"\t"+emp.get Deptno());}}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<Emp> readExcel(String excelFilePath,Class[] clazz) throws ParseException{List<Emp> list=new ArrayList<Emp>();Emp emp;version=(excelFilePath.endsWith(".xls")?version2003:version20 07);if(version==2003){try {InputStream stream=new FileInputStream(newFile(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, clazz[j]);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(newSimpleDateFormat("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 newSimpleDateFormat("yyyy-MM-dd").format(new Date());}else{return newSimpleDateFormat("yyyy-MM-dd").format(date);}}}return null;}}1.public class SummaryHSSF {2.3. public static void main(String[] args) throws IOException {4. //创建Workbook对象（这一个对象代表着对应的一个Excel文件）5. //HSSFWorkbook表示以xls为后缀名的文件6. Workbook wb = new HSSFWorkbook();7. //获得CreationHelper对象,这个应该是一个帮助类8. CreationHelper helper = wb.getCreationHelper();9. //创建Sheet并给名字(表示Excel的一个Sheet)10. Sheet sheet1 = wb.createSheet("HSSF_Sheet_1");11. Sheet sheet2 = wb.createSheet("HSSF_Sheet_2");12. //Row表示一行Cell表示一列13. Row row = null;14. Cell cell = null;15. for(int i=0;i<60;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(int j=0;j<25;j++){23. //设置每个sheet每一行的宽度,自动,根据需求自行确定24. sheet1.autoSizeColumn(j+1, true);25. //创建一个基本的样式26. CellStyle cellStyle = 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. }else if(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(new Double(2008.2008));49. }else if(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. }else if(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(new Date());59. }else if(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. }else{65. cellStyle = SummaryHSSF.setCellStyleAlignment(cellStyle, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER );66. cellStyle = SummaryHSSF.setFillBackgroundColors(cellStyle,IndexedColors.ORANGE.getIndex(),IndexedColors.OR ANGE.getIndex(),CellStyle.SOLID_FOREGROUND);67. cell.setCellStyle(cellStyle);68. cell.setCellValue(1);69. }70. }71. }72. //输出73. OutputStream os = new FileOutputStream(new File("c://SummaryHSSF.xls"));74. wb.write(os);75. os.close();76. }77. /**78. * 边框79. * @param wb80. * @return81. */82. public static CellStyle createStyleCell(Workbook wb){83. CellStyle cellStyle = 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. return cellStyle;95. }96. /**97. * 设置文字在单元格里面的位置98. * CellStyle.ALIGN_CENTER99. * CellStyle.VERTICAL_CENTER100. * @param cellStyle101. * @param halign102. * @param valign103. * @return104. */105. public static CellStyle setCellStyleAlignment(CellStyle cellStyle,short halign,short valign){106. //设置上下107. cellStyle.setAlignment(CellStyle.ALIGN_CENTER); 108. //设置左右109. cellStyle.setVerticalAlignment(CellStyle.VERTICAL_ CENTER);110. return cellStyle;111. }112. /**113. * 格式化单元格114. * 如#,##0.00,m/d/yy去HSSFDataFormat或XSSFDataFormat 里面找115. * @param cellStyle116. * @param fmt117. * @return118. */119. public static CellStyle setCellFormat(CreationHelper h elper,CellStyle cellStyle,String fmt){120. //还可以用其它方法创建format121. cellStyle.setDataFormat(helper.createDataFormat().getFormat(fmt));122. return cellStyle;123. }124. /**125. * 前景和背景填充的着色126. * @param cellStyle127. * @param bg IndexedColors.ORANGE.getIndex();128. * @param fg IndexedColors.ORANGE.getIndex();129. * @param fp CellStyle.SOLID_FOREGROUND130. * @return131. */132. public static CellStyle setFillBackgroundColors(CellSt yle cellStyle,short bg,short fg,short fp){133. //cellStyle.setFillBackgroundColor(bg);134. cellStyle.setFillForegroundColor(fg);135. cellStyle.setFillPattern(fp);136. return cellStyle;137. }138. /**139. * 设置字体140. * @param wb141. * @return142. */143. public static Font createFonts(Workbook wb){144. //创建Font对象145. Font font = 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. return font;155. }156.}读取Excel文件Java代码1.public class ReadExcel {2. public static void main(String[] args) throws Exception {3. InputStream is = new FileInputStream(new File("c://SummaryHSSF.xls"));4. //根据输入流创建Workbook对象5. Workbook wb = WorkbookFactory.create(is);6. //get到Sheet对象7. Sheet sheet = wb.getSheetAt(0);8. //这个必须用接口9. for(Row row : sheet){10. for(Cell cell : row){11. //cell.getCellType是获得cell里面保存的值的type12. //如Cell.CELL_TYPE_STRING13. switch(cell.getCellType()){14. case Cell.CELL_TYPE_BOOLEAN:15. //得到Boolean对象的方法16. System.out.print(cell.getBooleanCellValue()+" ");17. break;18. case Cell.CELL_TYPE_NUMERIC:19. //先看是否是日期格式20. if(DateUtil.isCellDateFormatted(cell)){21. //读取日期格式22. System.out.print(cell.getDateCellValue()+" ");23. }else{24. //读取数字25. System.out.print(cell.getNumericCellValue()+" ");26. }27. break;28. case Cell.CELL_TYPE_FORMULA:29. //读取公式30. System.out.print(cell.getCellFormula()+" ");31. break;32. case Cell.CELL_TYPE_STRING:33. //读取String34. System.out.print(cell.getRichStringCellValue().toString()+" ");35. break;36. }37. }38. System.out.println("");39. }40. }41.}还有一种传统的读法Java代码1.Sheet sheet = wb.getSheetAt(0);2. for (Iterator rit = sheet.rowIterator(); rit.hasNext(); ) {3. Row row = (Row)rit.next();4. for (Iterator cit = row.cellIterator(); cit.hasNext();) {5. Cell cell = (Cell)cit.next();6. // Do something here7. }8. }9. HSSFSheet sheet = wb.getSheetAt(0);10. for (Iterator<HSSFRow> rit = (Iterator<HSSFRow>)sheet.rowIterator(); rit.hasNext(); ) {11. HSSFRow row = rit.next();12. for (Iterator<HSSFCell> cit = (Iterator<HSSFCell>)row.cellIterator(); cit.hasNext(); ) {13. HSSFCell cell = cit.next();14. // Do something here15. }16. }1.jxl 生成报表Java代码1.package excel;2.3.import java.io.FileOutputStream;4.import java.io.OutputStream;5.import java.text.SimpleDateFormat;6.import java.util.Date;7.8.import jxl.Workbook;9.import jxl.format.Alignment;10.import jxl.format.Border;11.import jxl.format.BorderLineStyle;12.import jxl.format.CellFormat;13.import bel;14.import jxl.write.WritableCellFormat;15.import jxl.write.WritableFont;16.import jxl.write.WritableSheet;17.import jxl.write.WritableWorkbook;18./***********************************************************************19. *20. * jxlCreate.java21. * @copyright Copyright: 2009-201222. * @creator 周辉<br/>23. * @create-time Mar 9, 2010 1:35:19 PM24. * @revision $Id: *25. ***********************************************************************/26.public class jxlCreate {27.28. /**29. * @param args30. */31. public static void main(String[] args) {32. // 准备设置excel工作表的标题33. String[] title = {"编号","产品名称","产品价格","产品数量","生产日期","产地","是否出口"};34. try {35. // 获得开始时间36. long start = System.currentTimeMillis();37. // 输出的excel的路径38. String filePath = "c:\\test.xls";39. // 创建Excel工作薄40. WritableWorkbook wwb;41. // 新建立一个jxl文件,即在C盘下生成test.xls42. OutputStream os = new FileOutputStream(filePath);43. wwb=Workbook.createWorkbook(os);44. // 添加第一个工作表并设置第一个Sheet的名字45. WritableSheet sheet = wwb.createSheet("产品清单", 0);46. Label label;47. for(int i=0;i<title.length;i++){48. // Label(x,y,z)其中x代表单元格的第x+1列，第y+1行, 单元格的内容是y49. // 在Label对象的子对象中指明单元格的位置和内容50. label = new Label(i,0,title[i]);51. // 将定义好的单元格添加到工作表中52. sheet.addCell(label);53. }54. // 下面是填充数据55. /*56. * 保存数字到单元格，需要使用jxl.write.Number57. * 必须使用其完整路径，否则会出现错误58. * */59. // 填充产品编号60. jxl.write.Number number = new jxl.write.Number(0,1,20071001);61. sheet.addCell(number);62. // 填充产品名称63. label = new Label(1,1,"金鸽瓜子");64. sheet.addCell(label);65. /*66. * 定义对于显示金额的公共格式67. * jxl会自动实现四舍五入68. * 例如 2.456会被格式化为2.46,2.454会被格式化为2.4569. * */70. jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.##");71. jxl.write.WritableCellFormat wcf = new jxl.write.WritableCellFormat(nf);72. // 填充产品价格73. jxl.write.Number nb = new jxl.write.Number(2,1,2.45,wcf);74. sheet.addCell(nb);75. // 填充产品数量76. jxl.write.Number numb = new jxl.write.Number(3,1,200);77. sheet.addCell(numb);78. /*79. * 定义显示日期的公共格式80. * 如:yyyy-MM-dd hh:mm81. * */82. SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");83. String newdate = sdf.format(new Date());84. // 填充出产日期85. label = new Label(4,1,newdate);86. sheet.addCell(label);87. // 填充产地88. label = new Label(5,1,"陕西西安");89. sheet.addCell(label);90. /*91. * 显示布尔值92. * */93. jxl.write.Boolean bool = new jxl.write.Boolean(6,1,true);94. sheet.addCell(bool);95. /*96. * 合并单元格97. * 通过writablesheet.mergeCells(int x,int y,int m,int n);来实现的98. * 表示将从第x+1列，y+1行到m+1列，n+1行合并99. *100. * */101. sheet.mergeCells(0,3,2,3);102. label = new Label(0,3,"合并了三个单元格");103. sheet.addCell(label);104. /*105. *106. * 定义公共字体格式107. * 通过获取一个字体的样式来作为模板108. * 首先通过web.getSheet(0)获得第一个sheet 109. * 然后取得第一个sheet的第二列，第一行也就是"产品名称"的字体110. * */111. CellFormat cf = wwb.getSheet(0).getCell(1, 0).getCellFormat();112. WritableCellFormat wc = new WritableCellFormat ();113. // 设置居中114. wc.setAlignment(Alignment.CENTRE);115. // 设置边框线116. wc.setBorder(Border.ALL, BorderLineStyle.THIN) ;117. // 设置单元格的背景颜色118. wc.setBackground(jxl.format.Colour.RED);119. label = new Label(1,5,"字体",wc);120. sheet.addCell(label);121.122. // 设置字体123. jxl.write.WritableFont wfont = new jxl.write.W ritableFont(WritableFont.createFont("隶书"),20);124. WritableCellFormat font = new WritableCellForm at(wfont);125. label = new Label(2,6,"隶书",font);126. sheet.addCell(label);127.128. // 写入数据129. wwb.write();130. // 关闭文件131. wwb.close();132. long end = System.currentTimeMillis();133. System.out.println("----完成该操作共用的时间是:"+(end-start)/1000);134. } catch (Exception e) {135. System.out.println("---出现异常---");136. e.printStackTrace();137. }138.139. }140.141.}2.POI 生成Java代码1.package excel;2.3.import java.io.FileOutputStream;4.import java.text.SimpleDateFormat;5.import java.util.Date;6.7.import ermodel.HSSFWorkbook;8.import ermodel.Cell;9.import ermodel.CellStyle;10.import ermodel.DataFormat;11.import ermodel.Row;12.import ermodel.Sheet;13.import ermodel.Workbook;14.import org.apache.poi.ss.util.CellRangeAddress;15.16.17./***********************************************************************18. *19. * poiCreate.java20. * @copyright Copyright: 2009-201221. * @creator 周辉<br/>22. * @create-time Mar 9, 2010 2:27:52 PM23. * @revision $Id: *24. ***********************************************************************/25.public class poiCreate {26.27. /**28. * @param args29. */30. public static void main(String[] args) throws Exception {31. //创建一个EXCEL32. Workbook wb = new HSSFWorkbook();33. DataFormat format = wb.createDataFormat();34. CellStyle style;35. //创建一个SHEET36. Sheet sheet1 = wb.createSheet("产品清单");37. String[] title = {"编号","产品名称","产品价格","产品数量","生产日期","产地","是否出口"};38. int i=0;39. //创建一行40. Row row = sheet1.createRow((short)0);41. //填充标题42. for (String s:title){43. Cell cell = row.createCell(i);44. cell.setCellValue(s);45. i++;46. }47. Row row1 = sheet1.createRow((short)1);48. //下面是填充数据49. row1.createCell(0).setCellValue(20071001);50. row1.createCell(1).setCellValue("金鸽瓜子");51. //创建一个单元格子52. Cell cell2=row1.createCell(2);53. // 填充产品价格54. cell2.setCellValue(2.45);55. style = wb.createCellStyle();56. style.setDataFormat(format.getFormat("#.##"));57. //设定样式58. cell2.setCellStyle(style);59. // 填充产品数量60. row1.createCell(3).setCellValue(200);61. /*62. * 定义显示日期的公共格式63. * 如:yyyy-MM-dd hh:mm64. * */65. SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");66. String newdate = sdf.format(new Date());67. // 填充出产日期68. row1.createCell(4).setCellValue(newdate);69. row1.createCell(5).setCellValue("陕西西安");70. /*71. * 显示布尔值72. * */73. row1.createCell(6).setCellValue(true);74. /*75. * 合并单元格76. * 通过writablesheet.mergeCells(int x,int y,int m,int n);来实现的77. * 表示将first row, last row,first column,last column78. *79. * */80. Row row2 = sheet1.createRow((short) 2);81. Cell cell3 = row2.createCell((short) 0);82. cell3.setCellValue("合并了三个单元格");83. sheet1.addMergedRegion(new CellRangeAddress(2,2,0,2));84.85. FileOutputStream fileOut = new FileOutputStream("d:\\test.xls");86. wb.write(fileOut);87. fileOut.close();88.89.90. }91.92.}上面代码2中方式生成 2张报表，涉及到基本生成报表中的几种单元格类型。