package JmExcel;

import java.io.File;

import java.io.FileOutputStream;

import java.io.IOException;

import java.io.OutputStream;

import java.util.ArrayList;

import java.util.Calendar;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import jxl.Workbook;

import jxl.write.Label;

import jxl.write.WritableCellFormat;

import jxl.write.WritableFont;

import jxl.write.WritableSheet;

import jxl.write.WritableWorkbook;

import jxl.write.WriteException;

import jxl.write.biff.RowsExceededException;

public class JavaMakeExcel {

    private List list = new ArrayList();

    public  void createQuarterlyExcel(OutputStream os) throws IOException, RowsExceededException, WriteException, InterruptedException{

        //创建一个工作薄excel文档

        WritableWorkbook workbook = Workbook.createWorkbook(os);

        //创建一个表sheet，一个工作薄可以有多个sheet

        WritableSheet sheet = workbook.createSheet("季度报", 0);

        //设置列宽，行列开始下标均为0

        sheet.setColumnView(0, 4);

        sheet.setColumnView(1, 10);

        sheet.setColumnView(2, 16);

        sheet.setColumnView(3, 20);

        sheet.setColumnView(4, 8);

        sheet.setColumnView(5, 12);

        sheet.setColumnView(6, 7);

        sheet.setColumnView(7, 12);

        sheet.setColumnView(8, 20);

        sheet.setColumnView(9, 20);

        sheet.setColumnView(10,20);

        sheet.setColumnView(11,16);

        Calendar cal = Calendar.getInstance();

        //添加合并单元格，第一个参数是起始列，第二个参数是起始行，第三个参数是终止列，第四个参数是终止行

        sheet.mergeCells(0, 0, 1, 0);

        //创建一个单元格，第一个参数为开始列，第二个参数为开始行，第三个参数为填充内容，第四个参数(可省略)内容格式

        Label fj = new Label(0,0,"附件：");

        //在表中添加该单元格

        sheet.addCell(fj);

        //合并单元格，第一个参数是起始列，第二个参数是起始行，第三个参数是终止列，第四个参数是终止行

        sheet.mergeCells(0, 1, 11, 1);

        //创建内容样式1：表头样式，设置字体种类和黑体显示,字体为Arial,字号大小为22,加粗

        WritableFont bold = new WritableFont(WritableFont.createFont("宋体"),22,WritableFont.BOLD);

        WritableCellFormat titleFormate = new WritableCellFormat(bold);//生成一个单元格样式控制对象

        titleFormate.setAlignment(jxl.format.Alignment.CENTRE);//单元格中的内容水平方向居中

        titleFormate.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//单元格的内容垂直方向居中

        //Label(开始列,开始行,labeltitle,格式化);

        Label title = new Label(0,1,"**区"+cal.get(cal.YEAR)+"年市委市政府重点工作落实情况表",titleFormate);

        //设置行高，.setRowView(行下标，高度值)

        sheet.setRowView(1, 700, false);

        sheet.addCell(title);

        //创建内容样式2：表列头样式

        WritableFont color = new WritableFont(WritableFont.createFont("宋体"),10,WritableFont.BOLD);

        WritableCellFormat colorFormat = new WritableCellFormat(color);

        colorFormat.setAlignment(jxl.format.Alignment.CENTRE);

        colorFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);

        colorFormat.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);

        colorFormat.setWrap(true);

        //创建内容样式3

        WritableFont size = new WritableFont(WritableFont.createFont("宋体"),8,WritableFont.BOLD);

        WritableCellFormat colorFormat2 = new WritableCellFormat(size);

        colorFormat2.setAlignment(jxl.format.Alignment.CENTRE);

        colorFormat2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);

        colorFormat2.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);

        colorFormat2.setWrap(true);

        sheet.setRowView(2, 350, false);

        //填充表头

        sheet.mergeCells(0, 2, 0, 3);

        Label index = new Label(0,2,"序号",colorFormat);

        sheet.addCell(index);

        sheet.mergeCells(1, 2, 1, 3);

        Label leibie = new Label(1,2,"类别",colorFormat);

        sheet.addCell(leibie);

        sheet.mergeCells(2, 2, 2, 3);

        Label aim = new Label(2,2,"区年度具体目标",colorFormat);

        sheet.addCell(aim);

        Label aim2 = new Label(3,2,"工作目标",colorFormat);

        sheet.addCell(aim2);

        Label quarter = new Label(3,3,"三季度",colorFormat);

        sheet.addCell(quarter);

        sheet.mergeCells(4, 2, 4, 3);

        Label localHead = new Label(4,2,"区分管领导",colorFormat);

        sheet.addCell(localHead);

        sheet.mergeCells(5, 2, 5, 3);

        Label deptname = new Label(5,2,"牵头单位",colorFormat);

        sheet.addCell(deptname);

        sheet.mergeCells(6, 2, 6, 3);

        Label checker = new Label(6,2,"责任人",colorFormat);

        sheet.addCell(checker);

        sheet.mergeCells(7, 2, 7, 3);

        Label assist = new Label(7,2,"协办单位",colorFormat);

        sheet.addCell(assist);

        sheet.mergeCells(8, 2, 8, 3);

        Label finish = new Label(8,2,"落实情况",colorFormat);

        sheet.addCell(finish);

        sheet.mergeCells(9, 2, 9, 3);

        Label problem = new Label(9,2,"存在问题",colorFormat);

        sheet.addCell(problem);

        sheet.mergeCells(10, 2, 10, 3);

        Label next = new Label(10,2,"下部措施",colorFormat);

        sheet.addCell(next);

        sheet.mergeCells(11, 2, 11, 3);

        Label waring = new Label(11,2,"完成状态(按计划进行、进度顺利但有延误风险、延误)",colorFormat2);

        sheet.addCell(waring);

        //创建内容样式4：表正文样式

        WritableFont zw = new WritableFont(WritableFont.createFont("宋体"),10);

        WritableCellFormat zwFormat = new WritableCellFormat(zw);

        zwFormat.setAlignment(jxl.format.Alignment.CENTRE);

        zwFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);

        zwFormat.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);

        zwFormat.setWrap(true);  //换行：正文样式添加自动换行

        //遍历输出正文

        for (int i = 0; i < list.size(); i++) {

            Map m = (Map) list.get(i);

            String lbstr = String.valueOf(m.get("lb"));

            String year_target = String.valueOf(m.get("year_target"));

            String quarterly_target = String.valueOf(m.get("quarterly_target"));

            String head_name = String.valueOf(m.get("head_name"));

            String person_liable = String.valueOf(m.get("person_liable"));

            String main_dept_name = String.valueOf(m.get("main_dept_name"));

            String assist_dept_name = String.valueOf(m.get("assist_dept_name"));

            String contentes = String.valueOf(m.get("contentes"));

            String problem1 = String.valueOf(m.get("problem"));

            String nextplan = String.valueOf(m.get("nextplan"));

            String waringstr = null;

            String struts = String.valueOf(m.get("status"));

            struts= (struts.equals("null")) ? "1" : struts;

            int waringflag = Integer.parseInt(struts) ;

            if (waringflag == 1) {

                waringstr = "按计划进行";

            }else if (waringflag == 2) {

                waringstr = "进度顺利但有延误风险";

            }else if (waringflag == 3) {

                waringstr = "延误";

            }

            Label index2 = new Label(0,i+4,String.valueOf(i+1),zwFormat);

            sheet.addCell(index2);

            Label lb = new Label(1,i+4,lbstr,zwFormat);

            sheet.addCell(lb);

            Label year_targetl = new Label(2,i+4,year_target,zwFormat);

            sheet.addCell(year_targetl);

            Label quarterly_targetl = new Label(3,i+4,quarterly_target,zwFormat);

            sheet.addCell(quarterly_targetl);

            Label head_namel = new Label(4,i+4,head_name,zwFormat);

            sheet.addCell(head_namel);

            Label main_dept_namel = new Label(5,i+4,main_dept_name,zwFormat);

            sheet.addCell(main_dept_namel);

            Label person_liablel = new Label(6,i+4,person_liable,zwFormat);

            sheet.addCell(person_liablel);

            Label assist_dept_namel = new Label(7,i+4,assist_dept_name,zwFormat);

            sheet.addCell(assist_dept_namel);

            Label contentesl = new Label(8,i+4,contentes,zwFormat);

            sheet.addCell(contentesl);

            Label probleml = new Label(9,i+4,problem1,zwFormat);

            sheet.addCell(probleml);

            Label nextplanl = new Label(10,i+4,nextplan,zwFormat);

            sheet.addCell(nextplanl);

            Label waringstrl = new Label(11,i+4,waringstr,zwFormat);

            sheet.addCell(waringstrl);

        }

        workbook.write();

        workbook.close();

        os.close();

    }

    public static void main(String[] args) throws RowsExceededException, WriteException, IOException {

        JavaMakeExcel jmXL = new JavaMakeExcel();

        Map map = new HashMap();

        map.put("lb", "市重点工作");

        map.put("year_target", "深入落实贯彻党的十九大会议精神");

        map.put("quarterly_target", "新开工和筹建9660套");

        map.put("head_name", "ckx");

        map.put("person_liable", "戴敏");

        map.put("main_dept_name", "区纪委");

        map.put("assist_dept_name", "督查室");

        map.put("contentes", "11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111");

        map.put("problem", "1111111111111111111111111111111111111111111111111111111111111111111");

        map.put("nextplan", "1111111");

        map.put("status", "1");

        Map map2 = new HashMap();

        map2.put("lb", "区常委会议定");

        map2.put("year_target", "刨路修路挣外快");

        map2.put("quarterly_target", "再刨一百公里");

        map2.put("head_name", "zrt");

        map2.put("person_liable", "任公仆");

        map2.put("main_dept_name", "住建局");

        map2.put("assist_dept_name", "城管大队");

        map2.put("contentes", "222222212222222222222222222222222222222222222222222222222222");

        map2.put("problem", "2222222");

        map2.put("nextplan", "2222222");

        map2.put("status", "2");

        //List list = new ArrayList();

        jmXL.list.add(map);

        jmXL.list.add(map2);

        //创建文件保存地址

        File file=new File("D:/myeclipseworkpase/我的Java输出Excel.xls");

        //新建文件输出流

        OutputStream os = new FileOutputStream(file);

        try {

            jmXL.createQuarterlyExcel(os);

        } catch (InterruptedException e) {

            System.out.println("输出失败！");

        }

    }

}

