java 运用jxl 读取和输出Excel
文章结尾源码以及jxl包
1、输出excel：
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("输出失败！"); } } }
2、读取：
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(); } } }
源码：
链接：http://pan.baidu.com/s/1kVL2DWr 密码：h643
