JAVA使用POI操作excel
JAVA使用POI操作excel2007 和 excel2003
?
?日期格式和数字格式好像不好区分~~悲惨 ....
?
jar包下载：
http://mirror.bjtu.edu.cn/apache//poi/dev/bin/poi-bin-3.8-beta3-20110606.tar.gz
?
测试代码
?
?
import java.io.FileInputStream;import java.io.IOException;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFDateUtil;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.ss.usermodel.DateUtil;import org.apache.poi.ss.usermodel.Workbook;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;public class PoiTest{ /** * 说明 * * @param args * @author * @throws IOException * @date 2011-7-28 上午06:21:43 */ public static void main(String[] args) throws IOException { // read2007("aaa.xlsx"); read2003("bbb.xls"); } public static void read2003(String strPath) throws IOException { // 构造 XSSFWorkbook 对象，strPath 传入文件路径 Workbook xwb = new HSSFWorkbook(new FileInputStream(strPath)); // sheet 的个数 int sheets = xwb.getNumberOfSheets(); // 读取第一章表格内容 HSSFSheet sheet = (HSSFSheet) xwb.getSheetAt(0); // 定义 row、cell HSSFRow row; String cellStr; // 循环输出表格中的内容 for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++) { row = sheet.getRow(i); for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) { // 通过 row.getCell(j).toString() 获取单元格内容， cellStr = row.getCell(j).toString(); // System.out.print(cellStr); HSSFCell cell = row.getCell(j); switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: System.out.print(cell.getNumericCellValue()); if (HSSFDateUtil.isCellDateFormatted(cell)) { // 判断方法听说只有在2003下可以，2007判断不对头 System.out.print(DateUtil.getJavaDate(cell.getNumericCellValue())); System.out.println(cell.getDateCellValue()); } System.out.print("(NUMERIC)"); break; case HSSFCell.CELL_TYPE_STRING: System.out.print(cell.getStringCellValue()); System.out.print("(STRING)"); // value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_BLANK: System.out.print("(BLANK)"); break; default: System.out.print(row.getCell(j).toString()); System.out.print("(default)"); } System.out.print("\t"); } System.out.println(""); } } public static void read2007(String strPath) throws IOException { // 构造 XSSFWorkbook 对象，strPath 传入文件路径 Workbook xwb = new XSSFWorkbook(strPath); // sheet 的个数 int sheets = xwb.getNumberOfSheets(); // 读取第一章表格内容 XSSFSheet sheet = (XSSFSheet) xwb.getSheetAt(0); // 定义 row、cell XSSFRow row; String cellStr; // 循环输出表格中的内容 for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++) { row = sheet.getRow(i); for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) { // 通过 row.getCell(j).toString() 获取单元格内容， cellStr = row.getCell(j).toString(); // System.out.print(cellStr); XSSFCell cell = row.getCell(j); switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: System.out.print(cell.getNumericCellValue()); if (HSSFDateUtil.isCellDateFormatted(cell)) { // 判断方法听说只有在2003下可以，2007判断不对头 System.out.print(DateUtil.getJavaDate(cell.getNumericCellValue())); System.out.println(cell.getDateCellValue()); } System.out.print("(NUMERIC)"); break; case HSSFCell.CELL_TYPE_STRING: System.out.print(cell.getStringCellValue()); System.out.print("(STRING)"); // value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_BLANK: System.out.print("(BLANK)"); break; default: System.out.print(row.getCell(j).toString()); System.out.print("(default)"); } System.out.print("\t"); } System.out.println(""); } }}?
?
?
下面是兼容2003和2007的代码 ，已测试
转自：http://blog.csdn.net/jack0511/article/details/6179593
?
?
/** * ClassName:ExcelReader.java * Author: wenbin.ji * CreateTime: Jan 28, 2011 11:16:29 AM * Description:Excel数据读取工具类，POI实现，兼容Excel2003，及Excel2007 **/import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.io.InputStream;import java.util.ArrayList;import java.util.Arrays;import java.util.List;import org.apache.poi.openxml4j.exceptions.InvalidFormatException;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.DateUtil;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 ExcelReader{ Workbook wb = null; List<String[]> dataList = new ArrayList<String[]>(100); public static void main(String[] args) { ExcelReader e = new ExcelReader("aaa.xlsx"); // ExcelReader e = new ExcelReader("bbb.xls"); System.out.println(e.getRowNum(0)); List<String[]> list = e.getAllData(0); for (String[] s : list) { System.out.println(Arrays.asList(s).toString()); } } public ExcelReader(String path) { try { InputStream inp = new FileInputStream(path); wb = WorkbookFactory.create(inp); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (InvalidFormatException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } /** * 取Excel所有数据，包含header * * @return List<String[]> */ public List<String[]> getAllData(int sheetIndex) { int columnNum = 0; Sheet sheet = wb.getSheetAt(sheetIndex); if (sheet.getRow(0) != null) { columnNum = sheet.getRow(0).getLastCellNum() - sheet.getRow(0).getFirstCellNum(); } if (columnNum > 0) { for (Row row : sheet) { String[] singleRow = new String[columnNum]; int n = 0; for (int i = 0; i < columnNum; i++) { Cell cell = row.getCell(i, Row.CREATE_NULL_AS_BLANK); switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: singleRow[n] = ""; break; case Cell.CELL_TYPE_BOOLEAN: singleRow[n] = Boolean.toString(cell.getBooleanCellValue()); break; // 数值 case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { singleRow[n] = String.valueOf(cell.getDateCellValue()); } else { cell.setCellType(Cell.CELL_TYPE_STRING); String temp = cell.getStringCellValue(); // 判断是否包含小数点，如果不含小数点，则以字符串读取，如果含小数点，则转换为Double类型的字符串 if (temp.indexOf(".") > -1) { singleRow[n] = String.valueOf(new Double(temp)).trim(); } else { singleRow[n] = temp.trim(); } } break; case Cell.CELL_TYPE_STRING: singleRow[n] = cell.getStringCellValue().trim(); break; case Cell.CELL_TYPE_ERROR: singleRow[n] = ""; break; case Cell.CELL_TYPE_FORMULA: cell.setCellType(Cell.CELL_TYPE_STRING); singleRow[n] = cell.getStringCellValue(); if (singleRow[n] != null) { singleRow[n] = singleRow[n].replaceAll("#N/A", "").trim(); } break; default: singleRow[n] = ""; break; } n++; } if ("".equals(singleRow[0])) { continue; }// 如果第一行为空，跳过 dataList.add(singleRow); } } return dataList; } /** * 返回Excel最大行index值，实际行数要加1 * * @return */ public int getRowNum(int sheetIndex) { Sheet sheet = wb.getSheetAt(sheetIndex); return sheet.getLastRowNum(); } /** * 返回数据的列数 * * @return */ public int getColumnNum(int sheetIndex) { Sheet sheet = wb.getSheetAt(sheetIndex); Row row = sheet.getRow(0); if (row != null && row.getLastCellNum() > 0) { return row.getLastCellNum(); } return 0; } /** * 获取某一行数据 * * @param rowIndex * 计数从0开始，rowIndex为0代表header行 * @return */ public String[] getRowData(int sheetIndex, int rowIndex) { String[] dataArray = null; if (rowIndex > this.getColumnNum(sheetIndex)) { return dataArray; } else { dataArray = new String[this.getColumnNum(sheetIndex)]; return this.dataList.get(rowIndex); } } /** * 获取某一列数据 * * @param colIndex * @return */ public String[] getColumnData(int sheetIndex, int colIndex) { String[] dataArray = null; if (colIndex > this.getColumnNum(sheetIndex)) { return dataArray; } else { if (this.dataList != null && this.dataList.size() > 0) { dataArray = new String[this.getRowNum(sheetIndex) + 1]; int index = 0; for (String[] rowData : dataList) { if (rowData != null) { dataArray[index] = rowData[colIndex]; index++; } } } } return dataArray; }}?