java 操作xls

来源：互联网发布：win7 端口占用编辑：程序博客网时间：2024/04/27 19:50

pom:

<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.11-beta1</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.11-beta1</version></dependency>

package com.orcl.test;import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.IOException;import java.io.OutputStream;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import org.apache.poi.hssf.usermodel.HSSFWorkbook;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 ExcelOperate {private static final String EXCEL_XLS = "xls";private static final String EXCEL_XLSX = "xlsx";public static void writeExcel(ResultSet resultInput, String finalXlsxPath) {OutputStream out = null;try {// 读取Excel文档File finalXlsxFile = new File(finalXlsxPath);Workbook workBook = getWorkbok(finalXlsxFile);// sheet 对应一个工作页Sheet sheet = workBook.getSheetAt(0);/** * 删除原有数据，除了属性列 */int rowNumber = sheet.getLastRowNum(); // 第一行从0开始算for (int i = 1; i <= rowNumber; i++) {Row row = sheet.getRow(i);sheet.removeRow(row);}// 创建文件输出流，输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效out = new FileOutputStream(finalXlsxPath);workBook.write(out);/** * 往Excel中写新数据 */ResultSetMetaData rsmd = resultInput.getMetaData();int count = rsmd.getColumnCount();List<Map<String, String>> list = new ArrayList<Map<String, String>>();while (resultInput.next()) {Map<String, String> rowData = new HashMap<String, String>();for (int i = 0; i < count; i++) {String columName = rsmd.getColumnName(i + 1);rowData.put(columName, resultInput.getString(columName));}list.add(rowData);}for (int j = 0; j < list.size(); j++) {Row row = sheet.createRow(j + 1);for (int i = 0; i < count; i++) {String columName = rsmd.getColumnName(i + 1);Cell cell = row.createCell(i);cell.setCellValue(list.get(j).get(columName));}}out = new FileOutputStream(finalXlsxPath);workBook.write(out);} catch (Exception e) {e.printStackTrace();} finally {try {if (out != null) {out.flush();out.close();}} catch (IOException e) {e.printStackTrace();}}System.out.println("数据导出成功");}/** * 判断Excel的版本,获取Workbook *  * @param in * @param filename * @return * @throws IOException */public static Workbook getWorkbok(File file) throws IOException {Workbook wb = null;FileInputStream in = new FileInputStream(file);if (file.getName().endsWith(EXCEL_XLS)) { // Excel 2003wb = new HSSFWorkbook(in);} else if (file.getName().endsWith(EXCEL_XLSX)) { // Excel 2007/2010wb = new XSSFWorkbook(in);}return wb;}}

0 0