Jxl导出excel开发实例

package chances.quote.service;


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


import chances.quote.entity.HardwareHistory;
import chances.quote.entity.SoftwareHistory;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;


public class WriteExcelService {
    public void exportExcel(String path,List<HardwareHistory> list1,List<SoftwareHistory> list2,Map<String,String> result){
        WritableWorkbook book = null;
        //货币格式
        NumberFormat nb = new NumberFormat("￥#,##0.00;￥-#,##0.00");
        //数字带小数点
        NumberFormat nb_number = new NumberFormat("#,##0");
        WritableFont wf = new WritableFont(WritableFont.createFont("宋体"),10,WritableFont.NO_BOLD,false);
        WritableFont title = new WritableFont(WritableFont.createFont("宋体"),10,WritableFont.BOLD,false);
        WritableCellFormat wcf_number = new WritableCellFormat(nb);
        WritableCellFormat wcf_nb = new WritableCellFormat(nb_number);
        WritableCellFormat  wcf = new WritableCellFormat(wf);
        WritableCellFormat  wcf_title = new WritableCellFormat(title);
        try {
            wcf.setWrap(true);//单元格自动换行
            wcf.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
            wcf_title.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
            wcf_nb.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
            wcf_number.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
        } catch (WriteException e1) {
            e1.printStackTrace();
        }
        System.out.println(path);
        double hardware_total_price=0;
        double software_cost_total=0;
        double software_profit_total=0;
        double software_total_price=0;
        String info1[] = {"设备名称","设备类型","功能描述","配置描述","单价","数量","总价"};
        String info2[] = {"模块名称","所属系统","功能描述","PD时间","QA时间","PM时间","PD单价","QA单价","PM单价","成本总价","利润率","利润总计","总价"};
        try{
            book = Workbook.createWorkbook(new File(path));
            //生成工作表，参数0表示第一页
            WritableSheet sheet1 = book.createSheet("硬件报价表", 0);
            WritableSheet sheet2 = book.createSheet("软件报价表", 1);
            sheet1.setColumnView(0, 20);//设置列宽
            sheet1.setColumnView(6, 20);
            sheet1.setColumnView(1, 20);
            sheet1.setColumnView(2, 30);
            sheet1.setColumnView(3, 50);
            sheet1.setColumnView(4, 15);
            sheet2.setColumnView(0, 20);
            sheet2.setColumnView(1, 20);
            sheet2.setColumnView(2, 20);
            sheet2.setColumnView(6, 13);
            sheet2.setColumnView(7, 13);
            sheet2.setColumnView(8, 13);
            sheet2.setColumnView(9, 15);
            sheet2.setColumnView(11, 15);
            sheet2.setColumnView(12, 15);
            //表头导航
            for(int j=0;j<7;j++){
                Label label = new Label(j, 0, info1[j],wcf_title);
                sheet1.addCell(label);
            }
            for(int i=0;i<list1.size();i++){
                sheet1.setRowView(i+1, 750);//设置行高
                sheet1.addCell(new Label(0,i+1,list1.get(i).getDeviceName(),wcf));
                sheet1.addCell(new Label(1,i+1,result.get(list1.get(i).getDeviceType().toString()),wcf));
                sheet1.addCell(new Label(2,i+1,list1.get(i).getDescription(),wcf));
                sheet1.addCell(new Label(3,i+1,list1.get(i).getDeployDescription(),wcf));
                sheet1.addCell(new Number(4, i+1, list1.get(i).getUnitPrice(), wcf_number));
                sheet1.addCell(new Number(5, i+1, list1.get(i).getNumber(), wcf_nb));
                sheet1.addCell(new Number(6, i+1, list1.get(i).getTotalPrice(), wcf_number));
                hardware_total_price += list1.get(i).getTotalPrice();
            }
            sheet1.addCell(new Label(5, list1.size()+2, "总价",wcf_title));
            sheet1.addCell(new Number(6, list1.size()+2, hardware_total_price, wcf_number));
            for(int j=0;j<13;j++){
                Label label = new Label(j, 0, info2[j],wcf_title);
                sheet2.addCell(label);
            }
            for(int i=0;i<list2.size();i++){
                sheet2.setRowView(i+1, 750);
                sheet2.addCell(new Label(0,i+1,list2.get(i).getModularName(),wcf));
                sheet2.addCell(new Label(1,i+1,list2.get(i).getSystemName(),wcf));
                sheet2.addCell(new Label(2,i+1,list2.get(i).getDescription(),wcf));
                sheet2.addCell(new Number(3, i+1, list2.get(i).getPdTime(), wcf_nb));
                sheet2.addCell(new Number(4, i+1, list2.get(i).getQaTime(), wcf_nb));
                sheet2.addCell(new Number(5,i+1,list2.get(i).getPmTime(),wcf_nb));
                sheet2.addCell(new Number(6,i+1,list2.get(i).getPdPrice(),wcf_number));
                sheet2.addCell(new Number(7,i+1,list2.get(i).getQaPrice(),wcf_number));
                sheet2.addCell(new Number(8,i+1,list2.get(i).getPmPrice(),wcf_number));
                sheet2.addCell(new Number(9,i+1,list2.get(i).getCostTotal(),wcf_number));
                sheet2.addCell(new Number(10,i+1,list2.get(i).getProfitMargin(),wcf_nb));
                sheet2.addCell(new Number(11,i+1,list2.get(i).getProfitTotal(),wcf_number));
                sheet2.addCell(new Number(12,i+1,list2.get(i).getTotalPrice(),wcf_number));
                software_cost_total += list2.get(i).getCostTotal();
                software_profit_total += list2.get(i).getProfitTotal();
                software_total_price += list2.get(i).getTotalPrice();
            }
            sheet2.addCell(new Label(8, list2.size()+2, "小计",wcf_title));
            sheet2.addCell(new Number(9,list2.size()+2,software_cost_total,wcf_number));
            sheet2.addCell(new Label(10, list2.size()+2, "小计",wcf_title));
            sheet2.addCell(new Number(11,list2.size()+2,software_profit_total,wcf_number));
            sheet2.addCell(new Number(12,list2.size()+2,software_total_price,wcf_number));
            // 写入数据并关闭文件
            book.write();
        } catch (Exception e) {
            System.out.println(e);
        }finally{
            if(book!=null){
                try {
                    book.close();
                } catch (Exception e) {
                    e.printStackTrace();
                } 
            }
        }
    }
}