• 2022-11-28
宇哥博客 后端开发 Java使用POI生成Excel文件,附jar包下载

Java使用POI生成Excel文件,附jar包下载

本文所用的是 POI3.16 jar包,生成 Excel2007 格式的文件。代码中包含常用的字体大小、字体颜色、背景色、宽度、行高、内容自动换行等设置,以下为完整样例代码:

package com.test;

import java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;

public class ExportExcel {

	public static SXSSFWorkbook export(List<Map<String, Object>> list){
		  SXSSFWorkbook  workBook = null;
		  List<String> cellTitle=new ArrayList<String>();//设置表头
		  cellTitle.add("No");
		  cellTitle.add("申请日期");
		  cellTitle.add("是否通过");
		  cellTitle.add("备注");
		  try {
			   workBook = new SXSSFWorkbook(10000);//创建工作薄
			   SXSSFSheet   sheet = (SXSSFSheet) workBook.createSheet("Sheet1");//Sheet名称
			 
			   XSSFFont font = (XSSFFont) workBook.createFont();
			   font.setColor(XSSFFont.COLOR_NORMAL);
			   font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);//设置字体加粗
			   font.setColor((short) 2);//设置字体为红色
			   font.setFontHeightInPoints((short) 18);//设置字体大小
			   XSSFCellStyle cellStyle = (XSSFCellStyle) workBook.createCellStyle();//创建格式
			   cellStyle.setFont(font);
			   cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			   cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			   cellStyle.setFillForegroundColor((short) 13);//设置背景色(13为黄色)
			   cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//设置填充
			   
			   SXSSFRow titleRow = (SXSSFRow) sheet.createRow((short) 0);//第一行标题
			   //设置宽度
			   sheet.setColumnWidth(0, 256 * 8);
			   sheet.setColumnWidth(1, 256 * 15);
			   sheet.setColumnWidth(2, 256 * 8);
			   sheet.setColumnWidth(3, 256 * 15);
			   
			   titleRow.setHeightInPoints(100);//设置行高
			   
				for (int i = 0, size = cellTitle.size(); i < size; i++) {
					SXSSFCell cell = (SXSSFCell) titleRow.createCell(i, 0);
					cell.setCellStyle(cellStyle);
					cell.setCellType(XSSFCell.CELL_TYPE_STRING);
					cell.setCellValue(cellTitle.get(i));
				}
				//从第二行开始写数据
				if (list != null && !list.isEmpty()) {
					for (int i = 0, size = list.size(); i < size; i++) {
						SXSSFRow row = (SXSSFRow) sheet.createRow(i + 1);
						for (int j = 0, length = cellTitle.size(); j < length; j++) {
							XSSFCellStyle style = (XSSFCellStyle) workBook.createCellStyle();//创建格式
							SXSSFCell cell = (SXSSFCell) row.createCell(j, 0);//
							cell.setCellType(XSSFCell.CELL_TYPE_STRING);//
							style.setVerticalAlignment(VerticalAlignment.CENTER);//单元格文字垂直居中
							switch (j) {
								case 0:
									cell.setCellValue( Float.parseFloat(list.get(i).get("no").toString()) );//设置成数字格式
									style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
									cell.setCellStyle(style);
									break;
								case 1:
									cell.setCellValue( list.get(i).get("date").toString() );
									style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
									cell.setCellStyle(style);
									break;
								case 2:
									cell.setCellValue( list.get(i).get("is_pass").toString() );
									style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
									cell.setCellStyle(style);
									break;
								case 3:
									cell.setCellValue( list.get(i).get("remark").toString() );
									style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
									style.setWrapText(true);//设置自动换行
									cell.setCellStyle(style);
									break;
								default:
									cell.setCellValue("");
							}
						}
					}
				}
				
			String exportFileName = "e://"+new Date().getTime() + ".xlsx";
			   File file = new File( exportFileName);
			   FileOutputStream outStream = new FileOutputStream(file);
			   workBook.write(outStream);
			   outStream.flush();
			   outStream.close();
			   System.out.println("导出文件路径:"+file.getPath());
				
		  }catch(Exception e){
		   System.out.println("异常!"+e.getMessage());
		   e.printStackTrace();
		   return null;
		  }
		return workBook;
		  
	}
	
	public static void main(String[] args) {
		
		List<Map<String, Object>> list=new ArrayList();

		Map map1=new HashMap();
		map1.put("no", "1");
		map1.put("date", "2022-09-01");
		map1.put("is_pass", "是");
		map1.put("remark", "备注备注备注备注备注备注备注备注备注备注备注1111");
		list.add(map1);
		
		Map map2=new HashMap();
		map2.put("no", "2");
		map2.put("date", "2022-09-03");
		map2.put("is_pass", "是");
		map2.put("remark", "备注备注备注备注备注备注备注备注备注备注备注22222");
		list.add(map2);
		
		Map map3=new HashMap();
		map3.put("no", "3");
		map3.put("date", "2022-09-11");
		map3.put("is_pass", "否");
		map3.put("remark", "备注备注备注备注备注备注备注备注33333333333333333");
		list.add(map3);
		
		export(list);
	}
}

生成 Excel 文件效果:

图 | Excel文件

更多

相关文章:

Java POI写Excel控制列宽 http://www.ygbks.com/1100.html

POI相关jar包下载:https://wwz.lanzout.com/b031hfrmb (密码:8njz)

包含文件:
xmlbeans-3.1.0.jar
poi-ooxml-schemas-3.16-beta2.jar
poi-ooxml-3.16-beta2.jar
poi-3.16-beta2.jar
commons-collections4-4.1.jar

其中设置字体颜色、背景色对应色值可参考下图:

font.setColor((short) 2);//设置字体为红色
cellStyle.setFillForegroundColor((short) 13);//设置背景色(13为黄色)
图2 | 颜色对照表

“图2”转自:https://blog.csdn.net/w779050550/article/details/81094221, 包含有生成的代码;也可进入 https://blog.csdn.net/Han_Yi_To/article/details/119644992 查看。

本文来自网络,不代表本站立场,转载请注明出处。http://www.ygbks.com/3581.html

发表评论

您的电子邮箱地址不会被公开。

返回顶部