（1）操作excel的几种开源框架
Apache POI
OpenXls
JEXCEL
JXLS
xlSQL
JCOM
package com.common;import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.text.DecimalFormat;import java.text.SimpleDateFormat;import java.util.Date;import java.util.Iterator;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFDataFormat;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.hssf.util.HSSFColor;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 UploadExcel { public static void main(String[] args) throws IOException { String excelFilePath = "F://项目信息表格.XLSX"; UploadExcel.readExcel(excelFilePath); //UploadExcel.writeExcel(); } //对日期格式//数字类型的格式化 public static String getFormatKey(short formatNumber, Date date) { String format = "" ; SimpleDateFormat sdf = null; if(formatNumber == HSSFDataFormat.getBuiltinFormat("h:mm")) { sdf = new SimpleDateFormat("HH:mm"); } else if(formatNumber == HSSFDataFormat.getBuiltinFormat("m/d/yy")) { sdf = new SimpleDateFormat("yyyy-MM-dd"); } else if(formatNumber == HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")) { sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); } else { sdf = new SimpleDateFormat("MM-dd"); } format = sdf.format(date); return format; } public static String getCellValue(Cell cell) { String cellValue = ""; if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { cellValue = String.valueOf(cell.getBooleanCellValue()); } else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { short format = cell.getCellStyle().getDataFormat(); //获取类型值 String printString = ""; if( format == 14 || format == 20 || format == 22 || format == 31 || format == 32 || format == 57 || format == 58) { printString = UploadExcel.getFormatKey(format, cell.getDateCellValue()); } else { DecimalFormat df = new DecimalFormat("#.#"); printString = df.format(cell.getNumericCellValue()); } cellValue = printString; } else if(cell.getCellType() == Cell.CELL_TYPE_STRING) { cellValue = cell.getStringCellValue(); } else { cellValue = cell.getStringCellValue(); } return cellValue; } //读excel public static void readExcel(String excelFilePath) throws IOException{ FileInputStream inputStream = new FileInputStream(new File(excelFilePath)); //读写xls和xlsx格式时，HSSFWorkbook针对xls，XSSFWorkbook针对xlsx Workbook workbook = null; int index = excelFilePath.lastIndexOf("."); String suffix = excelFilePath.substring(index + 1).toLowerCase(); if ("xls".equals(suffix)) { workbook = new HSSFWorkbook(inputStream); } else if("xlsx".equals(suffix)) { workbook = new XSSFWorkbook(inputStream); } else { System.err.println("错误"); } Sheet firstSheet = workbook.getSheetAt(0); int columns = firstSheet.getRow(0).getPhysicalNumberOfCells(); int rows = firstSheet.getPhysicalNumberOfRows(); for(int i = 0 ; i < rows; i++) { Row row = firstSheet.getRow(i); for(int j = 0; j < columns; j++) { Cell cell = row.getCell(j); System.out.println(UploadExcel.getCellValue(cell)); } System.out.println("###"); } /** Iterator iterator = firstSheet.iterator(); while (iterator.hasNext()) { Row nextRow = iterator.next(); int lastCell = nextRow.getLastCellNum();//一行的单元格个数，从1开始 Iterator | cellIterator = nextRow.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: { System.out.print(cell.getBooleanCellValue()); break; } case Cell.CELL_TYPE_NUMERIC: { short format = cell.getCellStyle().getDataFormat(); //获取类型值 String printString = ""; if( format == 14 || format == 20 || format == 22 || format == 31 || format == 32 || format == 57 || format == 58) { printString = UploadExcel.getFormatKey(format, cell.getDateCellValue()); } else { DecimalFormat df = new DecimalFormat("#.#"); printString = df.format(cell.getNumericCellValue()); } System.out.print(printString); break; } case Cell.CELL_TYPE_STRING: { System.out.print(cell.getStringCellValue()); break; } default : { System.out.print(cell.getStringCellValue()); } } System.out.print("|"); } System.out.println("###"); } **/ workbook.close(); inputStream.close(); } //写excel public static void writeExcel(){ try { FileOutputStream fileOut = new FileOutputStream("F://poi-test.xls"); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet worksheet = workbook.createSheet("POI Worksheet"); // index from 0,0... cell A1 is cell(0,0) HSSFRow row1 = worksheet.createRow((short) 0); HSSFCell cellA1 = row1.createCell((short) 0); cellA1.setCellValue("Hello"); HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.GOLD.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellA1.setCellStyle(cellStyle); HSSFCell cellB1 = row1.createCell((short) 1); cellB1.setCellValue("Goodbye"); cellStyle = workbook.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellB1.setCellStyle(cellStyle); HSSFCell cellC1 = row1.createCell((short) 2); cellC1.setCellValue(true); HSSFCell cellD1 = row1.createCell((short) 3); cellD1.setCellValue(new Date()); cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); cellD1.setCellStyle(cellStyle); workbook.write(fileOut); fileOut.flush(); fileOut.close(); System.out.println("读取结束"); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }} |
导出excel名称乱码转化
response.setHeader("Content-disposition", "attachment;filename=" + new String("销售管理导出订单".getBytes( "gb2312" ), "ISO8859-1" ) + ".xls"); //跨行、跨列 Integer rowRange = rowNumber + rowspanNumber - 1; sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowRange, 0,0)); sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowRange, 1,1));