java 导入导出 excel

java 导入导出 excel
一、POI

private ActionForward importExcel(ActionMapping mapping, LotStepEndTimeForm theform, HttpServletRequest request,
                                      HttpServletResponse response) throws Exception {
        try {
            FormFile upFile = theform.getFormFile();
            String fileName = upFile.getFileName();
            if (StringUtils.isEmpty(fileName)) {
                throw new ValidateFailureException("Please select the Excel file to import!");
            }
            if (StringUtils.endsWithIgnoreCase(fileName, ".XLS") != true || upFile.getFileSize() < 1) {
                throw new ValidateFailureException("Only import .xls files!");
            }
            // 导入Excel
            ExcelImportHelper excelHelper = new ExcelImportHelper(fileName, 0, upFile);
            List<String[]> implist = excelHelper.getStringArrayList();
            excelHelper.closeExcel();
            int startRow = 1;
          
            List<Map<String, String>> impLotStepEndTimeList = new ArrayList<Map<String, String>>();
            for (int i = startRow; i < implist.size(); i++) {
                String[] rowData = implist.get(i);
                Map<String, String> endTimeInfo = new HashMap<>();
                boolean isNotEmptyRow = false;// 为true时，表示此行至少有一列数据，为false时，表示从此行数据全是空的
                for (int k = 0; k < colIds.length && k < rowData.length; k++) {
                    endTimeInfo.put(colIds[k], rowData[k]);
                    isNotEmptyRow = isNotEmptyRow || StringUtils.isNotEmptyTrim(rowData[k]);
                }
                if (isNotEmptyRow) {
                    impLotStepEndTimeList.add(validateImportData(endTimeInfo, theform, request));
                }
            }
            // 判断是否重复,重复数据validateFlag置为"false"
            Set<String> uniqueSet = new HashSet<String>();
            for (Map<String, String> impEndTime : impLotStepEndTimeList) {
                String uniqueKey = MapUtils.getString(impEndTime, "lotId") + MapUtils.getString(impEndTime, "stepId");
                if (uniqueSet.contains(uniqueKey)) {
                    if (MapUtils.getBooleanValue(impEndTime, "validateFlag")) {
                        impEndTime.put("validateFlag", new Boolean(false).toString());
                        impEndTime.put("validateMsg", "The data is repeated!");
                    }
                } else {
                    uniqueSet.add(uniqueKey);
                }
            }
            request.setAttribute("impLotStepEndTimesJsonStr", JSONUtils.toJSONString(impLotStepEndTimeList).replaceAll("\"", "\'"));
            request.setAttribute("impLotStepEndTimeList", impLotStepEndTimeList);
        } catch (Exception e) {
            log.error(e);
            request.setAttribute(ERROR_MSG, e.getMessage().replaceAll("\"", "\'"));
        }
        return mapping.findForward("import");
    }

 private Map validateImportData(Map<String, String> endTimeInfo, LotStepEndTimeForm theform,
                                    HttpServletRequest request) {
        HttpSession session = request.getSession(false);
        Long facilityRrn = (Long) session.getAttribute(SessionNames.FACILITY_KEY);
        StringBuffer validateMsg = new StringBuffer();
        try {
            String lotId = StringUtils.trimToUpperCase(MapUtils.getString(endTimeInfo, "lotId"));
            String stepId = StringUtils.trimToUpperCase(MapUtils.getString(endTimeInfo, "stepId"));
            String endTime = StringUtils.trim(MapUtils.getString(endTimeInfo, "endTime"));
            String comments = StringUtils.trim(MapUtils.getString(endTimeInfo, "comments"));
            if (StringUtils.isEmpty(lotId)) {
                throw new ValidateFailureException("Lot Id can not be empty ");
            }
            if (getLotRrn(facilityRrn, lotId) <= 0) {
                throw new ValidateFailureException("Lot Id " + lotId + " does not exist ");
            }
            if (StringUtils.isEmpty(stepId)) {
                throw new ValidateFailureException("Step Id can not be empty ");
            }
            if (getInstanceRrn(stepId, getNamedSpace(ObjectList.OPERATION_KEY, facilityRrn),
                                              ObjectList.OPERATION_KEY) <= 0) {
                throw new ValidateFailureException("Step Id " + stepId + " does not exist ");
            }
            if (StringUtils.isEmpty(endTime)) {
                throw new ValidateFailureException("EndTime can not be empty ");
            }
            if (!NumberUtils.isNumber(endTime)) {
                throw new ValidateFailureException("EndTime must be Number ");
            }
            endTime = new BigDecimal(endTime).setScale(2, BigDecimal.ROUND_HALF_UP).toPlainString();// 保留两位有效数字

            endTimeInfo.put("lotId", lotId);
            endTimeInfo.put("stepId", stepId);
            endTimeInfo.put("endTime", endTime);
            endTimeInfo.put("comments", comments);
        } catch (Exception e) {
            validateMsg.append(e.getMessage());
        }
        endTimeInfo.put("validateMsg", validateMsg.toString());
        endTimeInfo.put("validateFlag", new Boolean(validateMsg.length() == 0).toString());
        return endTimeInfo;
    }
    

