POI导出EXCEL
//网上代码下面是自己代码
import java.awt.Color; import java.io.File; import java.io.FileOutputStream; import org.apache.commons.io.FileUtils; 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.HSSFRow; 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.ClientAnchor; import org.apache.poi.ss.usermodel.CreationHelper; import org.apache.poi.ss.usermodel.Drawing; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.util.CellRangeAddress; public final class TestExportExcel { public static void main(String[] args) throws Exception { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("table"); //创建table工作薄 Object[][] datas = {{"区域产品销售额","",""},{"区域", "总销售额(万元)", "总利润(万元)简单的表格"}, {"江苏省" , 9045, 2256}, {"广东省", 3000, 690}}; HSSFRow row; HSSFCell cell; short colorIndex = 10; HSSFPalette palette = wb.getCustomPalette(); Color rgb = Color.GREEN; short bgIndex = colorIndex ++; palette.setColorAtIndex(bgIndex, (byte) rgb.getRed(), (byte) rgb.getGreen(), (byte) rgb.getBlue()); short bdIndex = colorIndex ++; rgb = Color.BLACK; palette.setColorAtIndex(bdIndex, (byte) rgb.getRed(), (byte) rgb.getGreen(), (byte) rgb.getBlue()); for(int i = 0; i < datas.length; i++) { row = sheet.createRow(i);//创建表格行 for(int j = 0; j < datas[i].length; j++) { cell = row.createCell(j);//根据表格行创建单元格 cell.setCellValue(String.valueOf(datas[i][j])); HSSFCellStyle cellStyle = wb.createCellStyle(); if(i == 0 || i == 1) { cellStyle.setFillForegroundColor(bgIndex); //bgIndex 背景颜色下标值 cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); } cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); //bdIndex 边框颜色下标值 cellStyle.setBottomBorderColor(bdIndex); cellStyle.setLeftBorderColor(bdIndex); cellStyle.setRightBorderColor(bdIndex); cellStyle.setTopBorderColor(bdIndex); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); if(i == datas.length - 1 && j == datas[0].length - 1) { HSSFFont font = wb.createFont(); font.setItalic(true); font.setUnderline(HSSFFont.U_SINGLE); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short)14); cellStyle.setFont(font); } cell.setCellStyle(cellStyle); } } //加入图片 byte[] bt = FileUtils.readFileToByteArray(new File("/Users/mike/pie.png")); int pictureIdx = wb.addPicture(bt, Workbook.PICTURE_TYPE_PNG); CreationHelper helper = wb.getCreationHelper(); Drawing drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = helper.createClientAnchor(); anchor.setDx1(MSExcelUtil.pixel2WidthUnits(60)); anchor.setDy1(MSExcelUtil.pixel2WidthUnits(60)); anchor.setCol1(0); anchor.setRow1(4); anchor.setCol2(3); anchor.setRow2(25); drawing.createPicture(anchor, pictureIdx); //合并单元格 CellRangeAddress region = new CellRangeAddress(0, // first row 0, // last row 0, // first column 2 // last column ); sheet.addMergedRegion(region); //创建表格之后设置行高与列宽 for(int i = 0; i < datas.length; i++) { row = sheet.getRow(i); row.setHeightInPoints(30); } for(int j = 0; j < datas[0].length; j++) { sheet.setColumnWidth(j, MSExcelUtil.pixel2WidthUnits(160)); } wb.write(new FileOutputStream("/Users/mike/table6.xls")); } }
package com.bs.web.rest; import cn.hutool.core.util.ArrayUtil; import com.bs.model.TodoPM; import com.bs.service.impl.TodoPMServiceImpl; import lombok.extern.slf4j.Slf4j; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.usermodel.IndexedColors; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.util.Arrays; import java.util.List; /** * 导出excel表格 * Created by Jxy on 2019/2/27 15:11 */ @RestController @Slf4j @RequestMapping(value = "/excel") public class ExcelResource { @Autowired TodoPMServiceImpl todoPMService; @GetMapping(value = "/download") public void downloadExcel(HttpServletResponse response) throws IOException { log.info(" ~~~generator Excel table "); /* 建立excel表格 */ HSSFWorkbook hssfWorkbook = new HSSFWorkbook(); /* 建立工作簿 */ HSSFSheet sheet = hssfWorkbook.createSheet("project"); /* 读取数据库todoPM表所有信息 */ List<TodoPM> list = todoPMService.list(); /* 文件名称 */ String fileName = "信息化事业部研究所平台框架组项目跟踪一览表" + ".xls"; String gbk = new String(fileName.getBytes("gbk"), "iso8859-1"); /* 表头信息 */ String[] headers = { "项目名称", "创建人","负责人", "项目类型", "开始日期","交付日期","当前阶段","WIKI页面"}; /* 创建一行 */ HSSFRow row = sheet.createRow(0); row.setHeightInPoints(25f);//设置第一行高度 HSSFCellStyle cellStyle1 = hssfWorkbook.createCellStyle(); cellStyle1.setFillBackgroundColor(IndexedColors.LIGHT_ORANGE.getIndex()); for (int i = 0; i < headers.length; i++) { HSSFCell cell = row.createCell(i); /* 富文本字符串hssfRichTextString */ HSSFRichTextString hssfRichTextString = new HSSFRichTextString(headers[i]); /* 设置表头信息 */ cell.setCellValue(hssfRichTextString); cell.setCellStyle(cellStyle1); } /* 创建样式 格式化时间 */ HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle(); HSSFDataFormat dataFormat = hssfWorkbook.createDataFormat(); short format = dataFormat.getFormat("yyyy-m-d"); cellStyle.setDataFormat(format); /* 填入数据 */ int num = 1; for (TodoPM todoPM : list) { HSSFRow row1 = sheet.createRow(num); row1.createCell(0).setCellValue(todoPM.getProjectName()); row1.createCell(1).setCellValue(todoPM.getCreator()); row1.createCell(2).setCellValue(todoPM.getProjectHead()); row1.createCell(3).setCellValue(todoPM.getProjectType()); HSSFCell cell4 = row1.createCell(4); cell4.setCellValue(todoPM.getStartDate()); cell4.setCellStyle(cellStyle); HSSFCell cell5 = row1.createCell(5); cell5.setCellValue(todoPM.getReleaseDate()); cell5.setCellStyle(cellStyle); row1.createCell(6).setCellValue(todoPM.getStage()); row1.createCell(7).setCellValue(todoPM.getWikiUrl()); num++; } response.setContentType("application/octet-stream"); response.setHeader("Content-disposition", "attachment;filename=" + gbk); response.flushBuffer(); hssfWorkbook.write(response.getOutputStream()); hssfWorkbook.close(); } }
gogogo