Exce模板导入

Exce模板导入

import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.FileInputStream;
import java.net.URLDecoder;
import java.util.List;

public class ExportExcelUtil {
    public File getExcelDemoFile(String fileDir)
            throws Exception {
        String classDir = null;
        classDir = Thread.currentThread().getContextClassLoader().getResource("").getPath();
        String classDir0 = URLDecoder.decode(classDir, "utf-8");
        //System.out.println(classDir0+ "../../" + fileDir);
        File file = new File(classDir0+ "../../" + fileDir);
        if (!file.exists()) {
            throw new Exception("模板文件不存在！");
        }
        return file;
    }

    public Workbook writeNewExcel(File file, String sheetName, List<String[]> lis)
            throws Exception {
        Workbook wb;
        Row row;
        Cell cell;
        FileInputStream fis = new FileInputStream(file);
        wb = new ImportExcelUtil().getWorkbook(fis, file.getName());
        Sheet sheet = wb.getSheet(sheetName);

        int lastRow = sheet.getLastRowNum();
        CellStyle cs = setSimpleCellStyle(wb);
        for (int i = 0; i < lis.size(); i++) {
            row = sheet.createRow(lastRow + i);
            String[] datas = lis.get(i);
            if (null == datas) {
                break;
            }
            for(int j = 0; j < datas.length; j++)
            {
                cell = row.createCell(j);
                cell.setCellValue(datas[j]);
                cell.setCellStyle(cs);
            }
        }
        return wb;
    }

    public Workbook writeNewExcel(File file, String sheetName, List<String[]> lis,int firstRow)
            throws Exception {
        Workbook wb;
        Row row;
        Cell cell;
        FileInputStream fis = new FileInputStream(file);
        wb = new ImportExcelUtil().getWorkbook(fis, file.getName());
        Sheet sheet = wb.getSheet(sheetName);
        int beginRows = sheet.getFirstRowNum();  
        int endRows = sheet.getLastRowNum();
        //在指定行写入，指定行后的数据清除
        for (int i = beginRows; i <= endRows; i++) {
            if(i >= firstRow){
                row = sheet.getRow(i);
                sheet.removeRow(row);
            }
        }
        int lastRow = firstRow;
        CellStyle cs = setSimpleCellStyle(wb);
        for (int i = 0; i < lis.size(); i++) {
            row = sheet.createRow(lastRow + i -1);
            String[] datas = lis.get(i);
            if (null == datas) {
                break;
            }
            for(int j = 0; j < datas.length; j++)
            {
                cell = row.createCell(j);
                cell.setCellValue(datas[j]);
                cell.setCellStyle(cs);
            }
        }
        return wb;
    }

    public Workbook writeNewExcel(File file, String sheetName, List<String[]> lis,int firstRow,int lie)
            throws Exception {
        Workbook wb;
        Row row;
        Cell cell;
        FileInputStream fis = new FileInputStream(file);
        wb = new ImportExcelUtil().getWorkbook(fis, file.getName());
        Sheet sheet = wb.getSheet(sheetName);

        int lastRow = firstRow;
        CellStyle cs = setSimpleCellStyle(wb);
        for (int i = 0; i < lis.size(); i++) {
            row = sheet.createRow(lastRow + i -1);
            String[] datas = lis.get(i);
            if (null == datas) {
                break;
            }
            for(int j = lie-1; j < datas.length+lie-1; j++)
            {
                cell = row.createCell(j);
                cell.setCellValue(datas[j-lie+1]);
                cell.setCellStyle(cs);
            }
        }
        return wb;
    }


    public Boolean writeToExcel(Workbook wb, String sheetName, List<String[]> lis,int firstRow)
            throws Exception {
        Row row;
        Cell cell;
        Sheet sheet = wb.getSheet(sheetName);

        int lastRow = firstRow;
        CellStyle cs = setSimpleCellStyle(wb);
        for (int i = 0; i < lis.size(); i++) {
            row = sheet.createRow(lastRow + i -1);
            String[] datas = lis.get(i);
            if (null == datas) {
                break;
            }
            for(int j = 0; j < datas.length; j++)
            {
                cell = row.createCell(j);
                cell.setCellValue(datas[j]);
                cell.setCellStyle(cs);
            }
        }
        return true;
    }

    public Boolean writeToExcel(Workbook wb, String sheetName, List<String[]> lis,int firstRow,int lie)
            throws Exception {
        Row row;
        Cell cell;
        Sheet sheet = wb.getSheet(sheetName);

        int lastRow = firstRow;
        CellStyle cs = setSimpleCellStyle(wb);
        for (int i = 0; i < lis.size(); i++) {
            row = sheet.createRow(lastRow + i -1);
            String[] datas = lis.get(i);
            if (null == datas) {
                break;
            }
            for(int j = lie-1; j < datas.length +lie-1; j++)
            {
                cell = row.createCell(j);
                cell.setCellValue(datas[j-lie+1]);
                cell.setCellStyle(cs);
            }
        }
        return true;
    }


    public Boolean writeToExcelTitle(Workbook wb, String sheetName, List<String[]> lis,int firstRow,int lie,int fontSize)
            throws Exception {
        Row row;
        Cell cell;
        Sheet sheet = wb.getSheet(sheetName);

        int lastRow = firstRow;
        CellStyle cs = setTitleStyle(wb,fontSize);
        for (int i = 0; i < lis.size(); i++) {
            row = sheet.createRow(lastRow + i -1);
            String[] datas = lis.get(i);
            if (null == datas) {
                break;
            }
            for(int j = lie-1; j < datas.length +lie-1; j++)
            {
                cell = row.createCell(j);
                cell.setCellValue(datas[j-lie+1]);
                cell.setCellStyle(cs);
            }
        }
        return true;
    }


    public CellStyle setSimpleCellStyle(Workbook wb) {
        CellStyle cs = wb.createCellStyle();
        cs.setFillBackgroundColor((short) 13);
        cs.setBorderBottom(BorderStyle.THIN);
        cs.setBorderLeft(BorderStyle.THIN);
        cs.setBorderTop(BorderStyle.THIN);
        cs.setBorderRight(BorderStyle.THIN);
        cs.setAlignment(HorizontalAlignment.CENTER);
        return cs;
    }


    @SuppressWarnings("deprecation")
    public CellStyle setTitleStyle(Workbook wb,int fontSize) {
        CellStyle cs = wb.createCellStyle();
        Font ztFont = wb.createFont();    
        ztFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        ztFont.setFontHeightInPoints((short)fontSize); 
        cs.setFont(ztFont);
        cs.setFillBackgroundColor((short) 13);
    /*  cs.setBorderBottom(BorderStyle.THIN);
        cs.setBorderLeft(BorderStyle.THIN);
        cs.setBorderTop(BorderStyle.THIN);*/
        cs.setBorderRight(BorderStyle.DASHED);
        cs.setAlignment(HorizontalAlignment.CENTER);
        return cs;
    }
}