JXL写入数据到EXCEL中

---

前言

我编写了一个用JXL技术将数据导入到EXCEL的模板

---

不多说直接上代码
**

导出Excel

package com.zhao;

import jxl.*;


import jxl.format.Alignment;
import jxl.format.ScriptStyle;
import jxl.format.UnderlineStyle;
import jxl.read.biff.BiffException;
import jxl.write.*;
import jxl.write.Label;
import jxl.write.Number;

import java.awt.*;
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Random;

public class TestJxl extends jxl.format.Colour{


  protected TestJxl(int val, String s, int r, int g, int b) {
    super(val, s, r, g, b);
  }

//创建Excel格式,path创建的文件地址，sheetStr sheet表和 sheetIndex第几个表，title表题名字，titleLenght表题合并单元格的长度，labelList表头和表的数据，titleColor表题的颜色（0-100），表头的颜色colorHead（0-100）,表单颜色colorTable（0-100）
    public static void jxlUtil(String path, String sheetStr, int sheetIndex, String title, int titleLenght, Map<Object,List<Object>> labelList, int titleColor , int colorHead,int colorTable) throws WriteException, IOException {
      WritableWorkbook workbook = Workbook.createWorkbook(new File(path));
      WritableSheet sheet = workbook.createSheet(sheetStr,sheetIndex);
      Label label = new Label(0,0,title);
      sheet.mergeCells(0,0,titleLenght-1,0);//合并单元格
      sheet.setRowView(0,700);//设置行高
      sheet.setColumnView(0,35);//设置列高
      sheet.addCell(label);
      for (int i=1; i<labelList.size(); i++){
        sheet.setRowView(i,350);//设置行高
        sheet.setColumnView(i,15);//设置列高
      }
      int  m=0;
      int b = -1;
      for (Object key: labelList.keySet()){
        b++;
        System.out.println(key);
         sheet.addCell( new Label(b,1,(String) key));
        int t = -1;
        for (Object o : labelList.get(key) ) {
         t++;
         int f = -1;
         int mn = sheet.getColumns();
         for (Object o1 :labelList.keySet() ) {
           f++;
            String cell = sheet.getCell(f, 1).getContents();
           if (cell.equals(key)) {
                m = f;
           }
         }

  List<Object> l =  labelList.get(key);
          sheet.addCell(new Label( m, t+2, (String)o));
          WritableFont titleWf2 = new WritableFont(WritableFont.createFont("宋体"),//字体
                  12,//字号
                  WritableFont.NO_BOLD,//粗体
                  false,//斜体
                  UnderlineStyle.NO_UNDERLINE,//下划线
                  Colour.BLACK,//字体偃师市
                  ScriptStyle.NORMAL_SCRIPT
          );
          WritableCellFormat wcf3 = new WritableCellFormat(titleWf2);
          ExcelUtil color2 = new ExcelUtil(colorTable,"255",0 ,0, 0	);
          wcf3.setAlignment(Alignment.CENTRE);
          wcf3.setBackground(color2);
          wcf3.setBorder(Border.ALL, BorderLineStyle.THICK);//添加边框
          sheet.getWritableCell(m,t+2).setCellFormat(wcf3);
        }

      }

 WritableCell cell = null;
      WritableCellFormat wcf = null;
      cell = sheet.getWritableCell(0,0);
      // WritableCell  cell1 = sheet.getWritableCell(0,1);
      List<WritableCell> writableCellList = new ArrayList<>();
      for (int i =0; i <labelList.size(); i++){
        writableCellList.add( sheet.getWritableCell(i,1));
      }
      WritableFont titleWf = new WritableFont(WritableFont.createFont("仿宋_GB2312"),//字体
              30,//字号
              WritableFont.NO_BOLD,//粗体
              false,//斜体
              UnderlineStyle.NO_UNDERLINE,//下划线
              Colour.BLACK,//字体偃师市
              ScriptStyle.NORMAL_SCRIPT
      );
      WritableFont titleWf1 = new WritableFont(WritableFont.createFont("宋体"),//字体
              15,//字号
              WritableFont.NO_BOLD,//粗体
              false,//斜体
              UnderlineStyle.NO_UNDERLINE,//下划线
              Colour.BLACK,//字体偃师市
              ScriptStyle.NORMAL_SCRIPT
      );

      wcf = new WritableCellFormat(titleWf);
      WritableCellFormat wcf1 = new WritableCellFormat(titleWf1);

      ExcelUtil color = new ExcelUtil(titleColor,"255",0 ,0, 0	);
      ExcelUtil color1 = new ExcelUtil(colorHead,"255",0 ,0, 0	);

  wcf.setBackground(color);//设置单元格的颜色
      wcf.setAlignment(Alignment.CENTRE);//设置对齐方式
      wcf1.setBackground(color1);//设置单元格的颜色
      wcf1.setAlignment(Alignment.CENTRE);
      wcf1.setBorder(Border.ALL, BorderLineStyle.THICK);//添加边框
      wcf.setBorder(Border.ALL, BorderLineStyle.THICK);//添加边框

      cell.setCellFormat(wcf);
      for (int i=0; i<writableCellList.size(); i++){
        writableCellList.get(i).setCellFormat(wcf1);
      }
      workbook.write();
      workbook.close();


    }
}

我这个写的有点长了而且很麻烦，但对于我这个菜鸟来说实在是想不出什么办法了，还请大家帮帮忙看看怎么改进。然后这个Colour我是通过他的构造方法来写的可以直接写颜色号，不过还不太清楚怎么实现，所以只能写他那个号（0-100）

测试类

package com.zhao;

import jxl.write.Label;
import jxl.write.WriteException;

import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class JXlTest {

    public static void main(String[] args) {

        Student s = new Student("张三","13","上海");
        Student s2 = new Student("李四","15","深圳");
        String name[] = {"姓名","年龄","地址"};
        List<Object> list1 = new ArrayList<>();
        list1.add(s.getName());
        list1.add(s2.getName());
        List<Object> list2 = new ArrayList<>();
        list2.add(s.getAge());
        list2.add(s2.getAge());
        List<Object> list3 = new ArrayList<>();
        list3.add(s.getAdd());
        list3.add(s2.getAdd());
        Map<Object,List<Object>> l = new HashMap<>();
        l.put( name[0],  list1);
        l.put( name[1],  list2);
        l.put( name[2],  list3);


        try {
            TestJxl.jxlUtil("user/练习.xls","第一个sheet页",0,"出口",3,l,41,60,20);
        } catch (WriteException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

这里我是创建了一个Student对象的有name ，age，和add属性，后面的

**

效果

大概就是这个样子的
**

总结

应该算是第一篇文章了，多有不足，希望大家可以多给我一些意见和建议