【Java学习20170413】Excel文件读写

Excel文件读取与写入

运用POI方式实现

package com.share.read; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.List; 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 ReadExcelXlsx { public List<List<String>> readxl(String path) throws IOException { InputStream is = new FileInputStream(path); XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is); List<List<String>> result = new ArrayList<List<String>>(); /* * //Sheet xssfSheet = null; //循环每一页，并处理当前循环页 */ for (XSSFSheet xssfSheet : xssfWorkbook) {// 遍历得到当前表格页 if (xssfSheet == null) continue; // 处理当前页，循环读取每一行 for (int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) { XSSFRow xssfRow = xssfSheet.getRow(rowNum);// 获取每一行的信息 int minColIx = xssfRow.getFirstCellNum(); int maxColIx = xssfRow.getLastCellNum(); List<String> rowList = new ArrayList<String>();//存储当前行的数据 // 遍历该行，获取处理每个cell元素 for (int colIx = minColIx; colIx < maxColIx; colIx++) { XSSFCell cell = xssfRow.getCell(colIx); if (cell == null) { continue; } if (rowNum == 0 || colIx == 1) {//第一行or第二列 rowList.add(cell.toString()); continue; } rowList.add(cell.getRawValue()); } result.add(rowList); } System.out.println("结束"); is.close(); return result; } is.close(); return result; } public String[][] readxlToArray(String path) throws IOException { InputStream is = new FileInputStream(path); XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is); /* * //Sheet xssfSheet = null; //循环每一页，并处理当前循环页 */ for (XSSFSheet xssfSheet : xssfWorkbook) {// 遍历得到当前表格页 if (xssfSheet == null) continue; String[][] value = new String[xssfSheet.getLastRowNum() + 1][]; // 处理当前页，循环读取每一行 for (int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) { XSSFRow xssfRow = xssfSheet.getRow(rowNum);// 获取每一行的信息 int minColIx = xssfRow.getFirstCellNum(); int maxColIx = xssfRow.getLastCellNum(); value[rowNum] = new String[maxColIx]; // 遍历该行，获取处理每个cell元素 for (int colIx = minColIx; colIx < maxColIx; colIx++) { XSSFCell cell = xssfRow.getCell(colIx); if (cell == null) { continue; } if (rowNum == 0 || colIx == 1) { value[rowNum][colIx] = cell.toString().trim(); continue; } value[rowNum][colIx] = cell.getRawValue(); } } System.out.println("结束"); is.close(); return value; } is.close(); return null; } } package com.share.read; import java.io.IOException; import java.util.List; public class Main { public static void main(String[] args) { showExcel(); } public static void showExcel() { ReadExcelXlsx rex = new ReadExcelXlsx(); try { String[][] value = rex.readxlToArray("C:\\Users\\Administrator\\Desktop\\java阶段性考试成绩.xlsx"); for (int i = 0; i < value.length; i++) { for (int j = 0; j < value[i].length; j++) { if (i == 0) System.out.print(value[i][j] + " "); else System.out.print(value[i][j] + "\t"); } System.out.println(); } } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } public static void showExcel2() { ReadExcelXlsx rex = new ReadExcelXlsx(); try { List<List<String>> list = rex.readxl("C:\\Users\\Administrator\\Desktop\\java阶段性考试成绩.xlsx"); for (int i = 0; i < list.size(); i++) { List<String> list1 = list.get(i); for (int j = 0; j < list1.size(); j++) { String value = list1.get(j); System.out.print(value + "\t"); } System.out.println(); } } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }

方案二

package com.share.test_4_12_excel; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFFont; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class Excel { public static void main(String[] args) { // 读 Map<Integer, List<String[]>> map = readExcel("C:\\Users\\Administrator\\Desktop\\java阶段性考试成绩.xlsx"); for (int n = 0; n < map.size(); n++) { List<String[]> list = map.get(n); System.out.println("-------------------------sheet" + n + "--------------------------------"); for (int i = 0; i < list.size(); i++) { String[] arr = (String[]) list.get(i); for (int j = 0; j < arr.length; j++) { if (j == arr.length - 1) System.out.print(arr[j]); else System.out.print(arr[j] + "|"); } System.out.println(); } } // 写 writeExcel("C:\\Users\\Administrator\\Desktop\\java阶段性考试成绩-1.xlsx", map); } /** * 读数据 */ public static Map<Integer, List<String[]>> readExcel(String fileName) { Map<Integer, List<String[]>> map = new HashMap<Integer, List<String[]>>(); try { InputStream is = new FileInputStream(fileName); XSSFWorkbook workbook = new XSSFWorkbook(is); is.close(); // 获取excel中的一个表格 XSSFSheet sheet = workbook.getSheetAt(0); List<String[]> list = new ArrayList<String[]>(); // 从第二行开始读 for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) { XSSFRow row = sheet.getRow(rowIndex); if (row == null) { continue; } // String数组储存每一个单元格数据 String[] singleRow = new String[row.getLastCellNum()]; XSSFCell idCell = row.getCell(0);// 排名列 singleRow[0] = String.valueOf(idCell.getNumericCellValue()); XSSFCell nameCell = row.getCell(1);// 姓名列 singleRow[1] = nameCell.getStringCellValue(); XSSFCell xuanzeCell = row.getCell(2);// 选择列 singleRow[2] = String.valueOf(xuanzeCell.getNumericCellValue()); XSSFCell panduanCell = row.getCell(3);// 判断列 singleRow[3] = String.valueOf(panduanCell.getNumericCellValue()); XSSFCell tiankongCell = row.getCell(4);// 填空列 singleRow[4] = String.valueOf(tiankongCell.getNumericCellValue()); XSSFCell yueduCell = row.getCell(5);// 阅读列 singleRow[5] = String.valueOf(yueduCell.getNumericCellValue()); XSSFCell juanmianfenCell = row.getCell(6);// 卷面总分列 singleRow[6] = String.valueOf(juanmianfenCell.getNumericCellValue()); XSSFCell shangjifenCell = row.getCell(7);// 上机总分列 singleRow[7] = String.valueOf(shangjifenCell.getNumericCellValue()); XSSFCell zongfenCell = row.getCell(8);// 总分列 singleRow[8] = String.valueOf(zongfenCell.getNumericCellValue()); list.add(singleRow); workbook.close(); } map.put(0, list); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } return map; } /** * 写入excel */ public static void writeExcel(String fileName, Map<Integer, List<String[]>> map) { try { XSSFWorkbook wb = new XSSFWorkbook(); // 创建一个表单 XSSFSheet sheet = wb.createSheet("0"); // 改格式 // 设置行高 sheet.setDefaultRowHeightInPoints(16); XSSFCellStyle style = wb.createCellStyle(); style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直 style.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 水平 // 设置边框 style.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 下边框 style.setBorderLeft(XSSFCellStyle.BORDER_THIN);// 左边框 style.setBorderTop(XSSFCellStyle.BORDER_THIN);// 上边框 style.setBorderRight(XSSFCellStyle.BORDER_THIN);// 右边框 // 设置列宽 sheet.setColumnWidth(0, 1500); sheet.setColumnWidth(1, 2300); sheet.setColumnWidth(2, 3900); sheet.setColumnWidth(3, 3900); sheet.setColumnWidth(4, 3900); sheet.setColumnWidth(5, 3900); sheet.setColumnWidth(6, 3900); sheet.setColumnWidth(7, 3900); sheet.setColumnWidth(8, 3900); // XSSFFont curFont = wb.createFont(); curFont.setFontName("微软雅黑"); style.setFont(curFont); List<String[]> list = map.get(0); XSSFRow row = sheet.createRow(0); XSSFCell idCell = row.createCell(0);// 排名列 idCell.setCellStyle(style); idCell.setCellValue("排名"); XSSFCell nameCell = row.createCell(1);// 姓名列 nameCell.setCellValue("姓名"); nameCell.setCellStyle(style); XSSFCell xuanzeCell = row.createCell(2);// 选择列 xuanzeCell.setCellValue("选择题（20）"); xuanzeCell.setCellStyle(style); XSSFCell panduanCell = row.createCell(3);// 判断列 panduanCell.setCellValue("判断（5）"); panduanCell.setCellStyle(style); XSSFCell tiankongCell = row.createCell(4);// 填空列 tiankongCell.setCellValue("填空（15）"); tiankongCell.setCellStyle(style); XSSFCell yueduCell = row.createCell(5);// 阅读列 yueduCell.setCellValue("阅读程序20"); yueduCell.setCellStyle(style); XSSFCell juanmianfenCell = row.createCell(6);// 卷面总分列 juanmianfenCell.setCellValue("卷面总分（60）"); juanmianfenCell.setCellStyle(style); XSSFCell shangjifenCell = row.createCell(7);// 上机总分列 shangjifenCell.setCellValue("上机总分（45+10）"); shangjifenCell.setCellStyle(style); XSSFCell zongfenCell = row.createCell(8);// 总分列 zongfenCell.setCellValue("考试总分"); zongfenCell.setCellStyle(style); for (int i = 1; i < 11; i++) { // 创建行 XSSFRow row1 = sheet.createRow(i); // 获取每一行数据存入str String[] str = list.get(i + 4);// 从第六行开始 for (int j = 0; j < str.length; j++) { // 创建单元格 XSSFCell cell = row1.createCell(j); // 写入单元格 if (str[j].endsWith("0")) { int x = str[j].indexOf("."); int str1 = Integer.valueOf(str[j].substring(0, x)); cell.setCellValue(str1); // 设置格式 cell.setCellStyle(style); } else if (str[j].endsWith("5")) { double str2 = Double.valueOf(str[j]); cell.setCellValue(str2); // 设置格式 cell.setCellStyle(style); } else { cell.setCellValue(str[j]); // 设置格式 cell.setCellStyle(style); } } } FileOutputStream outputStream = new FileOutputStream(fileName); outputStream.flush(); wb.write(outputStream); outputStream.close(); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } /* * 读取数据 */ public void readExcel() { try { FileInputStream excelFileInputStream = new FileInputStream( "C:\\Users\\Administrator\\Desktop\\java阶段性考试成绩.xlsx"); XSSFWorkbook workbook = new XSSFWorkbook(excelFileInputStream); excelFileInputStream.close(); XSSFSheet sheet = workbook.getSheetAt(0); for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) { XSSFRow row = sheet.getRow(rowIndex); if (row == null) { continue; } XSSFCell idCell = row.getCell(0);// 排名列 XSSFCell nameCell = row.getCell(1);// 姓名列 XSSFCell xuanzeCell = row.getCell(2);// 选择列 XSSFCell panduanCell = row.getCell(3);// 判断列 XSSFCell tiankongCell = row.getCell(4);// 填空列 XSSFCell yueduCell = row.getCell(5);// 阅读列 XSSFCell juanmianfenCell = row.getCell(6);// 卷面总分列 XSSFCell shangjifenCell = row.getCell(7);// 上机总分列 XSSFCell zongfenCell = row.getCell(8);// 总分列 // 写入 XSSFRow newRow = sheet.createRow(rowIndex); int cellIndex = 0; XSSFCell newIdCell = newRow.createCell(cellIndex++, Cell.CELL_TYPE_NUMERIC); newIdCell.setCellValue(idCell.getNumericCellValue()); XSSFCell newNameCell = newRow.createCell(cellIndex++, Cell.CELL_TYPE_STRING); newNameCell.setCellValue(nameCell.getStringCellValue()); XSSFCell newXuanZeCell = newRow.createCell(cellIndex++, Cell.CELL_TYPE_NUMERIC); newXuanZeCell.setCellValue(xuanzeCell.getNumericCellValue()); XSSFCell newPanDuanCell = newRow.createCell(cellIndex++, Cell.CELL_TYPE_NUMERIC); newPanDuanCell.setCellValue(panduanCell.getNumericCellValue()); XSSFCell newTianKongCell = newRow.createCell(cellIndex++, Cell.CELL_TYPE_NUMERIC); newTianKongCell.setCellValue(tiankongCell.getNumericCellValue()); XSSFCell newYueDuCell = newRow.createCell(cellIndex++, Cell.CELL_TYPE_NUMERIC); newYueDuCell.setCellValue(yueduCell.getNumericCellValue()); XSSFCell newJuanMianCell = newRow.createCell(cellIndex++, Cell.CELL_TYPE_NUMERIC); newJuanMianCell.setCellValue(juanmianfenCell.getNumericCellValue()); XSSFCell newShangJiCell = newRow.createCell(cellIndex++, Cell.CELL_TYPE_NUMERIC); newShangJiCell.setCellValue(shangjifenCell.getNumericCellValue()); XSSFCell newZongFenCell = newRow.createCell(cellIndex++, Cell.CELL_TYPE_NUMERIC); newZongFenCell.setCellValue(zongfenCell.getNumericCellValue()); FileOutputStream excelFileOutPutStream = new FileOutputStream( "C:\\Users\\Administrator\\Desktop\\java阶段性考试成绩-1.xlsx"); workbook.write(excelFileOutPutStream); excelFileOutPutStream.flush(); excelFileOutPutStream.close(); // // StringBuilder employeeInfoBuilder = new StringBuilder(); // employeeInfoBuilder.append("员工信息-->").append("排名：").append(idCell.getNumericCellValue()).append("姓名：") // .append(nameCell.getStringCellValue()).append("选择题（20）：") // .append(xuanzeCell.getNumericCellValue()).append("判断（5）：") // .append(panduanCell.getNumericCellValue()).append("填空（15）：") // .append(tiankongCell.getNumericCellValue()).append("阅读程序（20）：") // .append(yueduCell.getNumericCellValue()).append("卷面总分（60）：") // .append(juanmianfenCell.getNumericCellValue()).append("上机总分（45+10）：") // .append(shangjifenCell.getNumericCellValue()).append("考试总分：") // .append(zongfenCell.getNumericCellValue()); // System.out.println(employeeInfoBuilder.toString()); workbook.close(); } } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } /** * 测试 */ /** * 写入数据 */ public void writeExcel() { FileInputStream excelFileInputStream; try { excelFileInputStream = new FileInputStream("C:\\Users\\Administrator\\Desktop\\java阶段性考试成绩.xlsx"); XSSFWorkbook workbook = new XSSFWorkbook(excelFileInputStream); excelFileInputStream.close(); XSSFSheet sheet = workbook.getSheetAt(0); int currentLastRowIndex = sheet.getLastRowNum(); int newRowIndex = currentLastRowIndex + 1; XSSFRow newRow = sheet.createRow(newRowIndex); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }