1、pom引入poi的maven依赖
<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.15</version></dependency>
2、单元格的创建,设置样式和赋值
package com.yl;import java.io.FileOutputStream;import java.io.IOException;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.util.CellRangeAddress;@SuppressWarnings("deprecation")public class Book1 {public static void main(String[] args) {HSSFWorkbook workbook = new HSSFWorkbook(); // 创建一个excelHSSFSheet sheet = workbook.createSheet("sheet1");// 新建sheet页HSSFCellStyle cellStyle = workbook.createCellStyle(); // 新建单元格样式cellStyle.setFillForegroundColor((short) 13);// 设置背景色cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中cellStyle.setWrapText(true);// 设置自动换行HSSFRow titleRow = sheet.createRow(0);CellRangeAddress cra1 = new CellRangeAddress(0, 0, 0, 5);sheet.addMergedRegion(cra1);titleRow.createCell(0).setCellValue("haha1");CellRangeAddress cra2 = new CellRangeAddress(0, 0, 6, 11);sheet.addMergedRegion(cra2);titleRow.createCell(6).setCellValue("haha2");CellRangeAddress cra3 = new CellRangeAddress(0, 0, 12, 17);sheet.addMergedRegion(cra3);titleRow.createCell(12).setCellValue("haha3");CellRangeAddress cra4 = new CellRangeAddress(0, 0, 18, 23);sheet.addMergedRegion(cra4);titleRow.createCell(18).setCellValue("haha4");CellRangeAddress cra5 = new CellRangeAddress(0, 0, 24, 29);sheet.addMergedRegion(cra5);titleRow.createCell(24).setCellValue("haha5");HSSFRow row = sheet.createRow(1);// 参数1:起始行 参数2:终止行 参数3:起始列 参数4:终止列CellRangeAddress region1 = new CellRangeAddress(1, 5, 0, 5);sheet.addMergedRegion(region1);HSSFCell cellB1 = row.createCell(0);cellB1.setCellValue("B1");CellRangeAddress region2 = new CellRangeAddress(1, 5, 6, 11);sheet.addMergedRegion(region2);// HSSFRow row1 = sheet.createRow(0);//不要再重新创建行数了,这样会把之前填充的数据删除掉!HSSFCell cellB2 = row.createCell(6);cellB2.setCellValue("B2");CellRangeAddress region3 = new CellRangeAddress(1, 5, 12, 17);sheet.addMergedRegion(region3);// HSSFRow row1 = sheet.createRow(0);//不要再重新创建行数了,这样会把之前填充的数据删除掉!HSSFCell cellB3 = row.createCell(12);cellB3.setCellValue("B3");CellRangeAddress region4 = new CellRangeAddress(1, 5, 18, 23);sheet.addMergedRegion(region4);// HSSFRow row1 = sheet.createRow(0);//不要再重新创建行数了,这样会把之前填充的数据删除掉!HSSFCell cellB4 = row.createCell(18);cellB4.setCellValue("B4");// 输出到本地String excelName = "myExcel.xls";FileOutputStream out = null;try {out = new FileOutputStream(excelName);workbook.write(out);out.flush();out.close();} catch (Exception e) {e.printStackTrace();} finally {if (out != null)try {out.close();} catch (IOException e) {e.printStackTrace();}out = null;System.out.println("导出成功");}}}
3、将文件在网页上导出:
// POI导出流文件下载public static void downloadExcel(HSSFWorkbook workbook, HttpServletResponse response, String filename)throws IOException {try {OutputStream out = response.getOutputStream();response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8"));response.setContentType("application/msexcel;charset=UTF-8");workbook.write(out);out.flush();out.close();} catch (IOException e) {e.printStackTrace();}}