ExcelImportHelper.java

package com.mycim.core.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
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.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
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.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.struts.upload.FormFile;

/**
 * Excel导入工具类
 * 
 */
public class ExcelImportHelper {

    private static final String XLS_TYPE        = "XLS";                             // Excel 2007 之前，后缀为.xls
    private static final String XLSX_TYPE       = "XLSX";                            // Excel 2007 之后，后缀为.xlsx

    private String              excelType       = null;                              // XLS_TYPE or XLSX_TYPE Excel类型

    private Workbook            workbook        = null;
    private Sheet               sheet           = null;
    private Row                 row             = null;
    private Cell                cell            = null;

    private int                 totalRows       = 0;                                 // 总行数
    private short               totalCells      = 0;                                 // 总列数

    private Map<String, String> stringMapData   = null;                              // 存储字符串Map
    private List<String[]>      stringArrayList = null;                              // 存储字符串数组List
    private SimpleDateFormat    fmt             = new SimpleDateFormat("yyyy-MM-dd"); // 日期格式yyyy-mm-dd

    /**
     * 读取Excel
     * 
     * @param filePath 文件路径
     * @param sheetIndex 表索引，第一个表为0
     * @throws IOException
     */
    public ExcelImportHelper(String filePath, int sheetIndex, FormFile upFile) throws IOException{
        setExcelType(filePath);

        if (XLSX_TYPE.equals(excelType)) {
            initXLSX(upFile, sheetIndex);
        } else if (XLS_TYPE.equals(excelType)) {
            initXLS(upFile, sheetIndex);
        } else {
            throw new IOException("不支持的文件格式！");
        }
    }

    private void initXLS(FormFile upFile, int sheetIndex) throws IOException {
        this.workbook = getHssfWorkbook(upFile.getInputStream());
        this.sheet = getHssfSheet(sheetIndex);

        setTotalRows(sheet);
    }

    private void initXLSX(FormFile upFile, int sheetIndex) throws IOException {
        // 获取Excel引用
        this.workbook = getXssfWorkbook(upFile.getInputStream());
        // 获取Excel引用的第几张表
        this.sheet = getXssfSheet(sheetIndex);
        // 获取总行数
        setTotalRows(sheet);
    }

    // ---------- Private Methods
    /**
     * 设置当前读取Excel文件类型
     * 
     * @param filePath 文件路径
     */
    private void setExcelType(String filePath) {
        this.excelType = "X".equals(StringUtils.substringAfter(filePath.toUpperCase(), ".XLS")) ? XLSX_TYPE : XLS_TYPE;
    }

    /**
     * 获取当前读取Excel文件类型
     * 
     * @return XLS_TYPE or XLSX_TYPE
     */
    public String getExcelType() {
        return excelType;
    }

