jxl将list导入到Excel*下载

jxl操作excel工具类

import java.io.File;
import java.io.IOException;
import java.util.List;

import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

public class ExcelHelper{
    /**
     * 分隔符
     */
    private final static String SEPARATOR = "|";
    
    /**
     * 由List导出至指定的Sheet,带total行(最后一行)
     * @param wb   模板的workbook
     * @param sheetNum   第几个表单
     * @param targetFilePath 生成文件夹路径
     * @param l 内容list集合,以|分割的对象string集合
     * @param headInfoRows  头信息的行数
     * @param columnsLength  列数
     * @param remarkRowNumber 备注所在行
     * @param remark  备注
     * @return
     * @throws WriteException
     * @throws IOException
     * int
     */
    public static int exportExcelFromList(jxl.Workbook wb, int sheetNum,
            String targetFilePath, List<String> l, int headInfoRows,
            int columnsLength,int remarkRowNumber,String remark) throws WriteException, IOException {
        // 创建可写入的Excel工作薄对象
        WritableWorkbook wwb = null;
        int writeCount = 0;

        // 单元格样式
        // WritableFont bold = new
        // WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD);//设置字体种类和黑体显示,字体为Arial,字号大小为10,采用黑体显示
        WritableCellFormat normalFormat = new WritableCellFormat(
                NumberFormats.TEXT);
        normalFormat.setBorder(Border.ALL, BorderLineStyle.THIN,
                jxl.format.Colour.BLACK);
        
        
        //设置字体;  
        WritableFont font = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.RED);  
        WritableCellFormat normalFormat_total = new WritableCellFormat(
                font);
        normalFormat_total.setBorder(Border.ALL, BorderLineStyle.THIN,
                jxl.format.Colour.BLACK);

        try {
            
            // 创建可写入的Excel工作薄对象
            wwb = jxl.Workbook.createWorkbook(new File(targetFilePath), wb);
            WritableSheet ws = wwb.getSheet(0);
            
            Label cellRemark = new Label(0, remarkRowNumber, remark,
                    normalFormat);
            ws.addCell(cellRemark);

            int row = l.size();
            int columns = columnsLength;
            String[] ary = new String[120];
            
            for (int i = 0; i < row; i++) {
                ary = l.get(i).split("\\" + SEPARATOR);
                for (int j = 0; j < columns; j++) {
                    
                    if(i==row-1)
                    {
                        Label cell = new Label(j, i + headInfoRows, ary[j],
                                normalFormat_total);
                        ws.addCell(cell);
                    }else
                    {
                        Label cell = new Label(j, i + headInfoRows, ary[j],
                                normalFormat);
                        ws.addCell(cell);
                    }
                }
                writeCount++;
            }
            wwb.write();
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (wwb != null) {
                wwb.close();
            }

        }

