POI 读写 Excel

import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.io.InputStream;import java.io.OutputStream;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.List;import org.apache.poi.openxml4j.exceptions.InvalidFormatException;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.DateUtil;import org.apache.poi.ss.usermodel.Font;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.ss.usermodel.WorkbookFactory;/** * 操作Excel表格的功能类 */public class ExcelReader {    private Workbook wb;    private Sheet sheet;    private Row row;    /**     * 读取Excel表格表头的内容     * @param InputStream     * @return String 表头内容的数组     * @throws InvalidFormatException      */    public String[] readExcelTitle(InputStream is) throws InvalidFormatException {        try {        wb = WorkbookFactory.create(is);          } catch (IOException e) {            e.printStackTrace();        }        sheet = wb.getSheetAt(0);        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(i));        }        return title;    }    /**     * 读取Excel数据内容     * @param InputStream     * @return Map 包含单元格数据内容的Map对象     * @throws InvalidFormatException      */    public List<String[]> readExcelContent(InputStream is) throws InvalidFormatException {        try {        wb = WorkbookFactory.create(is);          } catch (IOException e) {            e.printStackTrace();        }        sheet = wb.getSheetAt(0);        int rowNum = sheet.getLastRowNum();        row = sheet.getRow(0);        int colNum = row.getPhysicalNumberOfCells();        List<String[]> list = new ArrayList<String[]>();        for (int i = 1; i <= rowNum; i++) {            row = sheet.getRow(i);            String content[] = new String[colNum];            int j = 0;            while (j < colNum) {            content[j] = this.getStringCellValue(row.getCell(j));                j++;            }            list.add(content);        }        return list;    }    /**     * 获取单元格数据内容为字符串类型的数据     *      * @param cell Excel单元格     * @return String 单元格数据内容     */    private String getStringCellValue(Cell cell) {        String strCell = "";        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");        switch (cell.getCellType()) {        case Cell.CELL_TYPE_STRING:            strCell = cell.getStringCellValue().trim();            break;        case Cell.CELL_TYPE_NUMERIC:        if(DateUtil.isCellDateFormatted(cell)){          strCell = sdf.format(cell.getDateCellValue());                }else{                   strCell = String.valueOf(cell.getNumericCellValue());              }            break;        case Cell.CELL_TYPE_BOOLEAN:            strCell = String.valueOf(cell.getBooleanCellValue());            break;        case Cell.CELL_TYPE_BLANK:            strCell = "";            break;        default:            strCell = "";            break;        }        return strCell;    }        /**     * 写入内容     * @param is     * @param sheetName     * @param list     * @throws IOException      * @throws InvalidFormatException      */    public void writeExcelContent(InputStream is,String sheetName,List<String[]> list,OutputStream os) throws Exception{    wb = WorkbookFactory.create(is);    sheet= wb.getSheetAt(0);        CellStyle style = wb.createCellStyle();        style.setBorderBottom(CellStyle.BORDER_THIN);          style.setBorderLeft(CellStyle.BORDER_THIN);          style.setBorderRight(CellStyle.BORDER_THIN);          style.setBorderTop(CellStyle.BORDER_THIN);          style.setAlignment(CellStyle.ALIGN_CENTER);                Font font = wb.createFont();          font.setFontHeightInPoints((short) 9);                 style.setFont(font);              for(int i=0;i<list.size();i++){    String[] obj = list.get(i);    row = sheet.createRow(i+1);    for(int j=0;j<obj.length;j++){    Cell cell = row.createCell(j);    cell.setCellStyle(style);    cell.setCellValue(obj[j]);    }    }    wb.write(os);       os.close();      }    public static void main(String[] args) throws Exception{        try {            // 对读取Excel表格标题测试            InputStream is = new FileInputStream("d:\\test.xlsx");            ExcelReader excelReader = new ExcelReader();            String[] title = excelReader.readExcelTitle(is);            System.out.println("获得Excel表格的标题:");            for (String s : title) {                System.out.print(s + " ");            }            // 对读取Excel表格内容测试            InputStream is2 = new FileInputStream("d:\\test.xlsx");            List<String[]> list = excelReader.readExcelContent(is2);            System.out.println("获得Excel表格的内容:");            for (int i = 0; i < list.size(); i++) {            String[] s = list.get(i);            for(int j=0;j<s.length;j++){            System.out.print(s[j] + " ");            }                System.out.println("\n");            }        } catch (FileNotFoundException e) {            System.out.println("未找到指定路径的文件!");            e.printStackTrace();        }    }}

 

/** *  * @Title: export  * @Description: 导出Excel */public void export(Context context){HttpServletRequest request = context.request;HttpServletResponse response = context.response;String filePath = request.getSession().getServletContext().getRealPath("/template/detail.xlsx");OutputStream os = null;FileInputStream fis = null;try {response.reset();response.setCharacterEncoding("gb2312");String fileName = "转账明细.xlsx";String name = new String(fileName.getBytes("gb2312"), "ISO8859-1");response.setHeader( "Content-Disposition","attachment; filename=" + name);response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); fis = new FileInputStream(filePath);os = response.getOutputStream();List<String[]> list = service.findAll();ExcelReader er = new ExcelReader();er.writeExcelContent(fis, "转账明细", list, os);os.flush();response.flushBuffer();} catch (Exception e) {logger.error(e.getMessage(), e);} finally {try {if (fis != null) fis.close();if (os != null) os.close();} catch (Exception e2) {logger.error(e2.getMessage(), e2);}}}