    /**
     * 获取文件字节流
     * 
     * @param filePath 文件路径
     * @return FileInputStream
     * @throws FileNotFoundException
     */
    private FileInputStream getFile(String filePath) throws FileNotFoundException {
        return new FileInputStream(new File(filePath));
    }

    // ----- For XLS
    /**
     * XLS类型的Excel引用
     * 
     * @param file 文件字节流
     * @return HSSFWorkbook
     * @throws IOException
     */
    private HSSFWorkbook getHssfWorkbook(InputStream file) throws IOException {
        return new HSSFWorkbook(file);
    }

    /**
     * XLS类型的Excel表单
     * 
     * @param sheetIndex 表单索引，第一个表为0
     * @return HSSFSheet
     */
    private HSSFSheet getHssfSheet(int sheetIndex) {
        return (HSSFSheet) this.workbook.getSheetAt(sheetIndex);
    }

    /**
     * XLS类型的Excel表单中的行
     * 
     * @param rowNum 行号
     * @return HSSFRow
     */
    private HSSFRow getHSSFRow(int rowNum) {
        return (HSSFRow) sheet.getRow(rowNum);
    }

    /**
     * XLS类型的Excel表单中的列
     * 
     * @param rowNum 行号
     * @param cellNum 列号
     * @return HSSFCell
     */
    private HSSFCell getHSSFCell(int rowNum, int cellNum) {
        return getHSSFRow(rowNum).getCell(cellNum);
    }

    // ----- For XLSX
    /**
     * XLSX类型的Excel引用
     * 
     * @param file 文件字节流
     * @return XSSFWorkbook
     * @throws IOException
     */
    private XSSFWorkbook getXssfWorkbook(InputStream file) throws IOException {
        return new XSSFWorkbook(file);
    }

    /**
     * XLSX类型的Excel表单
     * 
     * @param sheetIndex 表单索引
     * @return XSSFSheet
     */
    private XSSFSheet getXssfSheet(int sheetIndex) {
        return (XSSFSheet) this.workbook.getSheetAt(sheetIndex);
    }

    /**
     * XLSX类型的Excel表单中的行
     * 
     * @param rowNum 行号
     * @return XSSFRow
     */
    private XSSFRow getXSSFRow(int rowNum) {
        return (XSSFRow) sheet.getRow(rowNum);
    }

    /**
     * XLSX类型的Excel表单中的列
     * 
     * @param rowNum 行号
     * @param cellNum 列号
     * @return XSSFCell
     */
    private XSSFCell getXSSFCell(int rowNum, int cellNum) {
        return getXSSFRow(rowNum).getCell(cellNum);
    }

    /**
     * 设置总行数
     * 
     * @param sheet 表单引用 HSSFSheet or XSSFSheet
     */
    private void setTotalRows(Sheet sheet) {
        this.totalRows = sheet.getLastRowNum() + 1;
    }

    /**
     * 设置某行的总列数
     * 
     * @param row 行引用 HSSFRow or XSSFRow
     */
    private void setTotalCells(Row row) {
        this.totalCells = row.getLastCellNum();
    }

    // ---------- Public Methods
    /**
     * 获取Excel引用
     * 
     * @return HSSFWorkbook or XSSFWorkbook
     */
    public Workbook getWorkbook() {
        return workbook;
    }

    /**
     * 获取Excel表单
     * 
     * @return HSSFSheet or XSSFSheet
     */
    public Sheet getSheet() {
        return sheet;
    }

    /**
     * 获取Excel的行
     * 
     * @param i 行号
     * @return HSSFRow or XSSFRow
     */
    public Row getRow(int i) {
        return XLSX_TYPE.equals(excelType) ? getXSSFRow(i) : getHSSFRow(i);
    }

    /**
     * 获取Excel的列
     * 
     * @param i 行号
     * @param j 列号
     * @return HSSFCell or XSSFCell
     */
    public Cell getCell(int i, int j) {
        return XLSX_TYPE.equals(excelType) ? getXSSFCell(i, j) : getHSSFCell(i, j);
    }