        return writeCount;

    }
    
    /**
     * 导出不需要合计行
     * @param wb
     * @param sheetNum
     * @param targetFilePath
     * @param l
     * @param headInfoRows
     * @param columnsLength
     * @param remarkRowNumber
     * @param remark
     * @return
     * @throws WriteException
     * @throws IOException
     */
    public static int exportExcelFromListNoTotal(jxl.Workbook wb, int sheetNum,
            String targetFilePath, List<String> l, int headInfoRows,
            int columnsLength,int remarkRowNumber,String remark) throws WriteException, IOException {
        // 创建可写入的Excel工作薄对象
        WritableWorkbook wwb = null;
        int writeCount = 0;

        // 单元格样式
        // WritableFont bold = new
        // WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD);//设置字体种类和黑体显示,字体为Arial,字号大小为10,采用黑体显示
        WritableCellFormat normalFormat = new WritableCellFormat(
                NumberFormats.TEXT);
        normalFormat.setBorder(Border.ALL, BorderLineStyle.THIN,
                jxl.format.Colour.BLACK);
        
        
        //设置字体;  
        WritableFont font = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK);  
        WritableCellFormat normalFormat_total = new WritableCellFormat(
                font);
        normalFormat_total.setBorder(Border.ALL, BorderLineStyle.THIN,
                jxl.format.Colour.BLACK);

        try {
            
            // 创建可写入的Excel工作薄对象
            wwb = jxl.Workbook.createWorkbook(new File(targetFilePath), wb);
            WritableSheet ws = wwb.getSheet(0);
            
            Label cellRemark = new Label(0, remarkRowNumber, remark,
                    normalFormat);
            ws.addCell(cellRemark);

            int row = l.size();
            int columns = columnsLength;
            String[] ary = new String[120];
            
            for (int i = 0; i < row; i++) {
                ary = l.get(i).split("\\" + SEPARATOR);
                for (int j = 0; j < columns; j++) {
                    
                    
                        Label cell = new Label(j, i + headInfoRows, ary[j],
                                normalFormat);
                        ws.addCell(cell);
                }
                writeCount++;
            }
            wwb.write();
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (wwb != null) {
                wwb.close();
            }

        }

        return writeCount;

    }
    
    
    /**
     * 生成简单Excel(head + content)
     * @param path 导出路径
     * @param header Excel头
     * @param contentlist Excel内容
     * @return 成功返回true,失败返回false
     */
    public static boolean exportSimpleExcel(String path,String header[],List<List<String>> contentlist){
        WritableWorkbook book = null;
        try{
            book = Workbook.createWorkbook(new File(path));
            //生成名为eccif的工作表,参数0表示第一页
            WritableSheet sheet = book.createSheet("Sheet1", 0);
            //表头导航
            for(int j=0;j<header.length;j++){
                Label label = new Label(j, 0, header[j]);
                sheet.addCell(label);
            }
            /*遍历List,记录集合*/
            for(int i=0;i<contentlist.size();i++){
                List row = contentlist.get(i);
                if (row!=null){
                    /*遍历单条记录的数据列*/
                    for (int j = 0;j<row.size();j++){
                        sheet.addCell(new Label(j,i+1, (String) row.get(i)));
                    }
                }
            }
            // 写入数据并关闭文件
            book.write();
        } catch (Exception e) {
            e.printStackTrace();
            return false;  //导出失败
        }finally{
            if(book!=null){
                try {
                    book.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return true;
    }
    
}

无模板导出Excel

        /*导出路径*/
        String p = "D://测试.xls";
        /*Excel头*/
        String[] h = {"学号","学生姓名","系","班级"};
        /*Excel内容*/
        List<List<String>> list = new ArrayList<>();
        List<String> row1 = new ArrayList<>();
        row1.add("学号1");
        row1.add("姓名1");
        row1.add("系1");
        row1.add("班级1");
        list.add(row1);
        List<String> row2 = new ArrayList<>();
        row2.add("学号2");
        row2.add("姓名2");
        row2.add("系2");
        row2.add("班级2");
        list.add(row2);
        
        /*调用函数导出Excel*/
        ExcelHelper.exportSimpleExcel(p,h,list);

模板导出Excel

数据格式

exportList为List<String>,生成方式为遍历每个对象并将所有属性以|串起来

    List<DetectorHistory> dfList = service.getList();   //获取对象集合

    //生成的行数
    int operatorCount = 0;

    List<String> exportList = new ArrayList<String>();
    StringBuffer sbList = new StringBuffer();
    if (dfList!=null&&dfList.size()>0) {
        for (DetectorHistory ele:dfList) {
            sbList.delete(0, sbList.length());
         //加入|
            sbList.append("|");
            //加入属性
            ...
            exportList.add(sbList.toString());
            operatorCount++;
        }
    }

导出

        //模板所在文件夹路径
        String tempPath = req.getSession().getServletContext()
                .getRealPath(File.separator + "downLoad"
                        + File.separator + "FileTemplate");
        //生成文件所在文件夹路径
        String exportFilePath = req.getSession().getServletContext()
                .getRealPath(File.separator + "downLoad"
                        + File.separator + "ExportFile");

        //模板文件名,事先生成好以及头文件情况
        String templateFileName = "F2TotalTmp.xls";
        //生成文件名
        String exportFileName = "F2Total" + CommonTool.getNowDateStr2()    + "." + templateFileName.split("\\.")[1];

        JSONObject jsonObject = new JSONObject();
        try {
            // 文件导出
            if (exportList.size() > 0) {
                //利用模板生成Workbook
                Workbook rw = jxl.Workbook.getWorkbook(new File(tempPath + File.separator + templateFileName));

                // 写入备注行
                String remarkInfo = "tip:辅修学分上限为2分";

                // 导出文件路径
                String targetFilePath = exportFilePath + File.separator + exportFileName;
                //导出文件的文件夹不存在即创建文件
                File exportFile = new File(targetFilePath);
                if(!exportFile.exists()) {
                    if (!exportFile.getParentFile().exists()) {   //如果父文件夹不存在
                        exportFile.getParentFile().mkdirs();   //新建多层文件夹
                    }
                }
                //rw为模板workbook,0为sheetnum,其次为导出文件路径,exportList为|分割属性的string对象集合,4为头的行数,14为列数,1为备注所在行(从0开始),remarkinfo为备注所在行的信息
                operatorCount = ExcelHelper.exportExcelFromListNoTotal(rw,
                        0, targetFilePath,
                        exportList, 3, 8, 1, remarkInfo);

            }

            jsonObject.put("operatorCount", operatorCount);  //返回操作条数
            jsonObject.put("exportFilePath", File.separator + "downLoad"
                    + File.separator + "ExportFile" + File.separator + exportFileName);  //返回生成的文件路径

            if(jsonObject.get("operatorCount")!=null&&Integer.valueOf(jsonObject.get("operatorCount").toString())<=0)
            {//当生成内容条数为0时
                jsonObject.put("rtnCode", "404");
            }else
            {
                jsonObject.put("rtnCode", "0");
            }

        } catch (BiffException e) {
            e.printStackTrace();
        } catch (WriteException e) {
            e.printStackTrace();
        } finally{

        }

        resp.resetBuffer();
        resp.setContentType("text/html;charset=UTF-8");
        resp.getOutputStream().write(jsonObject.toString().getBytes("utf-8"));
        resp.getOutputStream().flush();

 

上一篇:奇偶升降序链表排序


下一篇:链表的一些Java练习题_6