利用POI导出Excel,要导出的数据组织成List<Object[]>
需要添加POI包,xmlbeans、dom4j这两个也需要添加,不然会报错
调用的时候,要设置表头等信息,调用下面的setxxx方法就好了
package com;
import java.io.FileOutputStream;
import java.util.List;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelOperation
{
/**
* Excel导出文件夹地址
*/
private String m_sExportPath;
/**
* Excel文件的名称
*/
private String m_sFileName;
/**
* Excel文件标题
*/
private String m_sTitle;
/**
* 占的列数
*/
private int m_iColspan;
/**
* 左边提示信息
*/
private String m_sLeftTips;
/**
* 表头
*/
private List<Object[]> m_listHeader;
/**
* 构造函数
* @param sExportPath 导出文件夹地址
* @param sFileName 文件名
* @param iColspan 占得列数
*/
public ExcelOperation(String sExportPath, String sFileName, int iColspan)
{
setM_sExportPath(sExportPath);
setM_sFileName(sFileName);
setM_iColspan(iColspan - 1);
}
/**
* 导出Excel
* <功能详细描述>
* @param listHeader 列头
* @param listContent 数据内容
* @param sTitle 标题
* @param sLeftTips 左边的提示信息
* @return
* @throws Exception
* @see [类、类#方法、类#成员]
*/
public boolean ExportToExcel(List<Object[]> listHeader, List<Object[]> listContent)
{
try
{
//1.创建工作簿
Workbook wkb;
if (StringUtils.isNotEmpty(m_sFileName) && m_sFileName.endsWith("xls"))
{
//HSSFWorkbook是用来导出Excel2003格式的
wkb = new HSSFWorkbook();
}
else if (m_sFileName.endsWith("xlsx"))
{
//XSSFWorkbook是用来导出Excel2007格式的
wkb = new XSSFWorkbook();
}
else
{
return false;
}
//2.创建页
Sheet sheet = wkb.createSheet();
//设置单元格格式 居中
CellStyle cellStyle = wkb.createCellStyle();
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
//设置标题
if (StringUtils.isNotEmpty(m_sTitle))
{
Row titleRow = sheet.createRow(0);
//这里是合并单元格,四个参数代表 (开始行,结束行,开始列,结束列)
CellRangeAddress range = new CellRangeAddress(0, 0, 0, m_iColspan);
sheet.addMergedRegion(range);
Cell cell = titleRow.createCell(0);
cell.setCellStyle(cellStyle);
cell.setCellValue(m_sTitle);
}
//设置左边提示信息
if (StringUtils.isNotEmpty(m_sLeftTips))
{
Row leftRow = sheet.createRow(1);
leftRow.createCell(0).setCellValue(m_sLeftTips);
}
//设置列头
if (listHeader != null && listHeader.size() > 0)
{
for (int i = 0; i < listHeader.size(); i++)
{
Row headerRow = sheet.createRow(2 + i);
Object[] oArrayHeader = listHeader.get(i);
int j = 0;
for (Object oHeader : oArrayHeader)
{
oHeader = oHeader != null ? oHeader : "";
headerRow.createCell(0 + j).setCellValue(oHeader.toString());
j++;
}
}
}
//设置内容
if (listContent != null && listContent.size() > 0)
{
//正文数据起始行
int iContentStartRow = 0;
iContentStartRow = listHeader == null ? 2 : listHeader.size() + 2;
for (int i = 0; i < listContent.size(); i++)
{
Row contentRow = sheet.createRow(iContentStartRow + i);
Object[] oArrayContent = listContent.get(i);
for (int j = 0; j < oArrayContent.length; j++)
{
Object oContent = oArrayContent[j] != null ? oArrayContent[j] : "";
contentRow.createCell(j).setCellValue(oContent.toString());
}
}
}
//将文件存到指定位置
FileOutputStream outputStream = new FileOutputStream(m_sExportPath + "\\" + m_sFileName);
wkb.write(outputStream);
outputStream.close();
return true;
}
catch (Exception e)
{
return false;
}
}
public String getM_sFileName()
{
return m_sFileName;
}
public void setM_sFileName(String m_sFileName)
{
this.m_sFileName = m_sFileName;
}
public String getM_sExportPath()
{
return m_sExportPath;
}
public void setM_sExportPath(String m_sExportPath)
{
this.m_sExportPath = m_sExportPath;
}
public String getM_sTitle()
{
return m_sTitle;
}
public void setM_sTitle(String m_sTitle)
{
this.m_sTitle = m_sTitle;
}
public String getM_sLeftTips()
{
return m_sLeftTips;
}
public void setM_sLeftTips(String m_sLeftTips)
{
this.m_sLeftTips = m_sLeftTips;
}
public List<Object[]> getM_listHeader()
{
return m_listHeader;
}
public void setM_listHeader(List<Object[]> m_listHeader)
{
this.m_listHeader = m_listHeader;
}
public int getM_iColspan()
{
return m_iColspan;
}
public void setM_iColspan(int m_iColspan)
{
this.m_iColspan = m_iColspan;
}
}