java 数据导出为Excel表格

第一种：jxl.jar 读取Excel表：

public void readWorkBook() { //用来存整个表 List<List<String>> list = new ArrayList<List<String>>(); //用来存一行 List<String> rowList = null; //用来存一个格子的内容 String cellContent = null; try { //新建文件输入流 InputStream inputStream = new FileInputStream(new java.io.File("C:\\Users\\LWR\\Desktop\\工作簿.xls")); //获得读取工作簿对象 Workbook workbook = Workbook.getWorkbook(inputStream); //获取工作表 Sheet sheet = workbook.getSheet(0); //获取工作表列数 int columns = sheet.getColumns(); //获取工作表行数 int rows = sheet.getRows(); //遍历读取表格内容 for (int i = 0; i < rows; i++) { rowList = new ArrayList<String>(); for (int j = 0; j < columns; j++) { //j列i行的格子内容 cellContent = sheet.getCell(j, i).getContents(); rowList.add(cellContent); } list.add(i, rowList); } //遍历输出读取的内容 for (List<String> row : list) { for (String col : row) { System.out.print(col+"\t\t"); } System.out.println(); } } catch (FileNotFoundException e) { System.out.println("找不到读取的文件\n" + e); } catch (BiffException e) { System.out.println("读取的文件有问题\n" + e); } catch (IOException e) { System.out.println("输入流出错\n" + e); } }

写入Excel表：

public void createWorkBook(List<List<String>> list) { OutputStream outputStream = null; try { File file = new File("C:\\Users\\LWR\\Desktop\\工作簿1.xls"); if (!file.exists()) { file.createNewFile(); } outputStream = new FileOutputStream(file); WritableWorkbook writableWorkbook = Workbook.createWorkbook(outputStream); WritableSheet writableSheet = writableWorkbook.createSheet("sheet_1", 0); // WritableFont font = new WritableFont(WritableFont.TAHOMA, 9, WritableFont.BOLD);// 定义字体 // font.setColour(Colour.RED);// 字体颜色 // WritableCellFormat wc = new WritableCellFormat(font); // wc.setAlignment(Alignment.RIGHT); // 设置左对齐 // wc.setBorder(Border.ALL, BorderLineStyle.THIN); // 设置边框线 // wc.setBackground(jxl.format.Colour.YELLOW); // 设置单元格的背景颜色 for (int i = 0; i < list.size(); i++) { List<String> list2 = list.get(i); for (int j = 0; j < list2.size(); j++) { writableSheet.addCell(new Label(j, i, list2.get(j))); } } writableWorkbook.write(); writableWorkbook.close(); } catch (FileNotFoundException e) { System.out.println("找不到读取的文件\n" + e); } catch (IOException e) { System.out.println("输出流出错\n" + e); } catch (RowsExceededException e) { System.out.println("行越界"); } catch (WriteException e) { System.out.println("写入错误"); } finally { if (outputStream != null) { try { outputStream.close(); } catch (IOException e) { e.printStackTrace(); } } } }

第二种：POI 这是简单的读写，样式设置自己添加：

把数据写入Excel表格API：

public void exportExcel(List<List<String>> list) { HSSFWorkbook hssfWorkbook = new HSSFWorkbook(); HSSFSheet hssfSheet = hssfWorkbook.createSheet(); HSSFRow hssfRow = hssfSheet.createRow(0); HSSFCell hssfCell = hssfRow.createCell(0); hssfCell.setCellValue(new HSSFRichTextString("第一列")); hssfCell = hssfRow.createCell(1); hssfCell.setCellValue(new HSSFRichTextString("第二列")); hssfCell = hssfRow.createCell(2); hssfCell.setCellValue(new HSSFRichTextString("第三列")); hssfCell = hssfRow.createCell(3); hssfCell.setCellValue(new HSSFRichTextString("第四列")); for (int i = 0; i < list.size(); i++) { hssfRow = hssfSheet.createRow(i + 1); List<String> list2 = list.get(i); for (int j = 0; j < list2.size(); j++) { hssfCell = hssfRow.createCell(j); hssfCell.setCellValue(new HSSFRichTextString(list2.get(j))); } } try { File file = new File("C:\\Users\\LWR\\Desktop\\工作簿1.xls"); if (!file.exists()) { file.createNewFile(); } OutputStream outputStream = null; outputStream = new FileOutputStream(file); hssfWorkbook.write(outputStream); outputStream.close(); } catch (IOException e) { e.printStackTrace(); } }

导入Excel表格数据： 一种是用循坏一行一列取值，另一种是用迭代器：

public static void importExcel() { // 用来存整个表 List<List<String>> list = new ArrayList<List<String>>(); // 用来存一行 List<String> rowList = null; // 用来存一个格子的内容 InputStream inputStream = null; try { // 新建文件输入流 inputStream = new FileInputStream(new File("C:\\Users\\LWR\\Desktop\\工作簿.xls")); POIFSFileSystem poifsFileSystem = new POIFSFileSystem(inputStream); HSSFWorkbook hssfWorkbook = new HSSFWorkbook(poifsFileSystem); HSSFSheet sheet = hssfWorkbook.getSheetAt(0); Iterator rowIterator = sheet.rowIterator(); while (rowIterator.hasNext()) { rowList = new ArrayList<String>(); HSSFRow row = (HSSFRow) rowIterator.next(); Iterator cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { HSSFCell cell = (HSSFCell) cellIterator.next(); rowList.add(cell.getStringCellValue()); } list.add(rowList); } // // 获取行数 // int rowNum = sheet.getLastRowNum(); // // 获取一行 // HSSFRow hssfRow = sheet.getRow(1); // // 获取一个格子 // HSSFCell cell = hssfRow.getCell(0); // // 获取值 // String content = cell.getStringCellValue(); } catch (IOException e) { } // 遍历输出读取的内容 for (List<String> row : list) { for (String col : row) { System.out.print(col + "\t\t"); } System.out.println(); } } }