excel 导出

以你之姓@ 2022-07-18 01:56 228阅读 0赞

package com.euc.qy.sn.util;  
  
  
import java.io.FileNotFoundException;  
import java.io.FileOutputStream;  
import java.io.IOException;  
import java.io.OutputStream;  
import java.util.ArrayList;  
import java.util.Collection;  
import java.util.Date;  
import java.util.Iterator;  
import java.util.List;  
import java.util.regex.Matcher;  
import java.util.regex.Pattern;  
  
  
import javax.swing.JOptionPane;  
  
  
import org.apache.poi.hssf.usermodel.HSSFCell;  
import org.apache.poi.hssf.usermodel.HSSFCellStyle;  
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;  
import org.apache.poi.hssf.usermodel.HSSFComment;  
import org.apache.poi.hssf.usermodel.HSSFFont;  
import org.apache.poi.hssf.usermodel.HSSFPatriarch;  
import org.apache.poi.hssf.usermodel.HSSFRichTextString;  
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.HSSFColor;  
  
  
import com.artery.util.GeneralUtility;  
  
  
public class ExportExcel \{  
HSSFWorkbook workbook = new HSSFWorkbook();  
String\[\] headers;  
Collection<Object\[\]> dataset;  
int headerRows = 1;  
public ExportExcel() \{  
  
\}  
public void exportExcelHeader(HSSFSheet sheet) \{  
// 设置表格默认列宽度为15个字节  
sheet.setDefaultColumnWidth( 15);  
// 生成一个样式  
HSSFCellStyle style = workbook.createCellStyle();  
// 设置这些样式  
style.setFillForegroundColor(HSSFColor.SKY\_BLUE.index);  
style.setFillPattern(HSSFCellStyle.SOLID\_FOREGROUND);  
style.setBorderBottom(HSSFCellStyle.BORDER\_THIN);  
style.setBorderLeft(HSSFCellStyle.BORDER\_THIN);  
style.setBorderRight(HSSFCellStyle.BORDER\_THIN);  
style.setBorderTop(HSSFCellStyle.BORDER\_THIN);  
style.setAlignment(HSSFCellStyle.ALIGN\_CENTER);  
// 生成一个字体  
HSSFFont font = workbook.createFont();  
font.setColor(HSSFColor.VIOLET.index);  
font.setFontHeightInPoints((short) 12);  
font.setBoldweight(HSSFFont.BOLDWEIGHT\_BOLD);  
// 把字体应用到当前的样式  
style.setFont(font);  
  
// 产生表格标题行  
HSSFRow row = sheet.createRow(0);  
for (int i = 0; i < headers.length; i++) \{  
row.createCell(i, HSSFCell.CELL\_TYPE\_STRING);  
HSSFCell cell = row.createCell(i);  
cell.setCellStyle(style);  
HSSFRichTextString text = new HSSFRichTextString(headers\[i\]);  
cell.setCellValue(text);  
\}  
headerRows = 1;  
  
  
\}  
  
public void exportExcelBody(HSSFSheet sheet) \{  
  
// 生成并设置另一个样式  
HSSFCellStyle style2 = workbook.createCellStyle();  
style2.setFillForegroundColor(HSSFColor.LIGHT\_YELLOW.index);  
style2.setFillPattern(HSSFCellStyle.SOLID\_FOREGROUND);  
style2.setBorderBottom(HSSFCellStyle.BORDER\_THIN);  
style2.setBorderLeft(HSSFCellStyle.BORDER\_THIN);  
style2.setBorderRight(HSSFCellStyle.BORDER\_THIN);  
style2.setBorderTop(HSSFCellStyle.BORDER\_THIN);  
style2.setAlignment(HSSFCellStyle.ALIGN\_CENTER);  
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL\_CENTER);  
// 生成另一个字体  
HSSFFont font2 = workbook.createFont();  
font2.setBoldweight(HSSFFont.BOLDWEIGHT\_NORMAL);  
// 把字体应用到当前的样式  
style2.setFont(font2);  
  
  
// 声明一个画图的顶级管理器  
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();  
// 定义注释的大小和位置,详见文档  
HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5));  
// 设置注释内容  
comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));  
// 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.  
comment.setAuthor("leno");  
  
  
// 产生表格标题行  
  
  
// 遍历集合数据，产生数据行  
Iterator<Object\[\]> it = dataset.iterator();  
int index = headerRows;  
while (it.hasNext()) \{  
HSSFRow row = sheet.createRow(index++);  
Object\[\] t = it.next();  
// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值  
for (int i = 0; i < t.length; i++) \{  
HSSFCell cell = row.createCell(i);  
cell.setCellStyle(style2);  
Object value = t\[i\];  
if (value == null) \{  
value = "";  
\}  
try \{  
  
  
// 判断值的类型后进行强制类型转换  
String textValue = null;  
  
if (value instanceof Boolean) \{  
boolean bValue = (Boolean) value;  
textValue = "有";  
if (!bValue) \{  
textValue = "无";  
\}  
\} else if (value instanceof Date) \{  
Date date = (Date) value;  
textValue =。。。。。。。。。。。。。;  
\} else if (value instanceof byte\[\]) \{  
// 有图片时，设置行高为60px;  
row.setHeightInPoints(60);  
// 设置图片所在列宽度为80px,注意这里单位的一个换算  
sheet.setColumnWidth(i, (short) (35.7 \* 80));  
// sheet.autoSizeColumn(i);  
byte\[\] bsValue = (byte\[\]) value;  
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 6, index, (short) 6, index);  
anchor.setAnchorType(2);  
patriarch.createPicture(anchor, workbook.addPicture(bsValue, HSSFWorkbook.PICTURE\_TYPE\_JPEG));  
\} else \{  
// 其它数据类型都当作字符串简单处理  
textValue = value.toString();  
\}  
// 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成  
if (textValue != null) \{  
Pattern p = Pattern.compile("^//d+(//.//d+)?$");  
Matcher matcher = p.matcher(textValue);  
if (matcher.matches()) \{  
// 是数字当作double处理  
cell.setCellValue(Double.parseDouble(textValue));  
\} else \{  
HSSFRichTextString richString = new HSSFRichTextString(textValue);  
HSSFFont font3 = workbook.createFont();  
font3.setColor(HSSFColor.BLUE.index);  
richString.applyFont(font3);  
cell.setCellValue(richString);  
\}  
\}  
\} catch (SecurityException e) \{  
e.printStackTrace();  
\} catch (IllegalArgumentException e) \{  
e.printStackTrace();  
\} finally \{  
// 清理资源  
\}  
\}  
\}  
\}  
  
  
  
