java poi excel2010_java的poi技术读，写Excel[2003-2007,2010]

1 /**

2 *3 */

4 packagecom.b510.excel.util;5

6 importjava.io.File;7 importjava.io.FileInputStream;8 importjava.io.FileOutputStream;9 importjava.io.IOException;10 importjava.io.InputStream;11 importjava.io.OutputStream;12 importjava.util.ArrayList;13 importjava.util.List;14

15 importorg.apache.poi.hssf.usermodel.HSSFCell;16 importorg.apache.poi.hssf.usermodel.HSSFRichTextString;17 importorg.apache.poi.hssf.usermodel.HSSFRow;18 importorg.apache.poi.hssf.usermodel.HSSFSheet;19 importorg.apache.poi.hssf.usermodel.HSSFWorkbook;20 importorg.apache.poi.xssf.usermodel.XSSFCell;21 importorg.apache.poi.xssf.usermodel.XSSFRichTextString;22 importorg.apache.poi.xssf.usermodel.XSSFRow;23 importorg.apache.poi.xssf.usermodel.XSSFSheet;24 importorg.apache.poi.xssf.usermodel.XSSFWorkbook;25

26 importcom.b510.excel.common.Common;27 importcom.b510.excel.vo.Student;28

29 /**

30 *@authorHongten31 * @created 2014-5-2032 */

