Java poi Excel下载

Java Excel下载

通过查数据库表，获得数据，然后将数据插入Excel表后下载

通过三种方法简洁明了说明了具体步骤

大家需要哪个看哪个方法就行，创建Excel的时候为了好看我设置了一些东西，如果不需要可以直接删掉

创建新的Excel表 createExcel方法

向Excel表中添加数据 addOobExcel方法

下载Excel downExcel方法

尬包：

import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.poi.hssf.usermodel.HSSFPalette;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.IndexedColors;import org.apache.poi.ss.usermodel.Row;

代码：

public class asd {public String newFileName = null;public File newFileDir = null;public String toFilePath = null;public static final String[] columnName = { "列一(选填)", "列二(必填)", "列三(选填)" };/** * 将数据库中的数据导出Excel * @param textString 数据库查出的数据 * @return */public void downDevice(List<Student> textString){//Excel下载的路径,为tomcat下项目中的class文件下，也可自己定义路径//the result save:/D:/tomcat8/tomcat/webapps/CIS/WEB-INF/classes/Excel_1509157574761.xlsString path = Thread.currentThread().getContextClassLoader ().getResource("").getPath();//以当前时间为新的Excel文件名称this.newFileName = "Excel_" + System.currentTimeMillis() + ".xls";//Excel保存路径this.toFilePath = path + newFileName;// 如果文件夹不存在，就创建文件夹。newFileDir = new File(toFilePath);if (!(newFileDir.exists()) && !(newFileDir.isDirectory())){boolean creadok = newFileDir.mkdirs();if (!creadok){System.out.println("Failed to create dir: " + toFilePath);}}System.out.println("the result save:" + toFilePath);// 创建新的Excel表createExcel(newFileDir.getAbsolutePath(), toFilePath, newFileName, columnName);// 向Excel表中添加数据addOobExcel(toFilePath, newFileName, textString);// 下载ExceldownExcel(toFilePath);}/** * 创建Excel表 * @param targetFilePath  * @param toFilePath Excel保存路径 * @param sheetName Excel名称 * @param titleRow Excel列名 */@SuppressWarnings("resource")public void createExcel(String targetFilePath, String toFilePath, String sheetName, String titleRow[]){System.out.println("create new Excel");// 创建workbookHSSFWorkbook workbook = new HSSFWorkbook();// 添加Worksheet（不添加sheet时生成的xls文件打开时会报错)HSSFSheet sheet1 = workbook.createSheet(sheetName);FileOutputStream fo = null;BufferedOutputStream out = null;File file = new File(targetFilePath);if (!file.exists()){System.out.println("file：" + targetFilePath + "not exit");return;}if (new File(toFilePath).exists()){new File(toFilePath).delete();}try{fo = new FileOutputStream(toFilePath);out = new BufferedOutputStream(fo);// 添加表头Row row = sheet1.createRow(0); // 创建第一行for (int i = 0; i < titleRow.length; i++){String string = titleRow[i];if (string.contains("选填")){HSSFCellStyle style = workbook.createCellStyle();//设置字体加粗HSSFFont font = workbook.createFont();//粗体显示font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//设置字体格式font.setFontName("宋体");style.setFont(font);// 设置背景颜色style.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());style.setFillPattern(CellStyle.SOLID_FOREGROUND);//自定义RGBHSSFPalette customPalette = workbook.getCustomPalette();customPalette.setColorAtIndex(IndexedColors.LIGHT_YELLOW.getIndex(),(byte) 255, (byte) 230, (byte) 153);// 设置列宽sheet1.setColumnWidth(i, 9000);// 创建第i列HSSFCell cell = (HSSFCell) row.createCell(i);cell.setCellValue(titleRow[i]);//让设置生效cell.setCellStyle(style);} else{HSSFCellStyle style = workbook.createCellStyle();//设置字体加粗HSSFFont font = workbook.createFont();//粗体显示font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//设置字体格式font.setFontName("宋体");style.setFont(font);// 设置背景颜色style.setFillForegroundColor(IndexedColors.SEA_GREEN.getIndex());style.setFillPattern(CellStyle.SOLID_FOREGROUND);//自定义RGBHSSFPalette customPalette = workbook.getCustomPalette();customPalette.setColorAtIndex(IndexedColors.SEA_GREEN.getIndex(), (byte) 112,(byte) 173, (byte) 71);// 设置列宽sheet1.setColumnWidth(i, 9000);// 创建第i列HSSFCell cell = (HSSFCell) row.createCell(i);cell.setCellValue(titleRow[i]);//让设置生效cell.setCellStyle(style);}}fo = new FileOutputStream(toFilePath);out = new BufferedOutputStream(fo);workbook.write(out);} catch (Exception e){System.out.println(e);} finally{try{out.flush();} catch (IOException e){System.out.println(e);}}}/** * 向Excel表中添加数据 * @param toFilePath Excel保存路径 * @param sheetName Excel名称 * @param excelOobBeanLst Excel保存的内容 */public static void addOobExcel(String toFilePath, String sheetName, List<Student> Students){System.out.println("Insert Information to NewOobExcel begin");// 创建workbookFile file = new File(toFilePath);HSSFWorkbook workbook = null;try{workbook = new HSSFWorkbook(new FileInputStream(file));} catch (FileNotFoundException e){System.out.println(e);} catch (IOException e){System.out.println(e);}// 流FileOutputStream out = null;HSSFSheet sheet = workbook.getSheet(sheetName);// 获取表格的总行数 并加一 为最新一行的数据插入点int rowCount = sheet.getLastRowNum() + 1;// 获取表头的列数int columnCount = sheet.getRow(0).getLastCellNum();try{//传过来实体类的话有多少个属性sb.append多少次for (Student s : Students){// 获得每列数据StringBuffer sb = new StringBuffer();//尽量用不常见的符号分割，我用逗号分割出了奇怪BUGsb.append(s.getName() + "~");sb.append(s.getAge() + "~");sb.append(s.getSex() + "~");//防止向Excel添加"null"值String oob = sb.toString().replaceAll("null", "");String[] split = oob.split("~", -1);// 最新要添加的一行Row row = sheet.createRow(rowCount);for (int i = 0; i < columnCount; i++){org.apache.poi.ss.usermodel.Cell cell = row.createCell(i);// 每列内容写入cell.setCellValue(split[i]);}//行数加一rowCount += 1;}out = new FileOutputStream(toFilePath);workbook.write(out);} catch (Exception e){System.out.println(e);} finally{try{out.close();System.out.println("addExcel end");} catch (IOException e){System.out.println(e);}}}/** * 下载Excel * @return */public String downExcel(String toFilePath) {System.out.println(" start downFile");HttpServletResponse response = ServletActionContext.getResponse();System.out.println(" start Excel path:"+toFilePath);File file = new File(toFilePath);String name = file.getName();/* 如果文件存在 */if (file.exists()) {System.out.println(" start down Excel Name:"+name);String disName = null;try {disName = URLEncoder.encode(name, "UTF-8");} catch (UnsupportedEncodingException e1) {e1.printStackTrace();}response.reset();response.setContentType("application/x-msdownload");response.addHeader("Content-Disposition", "attachment; filename=\""+ disName + "\"");InputStream inStream = null;ServletOutputStream servletOS = null;try {inStream = new FileInputStream(file);byte[] buf = new byte[4096];/* 创建输出流 */System.out.println(" get OutputStream");try {servletOS = response.getOutputStream();} catch (IOException e) {e.printStackTrace();}int readLength;while (((readLength = inStream.read(buf)) != -1)) {servletOS.write(buf, 0, readLength);}} catch (FileNotFoundException e1) {e1.printStackTrace();} catch (IOException e) {e.printStackTrace();} finally {try {inStream.close();servletOS.flush();servletOS.close();} catch (IOException e) {e.printStackTrace();}}System.out.println(" end downFile");}return null;}}