本文主要是针对在Action中创建Excel表格,一种是直接将文件写入指定的路径,另一种是弹出直接打开还是下载,并可以选择保存路径的提示
第一步:导入poi包
第二步:编写代码
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.struts2.ServletActionContext;
import com.opensymphony.xwork2.ActionSupport;
import com.redoor.da.facade.intf.ReportTemandhumInfoFacade;
import com.redoor.da.module.daservicemgr.vo.DaTemandhhumInfoVO;
public class CreateSimpleExcelToDisk extends ActionSupport{
private ReportTemandhumInfoFacade reportTemandhumInfoFacade;
private DaTemandhhumInfoVO temandhumInfo = new DaTemandhhumInfoVO();
public String exportReport() {
HttpServletRequest request = ServletActionContext.getRequest();
String tableName = request.getParameter("type"); //从页面中传过来的值
//第一步,创建一个webbook,对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
//第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet("历史数据监测表");
//第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow row = sheet.createRow((int)0);
row.createCell((short)0).setCellValue(tableName); //第一行标题
HSSFRow row1 = sheet.createRow((int)1);
//第四步,创建单元格,并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //创建一个居中格式
HSSFCell cell = row1.createCell((short)0);
cell.setCellValue("温度"); cell.setCellStyle(style);
cell = row1.createCell((short)1);
cell.setCellValue("湿度"); cell.setCellStyle(style);
cell = row1.createCell((short)2);
cell.setCellValue("风速"); cell.setCellStyle(style);
cell = row1.createCell((short)3);
cell.setCellValue("太阳辐射"); cell.setCellStyle(style);
cell = row1.createCell((short)4);
cell.setCellValue("采集时间"); cell.setCellStyle(style);
//第五步,写入实体数据 实际应用中这些数据从数据库得到
List<DaTemandhhumInfoVO> daTemandhhumInfoVOslist = reportTemandhumInfoFacade.queryTemandhhumHisLineInfo(temandhumInfo);
for (int i = 0; i < daTemandhhumInfoVOslist.size(); i++)
{
row1 = sheet.createRow((int)i+2);
DaTemandhhumInfoVO info = daTemandhhumInfoVOslist.get(i);
//第四步,创建单元格,并设置值
row1.createCell((short)0).setCellValue(info.getTemperature());
row1.createCell((short)1).setCellValue(info.getHumidity());
row1.createCell((short)2).setCellValue(info.getWindvalue());
row1.createCell((short)3).setCellValue(info.getSunvalue());
row1.createCell((short)4).setCellValue(info.getCollecttime());
cell = row1.createCell((short)4);
cell.setCellValue(info.getCollecttime());
}
// 第六步,将文件存到指定位置
// try
// {
// FileOutputStream fout = new FileOutputStream("E:/"+tableName+"监测表.xls");
// wb.write(fout);
// fout.close();
// } catch (Exception e)
// {
// e.printStackTrace();
// }
// 弹出直接打开或者下载(可以选择文件下载保存路径)
response.reset();
response.setCharacterEncoding("UTF-8");
response.setContentType("application/vnd.ms-excel"); //保证不乱码
try
{
response.setHeader("Content-Disposition","attachment;" + " filename=" + new String(tableName.getBytes(), "ISO-8859-1"));
}
catch (UnsupportedEncodingException e1)
{
// TODO Auto-generated catch block
e1.printStackTrace();
}
try
{
ByteArrayOutputStream oss =new ByteArrayOutputStream();
OutputStream os = response.getOutputStream();
wb.write(oss);
byte temp[] = oss.toByteArray();
ByteArrayInputStream in = new ByteArrayInputStream(temp);
int n = 0;
while ((n = in.read(temp)) >0) {
os.write(temp, 0, n);
}
os.flush();
os.close();
} catch(Exception e)
{
e.printStackTrace();
}
return null; //这边返回值设置为null,否则可能会报错getOutputStream() has already been called for this response
}
public ReportTemandhumInfoFacade getReportTemandhumInfoFacade()
{
return reportTemandhumInfoFacade;
}
public void setReportTemandhumInfoFacade(ReportTemandhumInfoFacade reportTemandhumInfoFacade)
{
this.reportTemandhumInfoFacade = reportTemandhumInfoFacade;
}
public DaTemandhhumInfoVO getTemandhumInfo()
{
return temandhumInfo;
}
public void setTemandhumInfo(DaTemandhhumInfoVO temandhumInfo)
{
this.temandhumInfo = temandhumInfo;
}
}
第三步:在web.xml中配置一下,添加下面几行代码
<mime-mapping>
<extension>xls</extension>
<mime-type>application/vnd.ms-excel</mime-type>
</mime-mapping>