利用Java将数据库中的数据导出为excel表格，导出完后可以下载，并压缩成zip格式
使用poi导出excel表，可以设置列宽、单元格合并居中、背景色设置等。
主要代码如下：
前端js代码
1 <body> 2 <button onclick="test()">导出 excel表</button> 3 4 <a id="download" href="#"> <span id="spanId" /> 5 </a> 6 </body> 7 <script type="text/javascript"> 8 9 function test(){ 10 $.ajax({ 11 //提交数据的类型 POST GET 12 type:"POST", 13 //提交的网址 14 url:"Excel.html", 15 //提交的数据 16 data:{"name":"zhangjinru"}, 17 //返回数据的格式 18 datatype: "json",//"xml", "html", "script", "json", "jsonp", "text". 19 //在请求之前调用的函数 20 // beforeSend:function(){$("#msg").html("logining");}, 21 //成功返回之后调用的函数 22 success:function(data){ 23 $("#download").attr("href", "/download/"+data.StringUrl); 24 //自动点击 25 $("#spanId").click(); 26 }, 27 //调用执行后调用的函数 28 complete: function(XMLHttpRequest, textStatus){ 29 }, 30 //调用出错执行的函数 31 error: function(){ 32 //请求出错处理 33 } 34 }); 35 } 36 </script>
后台主要代码
1 package com.zhang; 2 3 import java.io.File; 4 import java.io.FileInputStream; 5 import java.io.FileNotFoundException; 6 import java.io.FileOutputStream; 7 import java.io.IOException; 8 import java.io.PrintWriter; 9 import java.sql.Connection; 10 import java.sql.PreparedStatement; 11 import java.sql.ResultSet; 12 import java.sql.ResultSetMetaData; 13 import java.util.zip.ZipEntry; 14 import java.util.zip.ZipOutputStream; 15 16 import javax.servlet.ServletException; 17 import javax.servlet.http.HttpServlet; 18 import javax.servlet.http.HttpServletRequest; 19 import javax.servlet.http.HttpServletResponse; 20 21 import org.apache.poi.ss.usermodel.Cell; 22 import org.apache.poi.ss.usermodel.CellStyle; 23 import org.apache.poi.ss.usermodel.IndexedColors; 24 import org.apache.poi.ss.usermodel.Row; 25 import org.apache.poi.ss.usermodel.Sheet; 26 import org.apache.poi.ss.util.CellRangeAddress; 27 import org.apache.poi.xssf.streaming.SXSSFSheet; 28 import org.apache.poi.xssf.streaming.SXSSFWorkbook; 29 import org.apache.poi.xssf.usermodel.XSSFRichTextString; 30 31 public class Excel extends HttpServlet { 32 /** 33 * 查询数据库中的数据，然后将其导出到Excel表中。将导出的Excel表压缩成Zip文件。 34 * 点击导出后可以将Zip文件下载到文本地。单元格可以合并，可以设置背景色和居中 35 */ 36 private static final String file="D:/software/tomcat/webapps/DbToExcel/download/"; 37 private static final String zipFile="D:/software/tomcat/webapps/DbToExcel/download/"; 38 private static final long serialVersionUID = 1L; 39 40 public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { 41 doPost(request,response); 42 } 43 44 public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { 45 System.out.println(request.getParameter("name")); 46 47 response.setHeader("Access-Control-Allow-Origin", "*"); 48 response.setHeader("Access-Control-Allow-Methods", "*"); 49 response.setHeader("Access-Control-Allow-Headers", "x-requested-with,content-type"); 50 response.setCharacterEncoding("utf-8"); 51 //response.setContentType("text/html;charset=utf-8"); 52 response.setContentType("application/json;charset=utf-8"); 53 54 //得到数据库连接 55 Connection conn=DBUtil.getConnection(); 56 String sql="SELECT id AS id ,username AS 用户名 ,birthday AS 生日,sex AS 性别,address AS 地址 FROM TEST"; 57 PreparedStatement pstmt; 58 try { 59 pstmt = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY); 60 //设置每次只读取一部分数据 61 pstmt.setFetchSize(1000); 62 pstmt.setFetchDirection(ResultSet.FETCH_REVERSE); 63 ResultSet rs = pstmt.executeQuery(); 64 65 //一旦这个Workbook中数据量超过1000就会写入到磁盘中 66 SXSSFWorkbook workbook = new SXSSFWorkbook(1000); 67 Sheet sheet = (Sheet) workbook.createSheet(); 68 //设置宽度 sheet.setColumnWidth(m, “列名”.getBytes().length*2*256); 69 sheet.setColumnWidth(0, 1000); 70 sheet.setColumnWidth(1, 2000); 71 sheet.setColumnWidth(2, 3000); 72 sheet.setColumnWidth(3, 1000); 73 sheet.setColumnWidth(4, 4000); 74 workbook.setSheetName(0, "sheet1"); 75 Row row = sheet.createRow((short) 0); 76 Cell cell; 77 CellStyle cellStyle; 78 //单元格合并 79 sheet.addMergedRegion(new CellRangeAddress( 0, 0, 0, 4));//参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号 80 cell = row.createCell((short) 0); 81 cell.setCellType(Cell.CELL_TYPE_STRING); 82 cell.setCellValue(new XSSFRichTextString("数据流向")); 83 //设置背景色 84 cellStyle=workbook.createCellStyle(); 85 cellStyle.setFillForegroundColor(IndexedColors.CORAL.getIndex()); 86 cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); 87 88 cellStyle.setAlignment(CellStyle.ALIGN_CENTER); // 水平居中 89 // cellStyle.setAlignment(CellStyle.VERTICAL_CENTER); // 垂直居中 90 cell.setCellStyle(cellStyle); 91 92 /* 93 cellStyle.setBorderRight(CellStyle.BORDER_THIN);//右边框 94 cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); //下边框 95 cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框 96 cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);//上边框 97 */ 98 99 //获取有多少列 100 ResultSetMetaData md = rs.getMetaData(); 101 int nColumn = md.getColumnCount(); 102 row = sheet.createRow(1); 103 cellStyle=workbook.createCellStyle(); 104 for (int i = 1; i <= nColumn; i++) { 105 cell = row.createCell((short) (i-1)); 106 //设置数据类型Cell.CELL_TYPE_STRING Cell.CELL_TYPE_NUMERIC Cell.CELL_TYPE_FORMULA Cell.CELL_TYPE_BOOLEAN Cell.CELL_TYPE_BLANK Cell.CELL_TYPE_ERROR 107 cell.setCellType(Cell.CELL_TYPE_STRING); 108 //设置值 109 cell.setCellValue(new XSSFRichTextString(md.getColumnLabel(i))); 110 //设置背景色 111 cellStyle.setFillForegroundColor(IndexedColors.BLUE.getIndex()); 112 cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); 113 cellStyle.setAlignment(CellStyle.ALIGN_CENTER); // 水平居中 114 cell.setCellStyle(cellStyle); 115 } 116 117 int iRow = 2; 118 while (rs.next()) { 119 row = sheet.createRow(iRow); 120 for (int j = 1; j <= nColumn; j++) { 121 cell = row.createCell((short) (j - 1)); 122 cell.setCellType(Cell.CELL_TYPE_STRING); 123 if (rs.getObject(j) != null) { 124 cell.setCellValue(new XSSFRichTextString(rs.getObject(j).toString())); 125 } else { 126 cell.setCellValue(""); 127 } 128 } 129 //每1000行将数据存入硬盘 130 if (iRow % 1000 == 0) { 131 ((SXSSFSheet) sheet).flushRows(); 132 } 133 iRow++; 134 } 135 136 String filename=System.currentTimeMillis()+".xlsx"; 137 String savefile=file+filename; 138 String zipfilename=System.currentTimeMillis()+".zip"; 139 String zipsavefile=zipFile+zipfilename; 140 FileOutputStream fOut = new FileOutputStream(savefile); 141 workbook.write(fOut); 142 fOut.flush(); 143 fOut.close(); 144 145 //将文件转换成Zip文件 146 FiletoZip(savefile, zipsavefile); 147 148 //将下载路径返回给前端 149 PrintWriter out = response.getWriter(); 150 out.write("{\"StringUrl\":\""+zipfilename+"\"}"); 151 out.close(); 152 } catch (Exception e) { 153 e.printStackTrace(); 154 } 155 156 } 157 158 /** 159 * 将文件压缩成zip文件 160 * @param file 文件路径 161 * @param zipFile 压缩后的文件路径 162 * @throws IOException 163 * @throws FileNotFoundException 164 */ 165 private void FiletoZip(String file, String zipFile) throws IOException,FileNotFoundException { 166 File f = new File(file); 167 File ff = new File(zipFile); 168 if (!f.exists()) { 169 ff.createNewFile(); 170 } 171 FileInputStream in = new FileInputStream(file); 172 FileOutputStream out = new FileOutputStream(zipFile); 173 ZipOutputStream zipOut = new ZipOutputStream(out); 174 String fileName = file.substring(file.lastIndexOf('/') + 1, file.length()); 175 // 创建指向压缩原始文件的入流 176 ZipEntry entry = new ZipEntry(fileName); 177 zipOut.putNextEntry(entry); 178 // 向压缩文件中输出数据 179 int number = 0; 180 byte[] buffer = new byte[512]; 181 while ((number = in.read(buffer)) != -1) { 182 zipOut.write(buffer, 0, number); 183 } 184 zipOut.close(); 185 out.close(); 186 in.close(); 187 188 // f.delete(); 189 } 190 191 /** 192 * 设置跨越 193 */ 194 @Override 195 protected void doOptions(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { 196 resp.setHeader("Access-Control-Allow-Credentials", "false"); 197 resp.setHeader("Access-Control-Allow-Origin", "*"); 198 resp.setHeader("Access-Control-Allow-Methods", "*"); 199 resp.setHeader("Access-Control-Allow-Headers", "x-requested-with,content-type"); 200 resp.setContentType("application/json"); 201 resp.setCharacterEncoding("utf-8"); 202 super.doOptions(req, resp); 203 } 204 }
注意：如果数据超过1048575条会报下面的错误
本项目使用的jar包和源码下载链接：http://download.csdn.net/detail/u013865056/9908167