    /**
     * 检查第i行是否为空行
     * 
     * @param i 行号
     * @return boolean ? true : false
     */
    public boolean isRowNotNull(int i) {
        row = getRow(i);

        if (row != null) {
            setTotalCells(row);
            return true;
        }

        return false;
    }

    /**
     * 获取第i行第j列的单元格的值 除日期类型,其他类型全处理为文本,与表格内容一致
     * 
     * @param i 行号
     * @param j 列号
     * @return String 单元格中的数据
     */
    public String getCellStringValue(int i, int j) {

        String val = null;

        cell = getCell(i, j);

        if (cell != null) {
            if (cell.getCellTypeEnum().equals(CellType.NUMERIC) && DateUtil.isCellDateFormatted(cell)) {
                val = new SimpleDateFormat("yyyy-MM-dd").format(cell.getDateCellValue());
            } else {
                cell.setCellType(CellType.STRING);
                val = cell.toString().trim();
            }
        }
        return val == null ? "" : val.trim().toUpperCase();

    }

    /**
     * Map类型的数据集合<br>
     * 
     * @return Map<br>
     * key: R + i + C + j <br>
     * R代表行，i行号，C代表列，j列号<br>
     * e.g: map.get("R11C12")<br>
     * 第11行第12列的单元格中的值
     */
    public Map<String, String> getStringMapData() {
        stringMapData = new HashMap<>();
        for (int i = 0; i < totalRows; i++) {
            if (isRowNotNull(i)) {
                for (int j = 0; j < totalCells; j++) {
                    stringMapData.put("R" + i + "C" + j, getCellStringValue(i, j));
                }
            }
        }
        return stringMapData;
    }

    /**
     * String类型数组的List集合<br>
     * 
     * @return List<br>
     * Row,String[]: List.get(i) <br>
     * Cell,String: List.get(i)[j]<br>
     * e.g: list.get(11)[12]<br>
     * 第11行第12列的单元格中的值
     */
    public List<String[]> getStringArrayList() {
        stringArrayList = new ArrayList<>();
        for (int i = 0; i < totalRows; i++) {
            if (isRowNotNull(i)) {
                String[] strings = new String[totalCells];
                for (int j = 0; j < totalCells; j++) {
                    strings[j] = getCellStringValue(i, j);
                }
                stringArrayList.add(strings);
            }
        }
        return stringArrayList;
    }

    /**
     * 关闭流
     * 
     * @throws IOException
     */
    public void closeExcel() throws IOException {
        if (workbook != null) {
            workbook.close();
        }
    }

}