public void exportExcel(String title, String\[\] headers, Collection<Object\[\]> dataset, OutputStream out) \{  
// 声明一个工作薄  
workbook = new HSSFWorkbook();  
// 生成一个表格  
HSSFSheet sheet = workbook.createSheet(title);  
// 设置表格默认列宽度为15个字节  
sheet.setDefaultColumnWidth( 15);  
this.headers = headers;  
this.dataset = dataset;  
exportExcelHeader(sheet);  
exportExcelBody(sheet);  
try \{  
workbook.write(out);  
\} catch (IOException e) \{  
e.printStackTrace();  
\}  
\}  
  
  
public static void main(String\[\] args) \{  
// 测试学生  
ExportExcel ex = new ExportExcel();  
String\[\] headers = \{ "学号", "姓名", "年龄", "性别", "出生日期" \};  
List<Object\[\]> dataset = new ArrayList<Object\[\]>();  
dataset.add(new Object\[\]\{10000001, "张三", 20, true, new java.util.Date()\});  
dataset.add(new Object\[\]\{20000002, "李四", 24, false, new Date()\});  
dataset.add(new Object\[\]\{30000003, "王五", 22, true, new Date()\});  
  
try \{  
  
  
  
OutputStream out = new FileOutputStream("E://a.xls");  
ex.exportExcel("学生表格",headers, dataset, out);  
out.close();  
JOptionPane.showMessageDialog(null, "导出成功!");  
System.out.println("excel导出成功！");  
\} catch (FileNotFoundException e) \{  
e.printStackTrace();  
\} catch (IOException e) \{  
e.printStackTrace();  
\}  
\}  
\}