package JmExcel;

import java.io.FileInputStream;

import java.io.IOException;

import java.io.InputStream;

import java.util.ArrayList;

import java.util.List;

import jxl.Cell;

import jxl.Sheet;

import jxl.Workbook;

import jxl.read.biff.BiffException;

public class  JavaReadExcel{

    private String filePath;

    private List list = new ArrayList();

    public JavaReadExcel(String filePath){

        this.filePath = filePath;

    }

    private List readExcel() throws IOException, BiffException{

        //创建输入流

        InputStream stream = new FileInputStream(filePath);

        //获取Excel文件对象

        Workbook  rwb = Workbook.getWorkbook(stream);

        //获取文件的指定工作表 默认的第一个

        Sheet sheet = rwb.getSheet(0);

        //行数(表头的目录不需要，从1开始) sheet.getRows()获取excel行数

        for(int i=0; i<sheet.getRows(); i++){

             //创建一个数组 用来存储每一列的值  sheet.getColumns()获取excel列数

            String[] str = new String[sheet.getColumns()];

            Cell cell = null;

            //列数

            for(int j=0; j<sheet.getColumns(); j++){

              //获取第i行，第j列的值 sheet.getCell(逐行,逐列)

              cell = sheet.getCell(j,i);

              str[j] = cell.getContents();

            }

          //把刚获取的列存入list

          list.add(str);

        }

        return list;

    }

    private void outData(){

         for(int i=0;i<list.size();i++){

             String[] str = (String[])list.get(i);

             for(int j=0;j<str.length;j++){

              System.out.print(str[j]+'\t');

             }

             System.out.println();

         }

    }

    public static void main(String args[]) throws BiffException, IOException{

        JavaReadExcel excel = new JavaReadExcel("D:/myeclipseworkpase/我的Java输出Excel.xls");

        List li = excel.readExcel();

         for(int i=0;i<li.size();i++){

             String[] str = (String[])li.get(i);

             for(int j=0;j<str.length;j++){

              System.out.print(str[j]+'\t');

             }

             System.out.println();

         }

    }

}