二、jxl

    private ActionForward exportExcel(ActionMapping mapping, ActionForm form, HttpServletRequest request,
                                      HttpServletResponse response) {
        String exportData = request.getParameter("exportData");
        try {
            ByteArrayOutputStream os = (ByteArrayOutputStream) createWorkBook(exportData, request);
            ServletOutputStream out = response.getOutputStream();
            response.setHeader("Content-Disposition", "attachment;filename=foupsInfo.xls");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            out.write(os.toByteArray());
            out.flush();
            out.close();
        } catch (Exception e) {
            log.error(e);
        }
        return null;
    }

   private OutputStream createWorkBook(String exportData, HttpServletRequest request) throws Exception {
        HttpSession session = request.getSession(false);
        String language = (String) session.getAttribute(SessionNames.LANGUAGE_KEY);
        JSONObject exportJsonObj = JSONObject.fromObject(exportData);
        JSONArray headers = exportJsonObj.getJSONArray("headers");
        JSONArray rows = exportJsonObj.getJSONArray("rows");
        OutputStream os = new ByteArrayOutputStream();

        jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(os);
        String title = "Foups Info";
        jxl.write.WritableSheet ws = wwb.createSheet(title, 0);

        try {
            jxl.write.WritableFont wf = new jxl.write.WritableFont(WritableFont.TAHOMA, 20, WritableFont.BOLD, true);
            jxl.write.WritableCellFormat wcfF = new jxl.write.WritableCellFormat(wf);
            wcfF.setAlignment(Alignment.CENTRE);

            ws.mergeCells(0, 0, 10, 0);
            jxl.write.Label labelTitle = new jxl.write.Label(0, 0, title, wcfF);
            ws.addCell(labelTitle);

            jxl.write.Label labelColes = null;
            jxl.write.WritableFont wfs = new jxl.write.WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD, false);
            jxl.write.WritableCellFormat wfColes = new jxl.write.WritableCellFormat(wfs);
            wfColes.setAlignment(Alignment.CENTRE);

            // labelColes = new jxl.write.Label(2, 3, "product Id:" + baseInfo.getString("productId"), wfColes);
            // ws.addCell(labelColes);

            jxl.write.Label labelCols = null;
            jxl.write.WritableCellFormat wfCols = new jxl.write.WritableCellFormat();
            wfCols.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
            wfCols.setVerticalAlignment(VerticalAlignment.CENTRE);

            int colCount = 0;
            // int colIndex = 0;
            for (Iterator iterator = headers.iterator(); iterator.hasNext();) {
                int rowCount = 5;
                JSONObject header = (JSONObject) iterator.next();
                if (!header.getBoolean("hidden")) {
                    // if (colIndex == 0) {// 第一列没有值,不显示
                    // colIndex++;
                    // continue;
                    // }
                    if (header.getInt("width") != 0) {
                        ws.setColumnView(colCount, header.getInt("width") / 6);// px-->cm
                    }
                    labelCols = new jxl.write.Label(colCount, rowCount, header.getString("colName"), wfCols);
                    ws.addCell(labelCols);
                    String preValue = null;
                    int preRowCount = rowCount;
                    for (Iterator iterator2 = rows.iterator(); iterator2.hasNext();) {
                        rowCount++;
                        JSONObject colValue = (JSONObject) iterator2.next();
                        String tempValue = null;
                        String headerId = header.getString("colId");
                        if (colValue.containsKey(headerId)) {
                            tempValue = colValue.getString(headerId);
                        }
                        String value = ((tempValue == null) || "null".equals(tempValue)) || "false".equals(tempValue) ? "" : tempValue;
                        if ("_routeId".equals(header.getString("colId"))) {// merge column name(hard code)
                            if (rowCount == 6) {
                                preRowCount = rowCount;
                                preValue = value;
                            }
                            if (!value.equals(preValue) && preValue != null) {
                                ws.mergeCells(colCount, preRowCount, colCount, rowCount - 1);
                                labelCols = new jxl.write.Label(colCount, preRowCount, preValue, wfCols);
                                ws.addCell(labelCols);
                                preRowCount = rowCount;
                                preValue = value;
                            }
                            if ((rowCount - 5) == rows.size()) {
                                ws.mergeCells(colCount, preRowCount, colCount, rowCount);
                                labelCols = new jxl.write.Label(colCount, preRowCount, value, wfCols);
                                ws.addCell(labelCols);
                            }
                        } else {
                            if (StringUtils.equals(headerId, "pollutionLevel")) {
                                if (StringUtils.isNotEmpty(value)) {
                                    value = lotService.getPollutionLevel4Show(value);
                                }
                            }
                            labelCols = new jxl.write.Label(colCount, rowCount, value, wfCols);
                            ws.addCell(labelCols);
                        }
                    }
                    colCount++;
                }
            }

            wwb.write();
        } catch (Exception d) {
            log.error(d);
        } finally {
            try {
                if (wwb != null) {
                    wwb.close();
                }
            } catch (Exception e) {
                log.error(e);
            }
        }
        return os;
    }

