java poi excel 下载官网,Java使用Poi进行excel的文件导出，下载

灰太狼 2023-01-15 08:23 198阅读 0赞

Java使用Poi进行excel的文件导出，下载

先贴代码后讲解

导出方法1(确认已经有可导出的模板)

例如：

No:

$\{no\}

确认日期:

$\{date\}

确认人:

$\{p\}

import org.apache.poi.ss.usermodel.Workbook;

/\*\*

\* @param sheetList

\* 传入sheetList(sheet名称)

\* @param dataList

\* 传入导入数据List

\* @param in 文件输入流

\* @return Workbook 返回创建好的文件，在上层直接用此写入OutputStream 导出文件即可

\* @throws InvalidFormatException

\* @throws ParsePropertyException

\*/

public static Workbook exportCertificate( @NotNull ArrayListsheetList,@NotNull CertificateVo certificateVo,@NotNull InputStream in) throws ParsePropertyException, InvalidFormatException \{

// 判断数值是否为正常数值，否则返回

if (templateFileType < 0 && templateFileType > 8 && sheetList.size() < 0

&& certificateVo!=null &&in!= null) \{

return null;

HashMapintoExcelMap = new HashMap();

// 所有excel都有的三个参数 No，时间，人名 ，

intoExcelMap.put("no", certificateVo.getNo());

intoExcelMap.put("date", certificateVo.getDate());

intoExcelMap.put("p", certificateVo.getPersonName());

//读取传入in中的模版并创建一个副本

XLSTransformer transformer = new XLSTransformer();

return transformer.transformXLS(in, intoExcelMap);

以上代码为抽象出去的工具类，代码中都有对应的注释。

这里把参数传入excel 中有2个步骤

1 .需要在excel 中输入$\{key\};

2. 使用poi 的中的XLSTransformer.transformXLS(InputStream is, Map beanParams)方法，此方法第一个参数是传入读取的模板输入流，第二个为hashMap，hashMap的key 为你在excel 表格中输入的key值，value 为你要传入参数的值。

transformer还有其他的方法，例如transformMultipleSheetsList转换多列表格

transformer.transformMultipleSheetsList(in, dataList, sheetList, "List", new HashMap(),0);

以下为2个方法的注释，大家可自行做相对应的理解

/\*\*

\* Creates Workbook instance based on .xls template from a given InputStream and a number of beans

\* @param is xls InputStream with required

\* @param beanParams Beans in a map under keys used in .xls template to access to the beans

\* @return new \{@link org.apache.poi.ss.usermodel.Workbook\} generated by inserting beans into corresponding excel template

\* @throws net.sf.jxls.exception.ParsePropertyException if there were any problems in evaluating specified property value from a bean

\*/

transformXLS(InputStream is, Map beanParams)

/\*\*

\* This method transforms given XLS input stream template into multiple sheets workbook

\* creating separate Excel worksheet for every object in the list

\* @param is - \{@link InputStream\} for source XLS template file

\* @param objects - List of beans where each list item should be exported into a separated worksheet

\* @param newSheetNames - Sheet names to be used for newly created worksheets

\* @param beanName - Bean name to be used for a list item when processing sheet

\* @param beanParams - Common bean map containing all other objects to be used in the workbook

\* @param startSheetNum - Worksheet number (zero based) of the worksheet that needs to be used to create multiple worksheets

\* @return new \{@link org.apache.poi.ss.usermodel.Workbook\} object containing the result of transformation

\* @throws net.sf.jxls.exception.ParsePropertyException - \{@link ParsePropertyException\} is thrown when some property can't be parsed

\*/

transformMultipleSheetsList(InputStream is, List objects, List newSheetNames, String beanName, Map beanParams, int startSheetNum)

作为输出流传出下载

@RequestMapping(value = "exportCertificate")

public void export(HttpServletRequest request, HttpServletResponse response) \{

// 测试使用

String templateFileName = request.getSession().getServletContext().getRealPath("/")

\+ "/static/xls/test.xls";

// 测试证书名称

String cName = "test";

// sheet问卷名称

ArrayListsheetList = new ArrayList();

sheetList.add(cName);

// 导出文件文件名称 yyyy-MM-dd hh:mm:ss不使用：原因，windows 文件不能用：命名

String exportFileName = cName + new SimpleDateFormat("yyyyMMddhhmmss").format(new Date()) + ".xls";

CertificateVo certificateVo = new CertificateVo();

certificateVo.setNo("45123456789375");

certificateVo.setPersonName("test");

certificateVo.setDate(new SimpleDateFormat("yyyy-MM-dd").format(new Date()));

// 导出输出流

OutputStream out = null;

// 读取输入流

InputStream in = null;

// 定义流

try \{

// 读取输入流

in = new BufferedInputStream(new FileInputStream(templateFileName));

out = response.getOutputStream();

Workbook worktab = ZtpUtil.exportCertificate( sheetList, certificateVo, in);

// 设置响应

// 服务端要求客户端以下载文件的方式打开该文件

// 设置文件可以为中文名称

response.setHeader("Content-Disposition",

"attachment;filename=" + new String(exportFileName.getBytes("utf-8"), "ISO-8859-1"));

// 设置数据类型

response.setContentType("application/vnd.ms-excel");

worktab.write(out);

\} catch (ParsePropertyException e) \{

e.printStackTrace();

\} catch (InvalidFormatException e) \{

e.printStackTrace();

\} catch (FileNotFoundException e) \{

e.printStackTrace();

\} catch (Exception e) \{

e.printStackTrace();

\} finally \{

try \{

out.flush();

if (in != null) \{

in.close();

if (out != null) \{

out.close();

\} catch (IOException e) \{

e.printStackTrace();

这是在服务端使用的部分代码，如果错误异常，自己需要做相对应的处理。

导出方法2

自己生成excel

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("table");// 创建table工作薄

short colorIndex = 10;

HSSFPalette palette = wb.getCustomPalette();// 自定义颜色

Color rgb = Color.RED;

short bgIndex = colorIndex++; // 背景颜色下标值

palette.setColorAtIndex(bgIndex, (byte) rgb.getRed(), (byte) rgb.getGreen(), (byte) rgb.getBlue());

short bdIndex = colorIndex++; // 边框颜色下标值

palette.setColorAtIndex(bdIndex, (byte) rgb.getRed(), (byte) rgb.getGreen(), (byte) rgb.getBlue());

short fontColorIndex = colorIndex++; // 文字的颜色

rgb = Color.WHITE;

palette.setColorAtIndex(fontColorIndex, (byte) rgb.getRed(), (byte) rgb.getGreen(), (byte) rgb.getBlue());

List> parentlist = new ArrayList>();

ArrayListchildList = new ArrayList();

childList.add("证明");

ArrayListchildList2 = new ArrayList();

childList2.add("No:");

childList2.add("12313234213");

ArrayListchildList3 = new ArrayList();

childList3.add("日期:");

SimpleDateFormat f = new SimpleDateFormat("yyyy 年 MM 月 dd 日");

childList3.add(f.format(new Date()));

ArrayListchildList4 = new ArrayList();

childList4.add("人员:");

childList4.add("test");

parentlist.add(childList);

parentlist.add(childList2);

parentlist.add(childList3);

parentlist.add(childList4);

// 创建表格行列

HSSFRow row;

HSSFCell cell;

for (int i = 0; i < parentlist.size(); i++) \{

row = sheet.createRow(i);// 创建表格行

for (int j = 0; j < parentlist.get(i).size(); j++) \{

cell = row.createCell(j);// 根据表格行创建单元格

cell.setCellValue(parentlist.get(i).get(j));

/\*\*

\* create a new Font and add it to the workbook's font table

\* 创建新字体并将其添加到工作簿的字体表中

\*/

HSSFFont font = wb.createFont();

// xls 高级设置

HSSFCellStyle cellStyle = wb.createCellStyle();

if (i == 0) \{// 设置背景色

cellStyle.setFillForegroundColor(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);

/\*\*

\* 设置单元格的水平对齐类型 \* set the type of horizontal alignment for

\* the cell

\* @param align

\* - the type of alignment

\* @see \#ALIGN\_GENERAL

\* @see \#ALIGN\_LEFT

\* @see \#ALIGN\_CENTER

\* @see \#ALIGN\_RIGHT

\* @see \#ALIGN\_FILL

\* @see \#ALIGN\_JUSTIFY

\* @see \#ALIGN\_CENTER\_SELECTION

\*/

cellStyle.setAlignment(HSSFCellStyle.ALIGN\_CENTER);

font.setColor(fontColorIndex);// 设置文字的颜色

// int firstRow, int lastRow, int firstCol, int lastCol

CellRangeAddress region = new CellRangeAddress(0, 0, 0, 2);

// 设置合并单元格

sheet.addMergedRegion(region);

\} else \{

cellStyle.setAlignment(HSSFCellStyle.ALIGN\_LEFT);

font.setColor(bgIndex);// 设置文字的颜色

// int firstRow, int lastRow, int firstCol, int lastCol

CellRangeAddress region = new CellRangeAddress(i, i, 1, 2);

// 设置合并单元格

sheet.addMergedRegion(region);

/\*\*

\* 设置单元格的垂直对齐类型

\* @param align

\* the type of alignment

\* @see \#VERTICAL\_TOP

\* @see \#VERTICAL\_CENTER

\* @see \#VERTICAL\_BOTTOM

\* @see \#VERTICAL\_JUSTIFY

\*/

cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL\_CENTER);

cell.setCellStyle(cellStyle);

// 设置文字

cellStyle.setFont(font);

java poi excel 下载官网,Java使用Poi进行excel的文件导出，下载

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Java使用POI导出Excel

相关使用poi导出excel文件

相关 java poi excel 下载官网,Java使用Poi进行excel的文件导出，下载

相关 poi excel 下载

相关 Java POI 导出Excel

相关 Java POI导出Excel

相关使用POI导出Excel文件

相关 java POI导出excel

相关 Java POI导出excel

相关 POI导出EXCEL下载