An example explanation of jxl exporting data to excel

advantage:

Jxl supports Chinese very well, and the operation is simple, and the method depends on the name and meaning.

Jxl is a pure java API, which performs perfectly across platforms. The code can be run on Windows or Linux without rewriting

Supports all versions of Excel 95-2000 (it is said online that it can support Excel 2007 at present, but has not tried it yet)

Generate Excel 2000 standard format

Support font, number, date operations

Able to modify cell properties

Images and charts are supported, but this API has limited support for graphics and charts, and only recognizes PNG format.

shortcoming:

Inefficient, imperfect picture support, and less powerful support for formats than POI

Case:

 String times = (new SimpleDateFormat("yyyyMMddHHmmss")).format(new Date());String fname = "System Log" + times; // File name List<Logs> list=logsService.selectForList(hql.toString()); String path = request.getSession().getServletContext().getRealPath("/") + "xls/" + (new SimpleDateFormat("yyyyMMdd")).format(new Date()); File file = new File(path); // Create if (!file.exists() && !file.isDirectory()) { file.mkdir(); } response.setContentType("application/vnd.ms-excel;charset=utf-8");// // Specify the file's saving type. response.setCharacterEncoding("utf-8"); ExportUtil.writer_log(request,fname, list, response);//Download to local

Writer_log export method is as follows

 /** * Generate excel file and export to local computer* @param fname file name* @param list The data to be printed, that is, the data list of database queries*/ public static void writer_log(HttpServletRequest request,String fname, List list, HttpServletResponse response) { try { OutputStream os = response.getOutputStream();//Get the output stream response.reset();//Clear the output stream// The following is the start of processing the Chinese file name response.setCharacterEncoding("UTF-8");//Set the encoding format of the corresponding content if(isMsBrowser(request)) fname= java.net.URLEncoder.encode(fname ,"UTF-8"); else fname = new String(fname.getBytes("UTF-8"),"ISO-8859-1"); response.setHeader("Content-Disposition","attachment;filename="+fname+".xls"); response.setContentType("application/msexcel;charset=utf-8");//Define the output type// The processing of the Chinese file name ends// The Workbook here imports import jxl.Workbook; WritableWorkbook wbook = Workbook.createWorkbook(os); // Create excel file WritableSheet sheet = wbook.createSheet("system log", 0); // Worksheet name CellView cellView = new CellView(); cellView.setAutosize(true); // Set the automatic size sheet.setColumnView(0, 8); //Set the cell width, 0 is the column number, 8 is the width sheet.setColumnView(1, 20); //Set the cell width, 1 is the column number, 20 is the width sheet.setColumnView(2, 24); sheet.setColumnView(3, 20); sheet.setColumnView(4, 30); sheet.setColumnView(5, 13); sheet.setColumnView(6, 15); sheet.setColumnView(7, 32); sheet.setColumnView(8, 15); //Set Excel font WritableFont wfont = new WritableFont(WritableFont.createFont("宋体"), 22, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); //Set cell font style WritableCellFormat titleFormat = new WritableCellFormat(wfont); //Add cell font titleFormat.setAlignment(Alignment.CENTRE); //Set text centering alignment; String[] title = { "System Log" }; // Set the Excel table header start for (int i = 0; i < title.length; i++) { // What is imported here is import jxl.write.Label; Label excelTitle = new Label(i, 0, title[i], titleFormat); //Cell content// Parameter order: start column, start row, end column, end row sheet.mergeCells(0, 0, 8, 0); // Location, which row, which column sheet.addCell(excelTitle); //Add cell information} //Set the Excel table header end// The first row, that is, the display time, parameters: (Column, row, content) WritableFont wfonttime = new WritableFont(WritableFont.createFont("宋体"), 11, WritableFont.NO_BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); //Set cell font style WritableCellFormat titletime = new WritableCellFormat(wfonttime); //Add cell font titletime.setAlignment(Alignment.RIGHT); //Set text centering alignment; DateFormat df = new SimpleDateFormat("yyyy-MM-dd");// sheet.setColumnView(1, cellView); // Automatically set the column width according to the content Label contentDate = new Label(0, 1, df.format(new Date()), titletime); // Cell content// sheet.mergeCells(16, 1, 18, 1); // The location, which row and column are in the row, that is, the merged position. If there is no merge, you can not write sheet.addCell(contentDate); // Add cell information// The end of the first row// The second row shows the condition title bar WritableFont wfont2 = new WritableFont(WritableFont.createFont("宋体"), 11, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); //Set cell font style WritableCellFormat titleFormat2 = new WritableCellFormat(wfont2); //Add cell font titleFormat2.setBorder(Border.ALL, BorderLineStyle.THIN); //Set border-solid line; titleFormat2.setAlignment(Alignment.CENTRE); //Set the centered alignment of text; titleFormat2.setVerticalAlignment(VerticalAlignment.CENTRE); //Set vertical centering; Label content2a = new Label(0, 2, "serial number", titleFormat2); //Cell content--1st grid Label content2b = new Label(1, 2, "user name", titleFormat2); //Cell content--2nd grid Label content2c = new Label(2, 2, "record time", titleFormat2); //Cell content--3rd grid Label content2c = new Label(2, 2, "record time", titleFormat2); //Cell content--3rd grid Label content2c = new Label(2, 2, "record time", titleFormat2); //Cell content--3rd grid Label content2d = new Label(3, 2, "Operation Module", titleFormat2); //Cell content--4th grid Label content2e = new Label(4, 2, "Operation Content", titleFormat2); //Cell content--5th grid Label content2f = new Label(5, 2, "Operation Action", titleFormat2); //Cell content--6th grid Label content2g = new Label(6, 2, "Operator IP", titleFormat2); //Cell content--7th grid Label content2h = new Label(7, 2, "Operation Organization", titleFormat2); //Cell content--8th grid Label content2i = new Label(8, 2, "Note", titleFormat2); //Cell content--9th grid sheet.mergeCells(0, 1, 8, 1); /*sheet.mergeCells(0, 2, 0, 4); //The location, which row and column are in the row, that is, the position of the merge, if there is no merge, you can not write it; (Here is the starting line 3 to row 5, the column 1) sheet.mergeCells(1, 2, 1, 4); //The location, which row and column are in the row, that is, the position of the merge, if there is no merge, you can not write it; (Here is the starting line 3 to row 5, the column 2) sheet.mergeCells(2, 2, 2, 4); //The location, which row and column are in the row, that is, the merged position, if there is no merge, you can not write it; (Here is the starting line 3 to row 5, column 3) sheet.mergeCells(3, 2, 3, 4); //The location, which row and column are in the row, that is, the merged position, if there is no merge, you can not write it; (Here is the starting line 3 to row 5, column 4) sheet.mergeCells(4, 2, 4, 4); //The location, which row and column are in the row, that is, the merged position, if there is no merge, you can not write it; (Here is the starting line 3 to row 5, column 5) sheet.mergeCells(5, 2, 5, 4); //The location, which row and column are in the row, that is, the merged position, if there is no merge, you can not write it; (Here is the starting line 3 to row 5, column 6) sheet.mergeCells(6, 2, 6, 4); //The location, which row and column are in the row, that is, the merged position, if there is no merge, you can not write it; (Here is the starting line 3 to row 5, column 7) sheet.mergeCells(7, 2, 7, 4); //The location, which row and column are in the row, that is, the merged position, if there is no merge, you can not write it; (Here is the starting line 3 to row 5, column 8) sheet.mergeCells(8, 2, 8, 4); //The location, which row and column are in the row, that is, the position of the merge. If there is no merge, you can not write it; (Here is the starting line 3 to row 5, column 9) sheet.mergeCells(9, 2, 9, 4); //The location, which row and column are in the row, that is, the position of the merge. If there is no merge, you can not write it; (Here is the starting line 3 to row 5, column 10) sheet.mergeCells(10, 2, 12, 3); //The location, which row and column are in the row, that is, the position of the merge. If there is no merge, you can not write it; (Here is the starting line 3 to row 4, column 11-13) sheet.mergeCells(13, 2, 15, 3); //The location, which row and column are in the first row, that is, the merged position, if there is no merge, you can not write it; (Here is the starting line 3 to row 4, columns 14-16) sheet.mergeCells(16, 2, 16, 4); //The location, which row and column are in the first row, that is, the merged position, if there is no merge, you can not write it; (Here is the starting line 3 to row 5, column 17) sheet.mergeCells(17, 2, 17, 4); //The location, which row and column are in the first row, that is, the merged position, if there is no merge, you can not write it; (Here is the starting line 3 to row 5, column 18) sheet.mergeCells(18, 2, 18); //The location, which row and column are in the first row, that is, the merged position. If there is no merge, you can not write it; (Here is the starting line 3 to 5, column 19) */ sheet.addCell(content2a); //Add the first cell information sheet.addCell(content2b); //Add the second cell information sheet.addCell(content2c); //Add the third cell information sheet.addCell(content2d); //Add the fourth cell information sheet.addCell(content2e); //Add the fifth cell information sheet.addCell(content2f); //Add the sixth cell information sheet.addCell(content2g); //Add the seventh cell information sheet.addCell(content2h); //Add the 8th cell information sheet.addCell(content2i); //Add the 9th cell information WritableFont wf = new WritableFont(WritableFont.createFont("安安"), 11, WritableFont.NO_BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); //Set the cell font style WritableCellFormat wcf = new WritableCellFormat(wf); //Add cell font wcf.setBorder(Border.ALL, BorderLineStyle.THIN); //Set the border--solid line; wcf.setVerticalAlignment(VerticalAlignment.CENTRE); //Set vertical alignment wcf.setAlignment(Alignment.CENTRE); //Set horizontal centering alignment of text; wcf.setWrap(true); //Automatic line wrap WritableFont wf1 = new WritableFont(WritableFont.createFont("安体"), 11, WritableFont.NO_BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); //Set cell font style WritableCellFormat wcf1 = new WritableCellFormat(wf1); //Add cell font wcf1.setBorder(Border.LEFT, BorderLineStyle.THIN); //Set border-solid line; wcf1.setVerticalAlignment(VerticalAlignment.CENTRE); //Set vertical alignment wcf1.setAlignment(Alignment.CENTRE); //Set horizontal centering alignment of text; // Information obtained by the following loop database int c = 1; // The line number of Excel used for looping Iterator it = list.iterator(); while (it.hasNext()) { Logs tc = (Logs) it.next(); DateFormat dfmt = new SimpleDateFormat("yyyy.MM"); String xh = String.valueOf(c); if(xh== null){ xh = ""; } Label content0 = new Label(0, c+2, xh, wcf); //Serial number String name = tc.getUserName(); if(name == null){ name = ""; } Label content1 = new Label(1, c+2, name, wcf); //Username String xrPresent = tc.getLogTime(); if(xrPresent == null){ xrPresent = ""; } Label content2 = new Label(2, c+2, xrPresent, wcf); //Record time String czModel = tc.getModel(); if(czModel == null){ czModel = ""; } Label content3 = new Label(3, c+2, czModel, wcf); //Operation module String sex = tc.getContent(); if(sex == null){ sex = ""; } Label content4 = new Label(4, c+2, sex, wcf); //Operation content String birthday = tc.getOperate(); if(birthday == null){ birthday = ""; } Label content5 = new Label(5, c+2, birthday, wcf); //Operation action String nation = tc.getIp(); if(nation == null){ nation = ""; } Label content6 = new Label(6, c+2, nation, wcf); //Operator IP String origin = tc.getOrgName(); if(origin == null){ origin = ""; } Label content7 = new Label(7, c+2, origin, wcf); //Or organization String bPlace = tc.getRemark(); if(bPlace == null){ bPlace = ""; } Label content8 = new Label(8, c+2, bPlace, wcf); //Remark String abc=""; Label content9 = new Label(9, c+2, abc, wcf1); //Remark sheet.setRowView(c+2, 600); //Set the line height sheet.setRowView(c+2, 600); sheet.setRowView(c+2, 600); sheet.setRowView(c+2, 600); sheet.setRowView(c+2, 600); sheet.setRowView(c+2, 600); sheet.setRowView(c+2, 600); sheet.setRowView(c+2, 600); sheet.setRowView(c+2, 600); sheet.mergeCells(0, c+2, 0, c+2); // MergeCells(1, c+2, 0, c+2); //mergeCells(a,b,c,d) Cell merge function sheet.mergeCells(2, c+2, 0, c+2); //a Cell column number sheet.mergeCells(3, c+2, 0, c+2); //b Cell line number sheet.mergeCells(4, c+2, 0, c+2); //c Starting from cell [a,b], merge down to c column sheet.mergeCells(5, c+2, 0, c+2); //d From cell [a,b], merge down to row d sheet.mergeCells(6, c+2, 0, c+2); sheet.mergeCells(7, c+2, 0, c+2); sheet.mergeCells(8, c+2, 0, c+2); sheet.mergeCells(9, c+2, 0, c+2); sheet.addCell(content0); sheet.addCell(content1); sheet.addCell(content2); sheet.addCell(content3); sheet.addCell(content4); sheet.addCell(content5); sheet.addCell(content6); sheet.addCell(content7); sheet.addCell(content8); sheet.addCell(content9); c++; } wbook.write(); // Write to the file wbook.close(); os.close(); } catch (Exception e) { throw new PaikeException("Export file error"); } }

The above example explanation of jxl exporting data to excel is all the content I share with you. I hope you can give you a reference and I hope you can support Wulin.com more.