三、页面

  <fieldset id="fld3"><legend><mycim2:contentDisplay id="LBS_IMPORT_ENDTIME_INFO" default="Import End Time Info" /></legend>	
		  <table width="100%">	
		  <thead> 		  		
          <tr align="center" class="myinnertable">
            <td nowrap width="5%">NO</td>
            <td nowrap width="5%"><mycim2:contentDisplay id="LBL_LOT_ID" default="Lot ID" /></td>
            <%-- <td nowrap width="5%"><mycim2:contentDisplay id="LBL_ROUTE_SEQ" default="Route Seq." /></td>
            <td nowrap width="5%"><mycim2:contentDisplay id="LBL_ROUTE_ID" default="Route ID"/></td>
            <td nowrap width="5%"><mycim2:contentDisplay id="LBL_OPERATION_SEQ" default="Step Seq." /></td> --%>
            <td nowrap width="5%"><mycim2:contentDisplay id="LBL_OPERATION_ID" default="Step ID"/></td>
            <td nowrap width="5%"><mycim2:contentDisplay id="LBL_END_TIME" default="End Time"/></td>
            <td nowrap width="10%"><mycim2:contentDisplay id="LBL_COMMENTS" default="Comments"/></td>
            <td nowrap width="10%"><mycim2:contentDisplay id="LBL_INSTRUCTION_INFO" default="Instruction Info"/></td>            
          </tr>   
          <thead> 		     
          
          <tbody>
          <logic:present name="impLotStepEndTimeList">
			<logic:iterate id="item" name="impLotStepEndTimeList"  indexId="seq">		         
          	<tr align="center" class="myinnertable2 implData">		
	            <td nowrap class="myfield">
	                <mycim2:write name="item" property="NO" filter="true"/>
	                <input type='hidden' name='validateFlag' value='<mycim2:write name="item" property="validateFlag"/>'/>
	            </td> 	            
	            <td nowrap class="myfield"><mycim2:write name="item" property="lotId" filter="true"/></td> 	            
	           <%--  <td nowrap class="myfield"><mycim2:write name="item" property="routeSeq" filter="true"/></td> 	            
	            <td nowrap class="myfield"><mycim2:write name="item" property="routeId" filter="true"/></td>  
	            <td nowrap class="myfield"><mycim2:write name="item" property="stepSeq" filter="true"/></td> --%> 	            	          			
	            <td nowrap class="myfield"><mycim2:write name="item" property="stepId" filter="true"/></td>
	            <td nowrap class="myfield"><mycim2:write name="item" property="endTime" filter="true"/></td>	
	            <td width="10%" class="myfield"><mycim2:write name="item" property="comments" filter="true"/></td>	
	            <%-- <td width="15%" class="myfield"><mycim2:write name="item" property="createUser" filter="true"/></td> --%>
	            <td width="10%" class="myfield"><mycim2:write name="item" property="validateMsg" filter="true"/></td>
	            <%--  <td class="myfield" ><a href="javascript:doDelete('delete=Y&lotRrn=<mycim2:write name="item" property="lotRrn" filter="true"/>&endTimeRrn=<mycim2:write name="item" property="endTimeRrn" filter="true"/>')">
	            <img  height=16 src="<%=request.getContextPath()%>/img/delete.gif" width=16 border=0></a></td> --%>
          	</tr>	
			</logic:iterate>
			</logic:present>
			</tbody>
			</table>
			</fieldset>
            <br>
		    <table width="100%">
		    <tr>
		        <td>
			        <div align="center">
			        <input type="button" name="saveimp" value="<mycim2:contentDisplay id="LBS_SAVE" default="Save"/>" onClick="saveImp()">
			        <input type="button" name="reset" value="<mycim2:contentDisplay id="LBS_RESET" default="Reset"/>" onClick="clearImplData();">
			        <input type="button" name="back" value="<mycim2:contentDisplay id="LBS_BACK" default="Back"/>" onClick="goToURL('self', '<%=request.getContextPath()%>/lotstependtime.do?edit=1&nav=true')">
			        </div>
			    </td>
		    </tr>
	        </table>