从数据库导出数据到excel之List<map>导出

说明:很多时候取出来的数据是封装为List<Map<String,Object>>,可以直接导出excel表格

项目说明就在 “上一篇

直接上代码(数据层和业务层不用说了,查出的代码格式为List<Map<String,Object>>即可):

控制层

List<Map<String,String>> list=expot.GetStudentTest(gender.getGender());
         System.out.println("listDate:"+list);         
        ExportExcelHSSF<List<Map<String,String>>> ee= new ExportExcelHSSF<>();
        ExportExcelOutputStream ee=new ExportExcelOutputStream();
        //String[] headers = {  "姓名", "性别", "体重","班级","网络协议","javaEE","计算机基础","Linux操作系统","网络安全","sql数据库","数据结构" };
        String[] headers = {  "姓名","体重","网络协议","javaEE","计算机基础","Linux操作系统","网络安全","sql数据库","数据结构" };
        String fileName = "信息表";
        ee.exportExcel(list, headers,fileName, response);

实体类:

public class ExportDateTest implements Serializable{
    private String name;
    //private String gender;//性别
    private String weight;
    //private String grades;//班级
    private Double Networkprotocol;
    private Double javaEE;
    private Double Computerbasis;
    private Double Linuxoperatingsystem;
    private Double networksecurity;
    private Double SQLdatabase;
    private Double datastructure;
    public ExportDateTest() {
    
        // TODO Auto-generated constructor stub
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    /*
    public String getGender() {
        return gender;
    }
    public void setGender(String gender) {
        this.gender = gender;
    }
    */
    public String getWeight() {
        return weight;
    }
    public void setWeight(String weight) {
        this.weight = weight;
    }
    /*
    public String getGrades() {
        return grades;
    }
    public void setGrades(String grades) {
        this.grades = grades;
    }
    */
    public Double getNetworkprotocol() {
        return Networkprotocol;
    }
    public void setNetworkprotocol(Double networkprotocol) {
        Networkprotocol = networkprotocol;
    }
    public Double getJavaEE() {
        return javaEE;
    }
    public void setJavaEE(Double javaEE) {
        this.javaEE = javaEE;
    }
    public Double getComputerbasis() {
        return Computerbasis;
    }
    public void setComputerbasis(Double computerbasis) {
        Computerbasis = computerbasis;
    }
    public Double getLinuxoperatingsystem() {
        return Linuxoperatingsystem;
    }
    public void setLinuxoperatingsystem(Double linuxoperatingsystem) {
        Linuxoperatingsystem = linuxoperatingsystem;
    }
    public Double getNetworksecurity() {
        return networksecurity;
    }
    public void setNetworksecurity(Double networksecurity) {
        this.networksecurity = networksecurity;
    }
    public Double getSQLdatabase() {
        return SQLdatabase;
    }
    public void setSQLdatabase(Double sQLdatabase) {
        SQLdatabase = sQLdatabase;
    }
    public Double getDatastructure() {
        return datastructure;
    }
    public void setDatastructure(Double datastructure) {
        this.datastructure = datastructure;
    }
    public ExportDateTest(String name, String gender, String weight, String grades, Double networkprotocol, Double javaEE,
            Double computerbasis, Double linuxoperatingsystem, Double networksecurity, Double sQLdatabase,
            Double datastructure) {
        super();
        this.name = name;
        //this.gender = gender;
        this.weight = weight;
        //this.grades = grades;
        Networkprotocol = networkprotocol;
        this.javaEE = javaEE;
        Computerbasis = computerbasis;
        Linuxoperatingsystem = linuxoperatingsystem;
        this.networksecurity = networksecurity;
        SQLdatabase = sQLdatabase;
        this.datastructure = datastructure;
    }
    @Override
    public String toString() {
        return "ExportDate [name=" + name + ""
                //+ ", gender=" + gender + ""
                + ", weight=" + weight + ""
            //    + ", grades=" + grades
                + ", Networkprotocol=" + Networkprotocol + ", javaEE=" + javaEE + ", Computerbasis=" + Computerbasis
                + ", Linuxoperatingsystem=" + Linuxoperatingsystem + ", networksecurity=" + networksecurity
                + ", SQLdatabase=" + SQLdatabase + ", datastructure=" + datastructure + "]";
    }
    

    

}

导出代码:

/**
 * 将List<Map<>>数据格式导出到excel中
 * @author 开发者
 *
 */
public class ExportExcelOutputStreamTest {
     public String exportExcel(List<Map<String, String>> orderlist,String[] headerlist, 
             String name,HttpServletResponse response ) {  
            String result = "系统提示:Excel文件导出成功!";  
            // 以下开始输出到EXCEL  
            try {  
                 //定义输出流,以便打开保存对话框______________________begin  
                 OutputStream os = response.getOutputStream();// 取得输出流  
                 response.reset();// 清空输出流  
                   
                 String fileName = name + ".xls";  
                 response.setContentType("application/x-msdownload");// 设定输出文件类型
                 response.setHeader("Content-Disposition",
                         "attachment;filename=" + new String( fileName.getBytes("gb2312"), "ISO8859-1" )); //设定文件输出类型         
                 //定义输出流,以便打开保存对话框_______________________end  
                /** **********创建工作簿************ */  
                WritableWorkbook workbook = Workbook.createWorkbook(os);        
                /** **********创建工作表************ */       
                WritableSheet sheet = workbook.createSheet("Sheet1", 0);        
                /** **********设置纵横打印(默认为纵打)、打印纸***************** */  
                SheetSettings sheetset = sheet.getSettings();  
                sheetset.setProtected(false);  
                sheetset.setDefaultColumnWidth(20);  
      
                /** ************设置单元格字体************** */  
                WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 10);  
                WritableFont BoldFont = new WritableFont(WritableFont.ARIAL, 10,  
                        WritableFont.BOLD);  
                WritableFont TitleFont = new WritableFont(WritableFont.ARIAL, 20,  
                        WritableFont.BOLD);  
      
                /** ************以下设置三种单元格样式,灵活备用************ */  
                // 用于表名,要高端大气!  
                WritableCellFormat title_center = new WritableCellFormat(TitleFont);  
                title_center.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条  
                title_center.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐  
                title_center.setAlignment(Alignment.CENTRE); // 文字水平对齐  
                title_center.setWrap(false); // 文字是否换行  
                  
                // 用于标题居中  
                WritableCellFormat wcf_center = new WritableCellFormat(BoldFont);  
                wcf_center.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条  
                wcf_center.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐  
                wcf_center.setAlignment(Alignment.CENTRE); // 文字水平对齐  
                wcf_center.setWrap(false); // 文字是否换行  
      
                // 用于正文居左  
                WritableCellFormat wcf_left = new WritableCellFormat(NormalFont);  
                wcf_left.setBorder(Border.NONE, BorderLineStyle.THIN); // 线条  
                wcf_left.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐  
                wcf_left.setAlignment(Alignment.LEFT); // 文字水平对齐  
                wcf_left.setWrap(true); // 文字是否换行  
                  
      
       
                /** ***************以下是EXCEL第一行列标题********************* */  
                for (int i = 0; i < headerlist.length; i++) {  
                    sheet.addCell(new Label(i, 3, headerlist[i], wcf_center));  
                }  
                /** ***************以下是EXCEL正文数据********************* */  
                //这里要分成两种情况,第一种传的是List<Map>,用Map的方式处理  
                //第二种传的是List<Object>,用普通类取属性的方式来处理;  
        
                Iterator iterator = orderlist.iterator();  
                Object o = iterator.next();                 
                    Map m = (Map) o;  
                    System.out.println("m:"+m);  
                    Iterator iter = m.entrySet().iterator();  
                    int first_j=0;  
                    while (iter.hasNext()) {                            
                        Map.Entry entry = (Map.Entry) iter.next();  
                        String[] key_value = entry.toString().split("=");  
                       
                        System.out.println(key_value[1]);  
                        
                        sheet.addCell(new Label(first_j, 4, key_value[1], wcf_left));  
                        first_j++;  
                    }  
                    int i=5;  
                    while(iterator.hasNext()){  
                        Map row_map = (Map) iterator.next();  
                        Iterator row_iterator = row_map.entrySet().iterator();  
                        int second_j=0;  
                        while(row_iterator.hasNext()){  
                            Map.Entry entry_column = (Map.Entry) row_iterator.next();  
                            String[] key_value = entry_column.toString().split("=");  
                            sheet.addCell(new Label(second_j, i, key_value[1], wcf_left));  
                            second_j++;  
                        }  
                        i++;  
                    }
                /** **********将以上缓存中的内容写到EXCEL文件中******** */  
                workbook.write();  
                /** *********关闭文件************* */  
                workbook.close();  
                  
                System.out.println(result);  
      
            } catch (Exception e) {  
                result = "系统提示:Excel文件导出失败,原因:" + e.toString();  
                System.out.println(result);  
                e.printStackTrace();  
            }  
            return result;  
        }  

 

posted @ 2017-08-31 19:22  程序也疯狂  阅读(4852)  评论(0编辑  收藏  举报