excel导入导出
来源：互联网 发布：java instance 编辑：程序博客网 时间：2024/06/05 05:25
package EPF.util;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.io.InputStream;import java.io.OutputStream;import java.math.BigDecimal;import java.text.DecimalFormat;import java.text.ParseException;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.HashMap;import java.util.List;import java.util.Map;import javax.faces.context.ExternalContext;import javax.faces.context.FacesContext;import javax.servlet.http.HttpServletResponse;import oracle.jbo.Row;import oracle.jbo.ViewObject;import org.apache.myfaces.trinidad.model.UploadedFile;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFDataFormatter;import org.apache.poi.hssf.usermodel.HSSFDateUtil;import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.util.HSSFColor;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFCell;import org.apache.poi.xssf.usermodel.XSSFCellStyle;import org.apache.poi.xssf.usermodel.XSSFCreationHelper;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;import org.apache.poi.hssf.usermodel.HSSFWorkbook; public class ExcelUtils { private final static String excel2003L =".xls"; //2003- 版本的excel private final static String excel2007U =".xlsx"; //2007+ 版本的excel// private POIFSFileSystem fs;// private HSSFWorkbook wb;// private HSSFSheet sheet;// private HSSFRow row; public void setFile(UploadedFile _file) { this._file = _file; } public UploadedFile getFile() { return _file; } private UploadedFile _file; public ExcelUtils() { super(); } public void exportExcelFile(FacesContext facesContext,String sheetName,String[] headerName,String[] columnName,ViewObject vo,String fileName){ @SuppressWarnings("oracle.jdeveloper.java.semantic-warning") FacesContext context = facesContext.getCurrentInstance(); ExternalContext externalContext = context.getExternalContext(); try { HttpServletResponse response = (HttpServletResponse) externalContext.getResponse(); String afileName = new String(fileName.toString().getBytes("GBK"), "ISO-8859-1"); OutputStream outputStream = response.getOutputStream(); response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-disposition", "attachment;filename = " + afileName); XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet createSheet = workbook.createSheet(sheetName); createSheet.setDefaultColumnWidth(10); createSheet.setColumnWidth(7, 12 * 256); createSheet.setColumnWidth(8, 12 * 256); createSheet.setColumnWidth(9, 12 * 256); createSheet.setColumnWidth(10, 12 * 256); XSSFFont boldFont = workbook.createFont(); boldFont.setColor(HSSFColor.BLACK.index); boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); boldFont.setFontHeightInPoints((short)10); XSSFCellStyle boldStyle = workbook.createCellStyle(); boldStyle.setFont(boldFont); boldStyle.setFillBackgroundColor(HSSFColor.CORNFLOWER_BLUE.BLACK.index); // XSSFCellStyle style = workbook.createCellStyle(); // style.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)")); // style.setFillBackgroundColor(HSSFColor.AQUA.index); // style.setFillPattern(XSSFCellStyle.BIG_SPOTS); int index = 0; XSSFRow headerRow = createSheet.createRow(index); for(int i = 0 ; i < headerName.length ; i++){ XSSFCell cell = headerRow.createCell(i); cell.setCellValue(headerName[i]); cell.setCellStyle(boldStyle); } index++; Row[] allRowsInRange = vo.getAllRowsInRange(); for(int k = 0; k < allRowsInRange.length ; k++){ XSSFRow createRow = createSheet.createRow(index); for(int j = 0; j < columnName.length ; j++){ Object columnValue = allRowsInRange[k].getAttribute(columnName[j]); XSSFCell cell = createRow.createCell(j); setConvertedCellValue(workbook,cell,columnValue); } index++; } workbook.write(outputStream); outputStream.flush(); outputStream.close(); // XSSFFont createFont = workbook.createFont(); // XSSFCellStyle cellStyle = workbook.createCellStyle(); // XSSFDataFormat format = workbook.createDataFormat(); // cellStyle.setDataFormat(format.getFormat("@")); } catch (IOException e) { e.printStackTrace(); } } private void setConvertedCellValue(XSSFWorkbook wb, XSSFCell cell, Object value) { if (value instanceof oracle.jbo.domain.Number) { oracle.jbo.domain.Number number = (oracle.jbo.domain.Number) value; cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(number.getValue()); }else if (value instanceof Double) { Double number = (Double)value; cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(number); }else if (value instanceof BigDecimal) { String number = value.toString(); cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(number); }else if (value instanceof oracle.jbo.domain.Date) { oracle.jbo.domain.Date adfdate = (oracle.jbo.domain.Date) value; SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); Date date; try { date = sdf.parse(adfdate.toString()); XSSFCellStyle cellStyle = wb.createCellStyle(); XSSFCreationHelper creationHelper = wb.getCreationHelper(); cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("m/d/yy")); //cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy")); cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyle); cell.setCellValue(date); } catch (ParseException e) { ; } }else if (value instanceof String) { String string = (String)value; cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(string); }else{ if(value != null && !"".equals(value)){ String string = value.toString(); cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(string); } } } public void import2007ExcelFile(InputStream inputStream){ } public String[] import2003ExcelFile(InputStream inputStream){ HSSFWorkbook wb = null; try { wb = new HSSFWorkbook(inputStream); } catch (IOException e) { e.printStackTrace(); } HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row = sheet.getRow(0); int colNum = row.getPhysicalNumberOfCells(); System.out.println("colNum:" + colNum); String[] title = new String[colNum]; for (int i = 0; i < colNum; i++) { //title[i] = getStringCellValue(row.getCell((short) i)); title[i] = getCellFormatValue(row.getCell((short) i)); } for (String s : title) { System.out.print(s + " "); } Map<Integer, String> map = readExcelContent(inputStream); System.out.println("获得Excel表格的内容:"); for (int i = 1; i <= map.size(); i++) { System.out.println(map.get(i)); } return title; } /** * 读取Excel数据内容 * @param InputStream * @return Map 包含单元格数据内容的Map对象 */ public Map<Integer, String> readExcelContent(InputStream is) { Map<Integer, String> content = new HashMap<Integer, String>(); String str = ""; HSSFWorkbook wb = null; try { //fs = new POIFSFileSystem(is); wb = new HSSFWorkbook(is); } catch (IOException e) { e.printStackTrace(); } HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row = sheet.getRow(0); // 得到总行数 int rowNum = sheet.getLastRowNum(); int colNum = row.getPhysicalNumberOfCells(); // 正文内容应该从第二行开始,第一行为表头的标题 for (int i = 1; i <= rowNum; i++) { row = sheet.getRow(i); int j = 0; while (j < colNum) { // 每个单元格的数据内容用"-"分割开，以后需要时用String类的replace()方法还原数据 // 也可以将每个单元格的数据设置到一个javabean的属性中，此时需要新建一个javabean // str += getStringCellValue(row.getCell((short) j)).trim() + // "-"; str += getCellFormatValue(row.getCell((short) j)).trim() + " "; j++; } content.put(i, str); str = ""; } return content; } /** * 获取单元格数据内容为字符串类型的数据 * * @param cell Excel单元格 * @return String 单元格数据内容 */ private String getStringCellValue(HSSFCell cell) { String strCell = ""; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: strCell = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_NUMERIC: strCell = String.valueOf(cell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_BOOLEAN: strCell = String.valueOf(cell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_BLANK: strCell = ""; break; default: strCell = ""; break; } if (strCell.equals("") || strCell == null) { return ""; } if (cell == null) { return ""; } return strCell; } /** * 根据HSSFCell类型设置数据 * @param cell * @return */ private String getCellFormatValue(HSSFCell cell) { String cellvalue = ""; if (cell != null) { // 判断当前Cell的Type switch (cell.getCellType()) { // 如果当前Cell的Type为NUMERIC case HSSFCell.CELL_TYPE_NUMERIC: case HSSFCell.CELL_TYPE_FORMULA: { // 判断当前的cell是否为Date if (HSSFDateUtil.isCellDateFormatted(cell)) { // 如果是Date类型则，转化为Data格式 //方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00 //cellvalue = cell.getDateCellValue().toLocaleString(); //方法2：这样子的data格式是不带带时分秒的：2011-10-12 Date date = cell.getDateCellValue(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); cellvalue = sdf.format(date); } // 如果是纯数字 else { // 取得当前Cell的数值 cellvalue = String.valueOf(cell.getNumericCellValue()); } break; } // 如果当前Cell的Type为STRIN case HSSFCell.CELL_TYPE_STRING: // 取得当前的Cell字符串 cellvalue = cell.getRichStringCellValue().getString(); break; // 默认的Cell值 default: cellvalue = " "; } } else { cellvalue = ""; } return cellvalue; } public void importFile(){ UploadedFile file = getFile(); if(file == null){ JSFUtils.addFacesErrorMessage("请选择上传文件!"); } try { InputStream inputStream = file.getInputStream(); String filename = file.getFilename(); ExcelUtils eu = new ExcelUtils(); if(filename.endsWith("xls")){//03 eu.import2003ExcelFile(inputStream); }else if(filename.endsWith("xlsx")){//07 eu.import2007ExcelFile(inputStream); } } catch (IOException e) { e.printStackTrace(); } } public static void main(String[] args) { try { // 对读取Excel表格标题测试 InputStream is = new FileInputStream("E:\\123.xls"); System.out.println("IS: " + is); ExcelUtils excelReader = new ExcelUtils();// excelReader.import2003ExcelFile(is); List<List<Object>> listob = excelReader.getBankListByExcel(is, "123.xls"); System.out.println("size:" + listob.size()); for (int i = 0; i < listob.size(); i++) { List<Object> lo = listob.get(i); System.out.println("ID:" + lo.get(0)); System.out.println("父ID:" + lo.get(1)); System.out.println("状态:" + lo.get(2)); System.out.println("标题:" + lo.get(3)); System.out.println("责任部门:" + lo.get(4)); System.out.println("负责人:" + lo.get(5)); System.out.println("提出人:" + lo.get(6)); System.out.println("提出日期:" + lo.get(7)); System.out.println("开始日期:" + lo.get(8)); System.out.println("计划结束:" + lo.get(9)); System.out.println("更新日期:" + lo.get(10)); System.out.println("过程数:" + lo.get(11)); System.out.println("附件数:" + lo.get(12)); System.out.println("排序:" + lo.get(13)); } } catch (FileNotFoundException e) { System.out.println("未找到指定路径的文件!"); e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } } /** * 描述：根据文件后缀，自适应上传文件的版本 * @param inStr,fileName * @return * @throws Exception */ public Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{ Workbook wb = null; String fileType = fileName.substring(fileName.lastIndexOf(".")); if(excel2003L.equals(fileType)){ wb = new HSSFWorkbook(inStr); //2003- }else if(excel2007U.equals(fileType)){ wb = new XSSFWorkbook(inStr); //2007+ }else{ throw new Exception("解析的文件格式有误！"); } return wb; } /** * * @param in,fileName * @return * @throws IOException */ public List<List<Object>> getBankListByExcel(InputStream in,String fileName) throws Exception{ List<List<Object>> list = null; //创建Excel工作薄 Workbook work = this.getWorkbook(in,fileName); if(null == work){ throw new Exception("创建Excel工作薄为空！"); } Sheet sheet = null; org.apache.poi.ss.usermodel.Row row = null; Cell cell = null; list = new ArrayList<List<Object>>(); //遍历Excel中所有的sheet for (int i = 0; i < work.getNumberOfSheets(); i++) { sheet = work.getSheetAt(i); if(sheet==null){continue;} //遍历当前sheet中的所有行 for (int j = sheet.getFirstRowNum(); j < sheet.getLastRowNum() + 1; j++) { row = sheet.getRow(j); if(row==null||row.getFirstCellNum()==j){continue;} //遍历所有的列 List<Object> li = new ArrayList<Object>(); for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) { cell = row.getCell(y); li.add(this.getCellValue(cell)); } list.add(li); } }// in.close();// work.close(); return list; } /** * 描述：对表格中数值进行格式化 * @param cell * @return */ public Object getCellValue(Cell cell){ Object value = null; // DecimalFormat df = new DecimalFormat("0"); //格式化number String字符 SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); //日期格式化 // DecimalFormat df2 = new DecimalFormat("0.00"); //格式化数字 switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: value = cell.getRichStringCellValue().getString(); break; case Cell.CELL_TYPE_NUMERIC: if("General".equals(cell.getCellStyle().getDataFormatString())){ HSSFDataFormatter dataFormatter = new HSSFDataFormatter(); value = dataFormatter.formatCellValue(cell);//格式化数字 //value = (int)cell.getNumericCellValue(); }else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){ value = sdf.format(cell.getDateCellValue()); }else{ value = cell.getNumericCellValue(); System.out.println("123"); } break; case Cell.CELL_TYPE_BOOLEAN: value = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_BLANK: value = ""; break; default: break; } return value; } } 0 0
- 导入/导出Excel
- 导入/导出Excel
- 导入/导出Excel
- 导入/导出Excel
- EXCEL导入导出
- Excel导入＆导出
- 数据库导入导出Excel
- 导入/导出Excel
- 导入/导出Excel
- 导入/导出Excel
- 导入/导出Excel
- 导入/导出Excel
- excel导入、导出数据
- 导入/导出Excel
- 导入/导出Excel
- 导入/导出Excel
- 导入/导出Excel
- 导入/导出Excel
- C#ftp上传下载
- java 当前时间减去7天
- Qt项目中的常见用法
- Centos6.5/6.8 64位缺少防火墙配置文件及内部的相应配置
- LInux的启动过程
- excel导入导出
- 什么是java 序列化，如何实现java 序列化？
- 2016秋招笔试面试题一：Java及基础部分
- 161230工作笔记之设计模式
- 面试题 HashMap 数据结构 实现原理
- 如何在代码中设置textView跑马灯效果（不是在xml中）
- 线段树区间修改与查询（求和）
- 39.4 Spring Boot Shiro权限管理【从零开始学Spring Boot】
- mysql 子查询总结
