java解析Excel(兼容2003及2007):解析2003及以下使用HSSFWorkbook类, 解析2007及以上使用XSSFWorkbook, 如果解析类与excel版本不对应,抛出相应的异常,例如HSSFWorkbook解析2007: org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF) XSSF和HSSF虽然在不同的包里,但却都实现了同一接口Workbook,可以先判断excel版本,然后由对应的excel解析类解析,指向同一Workbook变量 程序如下: /** * 需要导入的jar包 * * poi-3.8-beta3-20110606.jar * * poi-excelant-3.8-beta3-20110606.jar * * poi-examples-3.8-beta3-20110606.jar * * poi-ooxml-schemas-3.8-beta3-20110606.jar * * poi-ooxml-3.8-beta3-20110606.jar * * poi-scratchpad-3.8-beta3-20110606.jar * * xmlbeans-2.3.0.jar * * dom4j-1.6.1.jar * * 所有jar包在poi-bin-3.8-beta3-20110606.zip中,或poi-bin-3.8-20120326.zip * * * 使用3.7版本 * * poi-3.7-20101029.jar * * poi-examples-3.7-20101029.jar * * poi-ooxml-3.7-20101029.jar * * poi-ooxml-schemas-3.7-20101029.jar * * poi-scratchpad-3.7-20101029.jar * * geronimo-stax-api_1.0_spec-1.0.jar * * xmlbeans-2.3.0.jar * * dom4j-1.6.1.jar * commons-logging-1.1.jar * * 所有jar包在poi-bin-3.7-20101029.zip中, * * 下载地址:http://download.csdn.net/detail/javaloveiphone/5821279 或 http://download.csdn.net/detail/javaloveiphone/5821291 * http://archive.apache.org/dist/poi/release/bin/ 或 http://www.java2s.com/Code/Jar/p/Downloadpoiexcelant38beta320110606jar.htm */ 程序一:判断Excel版本,选择对应的excel解析类 package com.read.excel; import java.io.BufferedInputStream; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; import org.apache.poi.POIXMLDocument; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.poifs.filesystem.POIFSFileSystem; 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; public class ReadExcel { /** 错误信息 */ private String errorInfo; /** * 验证EXCEL文件是否合法 */ public boolean validateExcel(String filePath){ /**判断文件名是否为空或者文件是否存在 */ if(!CEVUtil.fileExist(filePath)){ errorInfo = "文件不存在"; return false; } /**检查文件是否是Excel格式的文件 */ if (!CEVUtil.isExcel(filePath)) { errorInfo = "文件名不是excel格式"; return false; } return true; } /** * @描述:根据文件名读取excel文件 */ public List<List<String>> read(String filePath){ List<List<String>> dataLst = new ArrayList<List<String>>(); InputStream is = null; try{ /** 验证文件是否合法 */ if (!validateExcel(filePath)){ System.out.println(errorInfo); return null; } /** 判断文件的类型,是2003还是2007 */ boolean isExcel2003 = true; if (CEVUtil.isExcel2007(filePath)){ isExcel2003 = false; } /** 调用本类提供的根据流读取的方法 */ is = new FileInputStream(new File(filePath)); Workbook wb = null; if (isExcel2003){ wb = new HSSFWorkbook(is); }else{ wb = new XSSFWorkbook(is); } is.close(); }catch (IOException e){ e.printStackTrace(); }catch (Exception ex){ ex.printStackTrace(); }finally{ if (is != null){ try{ is.close(); }catch (IOException e){ is = null; e.printStackTrace(); } } } return dataLst; } /** * @描述:读取数据 */ private List<List<String>> read(Workbook wb){ List<List<String>> dataLst = new ArrayList<List<String>>(); /**得到总的shell */ int sheetAccount = wb.getNumberOfSheets(); /** 得到第一个shell */ Sheet sheet = wb.getSheetAt(0); /** 得到Excel的行数 */ int rowCount = sheet.getPhysicalNumberOfRows(); /** 也可以通过得到最后一行数*/ int lastRowNum = sheet.getLastRowNum(); /** 循环Excel的行 */ for (int r = 0; r < rowCount; r++){ Row row = sheet.getRow(r); if (row == null){ continue; } List<String> rowLst = new ArrayList<String>(); /** 循环Excel的列 */ for (int c = 0; c < row.getPhysicalNumberOfCells(); c++){ Cell cell = row.getCell(c); String cellValue = ""; if (null != cell){ // 以下是判断数据的类型 switch (cell.getCellType()){ //XSSFCell可以达到相同的效果 case HSSFCell.CELL_TYPE_NUMERIC: // 数字 double d = cell.getNumericCellValue(); if (HSSFDateUtil.isCellDateFormatted(cell)) {//日期类型 // Date date = cell.getDateCellValue(); Date date = HSSFDateUtil.getJavaDate(d); cellValue =new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date); }else{//数值类型 cellValue = cell.getNumericCellValue()+""; } cellValue = cell.getDateCellValue() + ""; break; case HSSFCell.CELL_TYPE_STRING: // 字符串 cellValue = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean cellValue = cell.getBooleanCellValue() + ""; break; case HSSFCell.CELL_TYPE_FORMULA: // 公式 cellValue = cell.getCellFormula() + ""; break; case HSSFCell.CELL_TYPE_BLANK: // 空值 cellValue = ""; break; case HSSFCell.CELL_TYPE_ERROR: // 故障 cellValue = "非法字符"; break; default: cellValue = "未知类型"; break; } } System.out.print(cellValue +"\t"); rowLst.add(cellValue); } System.out.println(); dataLst.add(rowLst); } return dataLst; } } /** * 工具类:判断是否为Excel文件,并检查Excel版本 * @author javaloveiphone * */ class CEVUtil{ /** * 依据后缀名判断读取的是否为Excel文件 * @param filePath * @return */ public static boolean isExcel(String filePath){ if(filePath.matches("^.+\\.(?i)(xls)$")||filePath.matches("^.+\\.(?i)(xlsx)$")){ return true; } return false; } /** * 检查文件是否存在 */ public static boolean fileExist(String filePath){ if(filePath == null || filePath.trim().equals("")) return false; File file = new File(filePath); if (file == null || !file.exists()){ return false; } return true; } /** * 依据内容判断是否为excel2003及以下 */ public static boolean isExcel2003(String filePath){ try { BufferedInputStream bis = new BufferedInputStream(new FileInputStream(filePath)); if(POIFSFileSystem.hasPOIFSHeader(bis)) { System.out.println("Excel版本为excel2003及以下"); return true; } } catch (IOException e) { e.printStackTrace(); return false; } return false; } /** * 依据内容判断是否为excel2007及以上 */ public static boolean isExcel2007(String filePath){ try { BufferedInputStream bis = new BufferedInputStream(new FileInputStream(filePath)); if(POIXMLDocument.hasOOXMLHeader(bis)) { System.out.println("Excel版本为excel2007及以上"); return true; } } catch (IOException e) { e.printStackTrace(); return false; } return false; } } 程序二:无需显示判断版本,直接读取解析 package com.read.excel; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.text.SimpleDateFormat; import java.util.Date; import java.util.LinkedList; import java.util.Map; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; 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.ss.usermodel.WorkbookFactory; public class ReadExcel2 { public LinkedList<Map<String, Object>> readExcel(String excelPath) throws InvalidFormatException, FileNotFoundException, IOException{ Workbook workbook = WorkbookFactory.create(new FileInputStream(new File(excelPath))); Sheet sheet = workbook.getSheetAt(0); int startRowNum = sheet.getFirstRowNum(); int endRowNum = sheet.getLastRowNum(); for(int rowNum = startRowNum;rowNum<=endRowNum;rowNum++){ Row row = sheet.getRow(rowNum); if(row == null) continue; int startCellNum = row.getFirstCellNum(); int endCellNum = row.getLastCellNum(); for(int cellNum = startCellNum;cellNum<endCellNum;cellNum++){ Cell cell = row.getCell(cellNum); if(cell == null) continue; int type = cell.getCellType(); switch (type) { case Cell.CELL_TYPE_NUMERIC://数值、日期类型 double d = cell.getNumericCellValue(); if (HSSFDateUtil.isCellDateFormatted(cell)) {//日期类型 // Date date = cell.getDateCellValue(); Date date = HSSFDateUtil.getJavaDate(d); System.out.print(" "+new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date)+" "); }else{//数值类型 System.out.print(" "+d+" "); } break; case Cell.CELL_TYPE_BLANK://空白单元格 System.out.print(" null "); break; case Cell.CELL_TYPE_STRING://字符类型 System.out.print(" "+cell.getStringCellValue()+" "); break; case Cell.CELL_TYPE_BOOLEAN://布尔类型 System.out.println(cell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: // 故障 System.err.println("非法字符");//非法字符; break; default: System.err.println("error");//未知类型 break; } } System.out.println(); } return null; } public static void main(String[] args) { ReadExcel2 ReadExcel2 = new ReadExcel2(); try { ReadExcel2.readExcel("C:\\Users\\javaloveiphone\\Desktop\\新建 Microsoft Office Excel 工作表.xlsx"); ReadExcel2.readExcel("C:\\Users\\javaloveiphone\\Desktop\\templateyou.xls"); }catch (InvalidFormatException e) { e.printStackTrace(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } } 以上内容整理来源:http://wenku.baidu.com/view/8e5c2531a32d7375a4178080.html http://blog.csdn.net/mmm333zzz/article/details/7962377 程序三、生成导出excel文件 package com.write.excel; import java.io.FileOutputStream; import java.io.IOException; import java.util.ArrayList; import java.util.Calendar; import java.util.Date; import java.util.List; import javax.servlet.http.HttpServletResponse; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFDataFormat; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFRichTextString; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; /** * 生成excel文件 * @author javaloveiphone * */ /**记录一个异常 * java.io.FileNotFoundException: C:\Users\javaloveiphone\Desktop\example.xls (另一个程序正在使用此文件,进程无法访问。) at java.io.FileOutputStream.open(Native Method) at java.io.FileOutputStream.<init>(FileOutputStream.java:194) at java.io.FileOutputStream.<init>(FileOutputStream.java:84) at com.write.excel.WriteExcel.writeExcel(WriteExcel.java:45) at com.write.excel.WriteExcel.main(WriteExcel.java:148) */ public class WriteExcel { // 标题字体 private HSSFFont titleFont = null; // private XSSFFont titleFont = null; //2007格式 // 标题样式 private HSSFCellStyle titleStyle = null; // private XSSFCellStyle titleStyle = null;//2007格式 // 行信息内容样式 private HSSFCellStyle contentStyle = null; // private XSSFCellStyle contentStyle = null;//2007格式 /** 写excel文件 * @throws IOException */ public void writeExcel(String[] titleStrs,List<String[]> contentList,String filename) throws IOException{ FileOutputStream fileOut = new FileOutputStream("C:\\Users\\javaloveiphone\\Desktop\\example.xls"); /* * severlet响应生成excel文件 * HttpServletResponse response * * // 文件标题 * String head = new String(filename.getBytes("GB2312"), "ISO-8859-1"); * response.reset(); * response.setContentType("application/vnd.ms-excel"); * response.addHeader("Content-Disposition", "attachment; filename="+ head + ".xls"); * * HSSFWorkbook wb = new HSSFWorkbook(); * 。。。。。 * * java.io.OutputStream os = response.getOutputStream(); * wb.write(os); * os.close(); * */ HSSFWorkbook wb = new HSSFWorkbook();// 创建新HSSFWorkbook对象 // XSSFWorkbook wb = new XSSFWorkbook();//2007格式 setExcelStyle(wb);//执行样式初始化 HSSFSheet sheet = wb.createSheet(filename);// 创建新的sheet对象 // XSSFSheet sheet = wb.createSheet(filename);//2007格式 HSSFRow titleRow = sheet.createRow((short) 0);//创建第一行 // XSSFRow titleRow = sheet.createRow((short) 0);//2007格式 // titleRow.setHeight((short)300);//设置行高,设置太小可能被隐藏看不到 titleRow.setHeightInPoints(20);//20像素 int titleCount = titleStrs.length;// 列数 // 写标题 for (int k = 0; k < titleCount; k++) { HSSFCell cell = titleRow.createCell((short) k); // 新建一个单元格 // XSSFCell cell = titleRow.createCell((short) k); //2007格式 // cell.setEncoding(HSSFCell.ENCODING_UTF_16); // 中文字符集转换 cell.setCellStyle(titleStyle);//设置标题样式 // cell.setCellValue(new HSSFRichTextString(titleStrs[k])); // 为单元格赋值 // cell.setCellValue(wb.getCreationHelper().createRichTextString("")); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(titleStrs[k]); sheet.setColumnWidth((short)k, (short)5000);//设置列宽 } int contentCount = contentList.size();//总的记录数 // 写内容 for (int i = 0; i < contentCount; i++) { String [] contents = contentList.get(i); HSSFRow row = sheet.createRow((short)(i + 1)); // 新建一行 // XSSFRow row = sheet.createRow((short)(i + 1)); // //2007格式 for (int j = 0; j < titleCount; j++) { HSSFCell cell = row.createCell((short) j); // 新建一个单元格 // XSSFCell cell = row.createCell((short) j); // //2007格式 cell.setCellStyle(contentStyle);//设置内容样式 if (contents[j] == null || contents[j].equals("null")) { contents[j] = ""; } //格式化日期 if(j == 2){ HSSFCellStyle style = wb.createCellStyle(); // XSSFCellStyle style = wb.createCellStyle();//2007格式 style.setDataFormat(wb.getCreationHelper().createDataFormat().getFormat("yyyy-mm-dd hh:mm:ss")); // cell.setCellValue(new Date()); // cell.setCellValue(Calendar.getInstance()); cell.setCellValue(contents[j]); cell.setCellStyle(style); }else{ cell.setCellValue(new HSSFRichTextString(contents[j])); } } } wb.write(fileOut); fileOut.flush(); fileOut.close(); } /** 样式初始化*/ public void setExcelStyle(HSSFWorkbook workBook){ // 设置列标题字体,样式 titleFont = workBook.createFont(); titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 标题列样式 titleStyle = workBook.createCellStyle(); titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 设置边框 titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); titleStyle.setFont(titleFont); // 内容列样式 contentStyle = workBook.createCellStyle(); contentStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); contentStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); contentStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); contentStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); contentStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); contentStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); } /** 测试*/ public static void main(String[] args) { WriteExcel we = new WriteExcel(); String [] titleStrs = {"部门","城市","日期","金额"}; List<String[]> contentList = new ArrayList<String[]>(); String [] contents1 = {"财务部","北京","2013-07-25","1000.25"}; String [] contents2 = {"销售部","深圳","2013-08-01","0.099"}; String [] contents3 = {"产品部","天津","2013-11-17","18888888"}; String [] contents4 = {"市场部","上海","2013-12-10","5658978987.135454的"}; contentList.add(contents1); contentList.add(contents2); contentList.add(contents3); contentList.add(contents4); String filename = "WriteExcel"; try { we.writeExcel(titleStrs, contentList, filename); } catch (IOException e) { e.printStackTrace(); } } } http://blog.csdn.net/u011247211/article/details/12175329
java导入与导出excel,兼容excel2003以及excel2007
最新推荐文章于 2019-02-27 11:42:37 发布