33 public classExcelUtil {34

35 public void writeExcel(List list, String path) throwsException {36 if (list == null) {37 return;38 } else if (path == null ||Common.EMPTY.equals(path)) {39 return;40 } else{41 String postfix =Util.getPostfix(path);42 if (!Common.EMPTY.equals(postfix)) {43 if(Common.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {44 writeXls(list, path);45 } else if(Common.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {46 writeXlsx(list, path);47 }48 }else{49 System.out.println(path +Common.NOT_EXCEL_FILE);50 }51 }52 }53

54 /**

55 * read the Excel file56 *@parampath the path of the Excel file57 *@return

58 *@throwsIOException59 */

60 public List readExcel(String path) throwsIOException {61 if (path == null ||Common.EMPTY.equals(path)) {62 return null;63 } else{64 String postfix =Util.getPostfix(path);65 if (!Common.EMPTY.equals(postfix)) {66 if(Common.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {67 returnreadXls(path);68 } else if(Common.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {69 returnreadXlsx(path);70 }71 } else{72 System.out.println(path +Common.NOT_EXCEL_FILE);73 }74 }75 return null;76 }77

78 /**

79 * Read the Excel 201080 *@parampath the path of the excel file81 *@return

82 *@throwsIOException83 */

84 public List readXlsx(String path) throwsIOException {85 System.out.println(Common.PROCESSING +path);86 InputStream is = newFileInputStream(path);87 XSSFWorkbook xssfWorkbook = newXSSFWorkbook(is);88 Student student = null;89 List list = new ArrayList();90 //Read the Sheet

91 for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {92 XSSFSheet xssfSheet =xssfWorkbook.getSheetAt(numSheet);93 if (xssfSheet == null) {94 continue;95 }96 //Read the Row

97 for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {98 XSSFRow xssfRow =xssfSheet.getRow(rowNum);99 if (xssfRow != null) {100 student = newStudent();101 XSSFCell no = xssfRow.getCell(0);102 XSSFCell name = xssfRow.getCell(1);103 XSSFCell age = xssfRow.getCell(2);104 XSSFCell score = xssfRow.getCell(3);105 student.setNo(getValue(no));106 student.setName(getValue(name));107 student.setAge(getValue(age));108 student.setScore(Float.valueOf(getValue(score)));109 list.add(student);110 }111 }112 }113 returnlist;114 }115

116 /**

117 * Read the Excel 2003-2007118 *@parampath the path of the Excel119 *@return

120 *@throwsIOException121 */

122 public List readXls(String path) throwsIOException {123 System.out.println(Common.PROCESSING +path);124 InputStream is = newFileInputStream(path);125 HSSFWorkbook hssfWorkbook = newHSSFWorkbook(is);126 Student student = null;127 List list = new ArrayList();128 //Read the Sheet

129 for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {130 HSSFSheet hssfSheet =hssfWorkbook.getSheetAt(numSheet);131 if (hssfSheet == null) {132 continue;133 }134 //Read the Row

135 for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {136 HSSFRow hssfRow =hssfSheet.getRow(rowNum);137 if (hssfRow != null) {138 student = newStudent();139 HSSFCell no = hssfRow.getCell(0);140 HSSFCell name = hssfRow.getCell(1);141 HSSFCell age = hssfRow.getCell(2);142 HSSFCell score = hssfRow.getCell(3);143 student.setNo(getValue(no));144 student.setName(getValue(name));145 student.setAge(getValue(age));146 student.setScore(Float.valueOf(getValue(score)));147 list.add(student);148 }149 }150 }151 returnlist;152 }153

154 @SuppressWarnings("static-access")155 privateString getValue(XSSFCell xssfRow) {156 if (xssfRow.getCellType() ==xssfRow.CELL_TYPE_BOOLEAN) {157 returnString.valueOf(xssfRow.getBooleanCellValue());158 } else if (xssfRow.getCellType() ==xssfRow.CELL_TYPE_NUMERIC) {159 returnString.valueOf(xssfRow.getNumericCellValue());160 } else{161 returnString.valueOf(xssfRow.getStringCellValue());162 }163 }164

165 @SuppressWarnings("static-access")166 privateString getValue(HSSFCell hssfCell) {167 if (hssfCell.getCellType() ==hssfCell.CELL_TYPE_BOOLEAN) {168 returnString.valueOf(hssfCell.getBooleanCellValue());169 } else if (hssfCell.getCellType() ==hssfCell.CELL_TYPE_NUMERIC) {170 returnString.valueOf(hssfCell.getNumericCellValue());171 } else{172 returnString.valueOf(hssfCell.getStringCellValue());173 }174 }175

176 public void writeXls(List list, String path) throwsException {177 if (list == null) {178 return;179 }180 int countColumnNum =list.size();181 HSSFWorkbook book = newHSSFWorkbook();182 HSSFSheet sheet = book.createSheet("studentSheet");183 //option at first row.

184 HSSFRow firstRow = sheet.createRow(0);185 HSSFCell[] firstCells = newHSSFCell[countColumnNum];186 String[] options = { "no", "name", "age", "score"};187 for (int j = 0; j < options.length; j++) {188 firstCells[j] =firstRow.createCell(j);189 firstCells[j].setCellValue(newHSSFRichTextString(options[j]));190 }191 //192 for (int i = 0; i < countColumnNum; i++) {193 HSSFRow row = sheet.createRow(i + 1);194 Student student =list.get(i);195 for (int column = 0; column < options.length; column++) {196 HSSFCell no = row.createCell(0);197 HSSFCell name = row.createCell(1);198 HSSFCell age = row.createCell(2);199 HSSFCell score = row.createCell(3);200 no.setCellValue(student.getNo());201 name.setCellValue(student.getName());202 age.setCellValue(student.getAge());203 score.setCellValue(student.getScore());204 }205 }206 File file = newFile(path);207 OutputStream os = newFileOutputStream(file);208 System.out.println(Common.WRITE_DATA +path);209 book.write(os);210 os.close();211 }212

213 public void writeXlsx(List list, String path) throwsException {214 if (list == null) {215 return;216 }217 //XSSFWorkbook

218 int countColumnNum =list.size();219 XSSFWorkbook book = newXSSFWorkbook();220 XSSFSheet sheet = book.createSheet("studentSheet");221 //option at first row.

222 XSSFRow firstRow = sheet.createRow(0);223 XSSFCell[] firstCells = newXSSFCell[countColumnNum];224 String[] options = { "no", "name", "age", "score"};225 for (int j = 0; j < options.length; j++) {226 firstCells[j] =firstRow.createCell(j);227 firstCells[j].setCellValue(newXSSFRichTextString(options[j]));228 }229 //230 for (int i = 0; i < countColumnNum; i++) {231 XSSFRow row = sheet.createRow(i + 1);232 Student student =list.get(i);233 for (int column = 0; column < options.length; column++) {234 XSSFCell no = row.createCell(0);235 XSSFCell name = row.createCell(1);236 XSSFCell age = row.createCell(2);237 XSSFCell score = row.createCell(3);238 no.setCellValue(student.getNo());239 name.setCellValue(student.getName());240 age.setCellValue(student.getAge());241 score.setCellValue(student.getScore());242 }243 }244 File file = newFile(path);245 OutputStream os = newFileOutputStream(file);246 System.out.println(Common.WRITE_DATA +path);247 book.write(os);248 os.close();249 }250 }