import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
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.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.neusoft.dcd.css.common.BaseAction;
import com.neusoft.dcd.css.model.management.user.UserBaseInfo;
import com.neusoft.dcd.css.model.tsc.Tsc;
import com.neusoft.dcd.css.service.tsc.TscService;
import com.opensymphony.xwork2.Preparable;
public class ExportExcel {
private static final long serialVersionUID = 1L;
private TscService cssTscService;
public void prepare() throws Exception {
}
/**
* 绘出Excel
*
* @param actionMapping
* @param actionForm
* @param request
* @param response
* @return
* @throws Exception
*/
public String init() throws Exception {
UserBaseInfo userinfo = (UserBaseInfo) ServletActionContext
.getRequest().getSession().getAttribute("UserBaseInfo");
if(userinfo!=null){
HttpServletResponse response = ServletActionContext.getResponse();
List dataList=cssTscService.queryForProTsclb(userinfo.getUserId());
HSSFWorkbook workbook = exportExcel(dataList);
if(workbook!= null){
this.printExcel(workbook,response,"ExcelTsc.xls");
}
}
return SUCCESS;
}
// 导出Excel
private void printExcel(HSSFWorkbook workbook,HttpServletResponse response, String string) throws IOException {
OutputStream out = response.getOutputStream();
response.setHeader("Content-disposition","attachment; filename=" + "TSC.xls");
response.setContentType("application/msexcel;charset=UTF-8");
workbook.write(out);
out.flush();
out.close();
}
public HSSFWorkbook exportExcel(List dataList) throws Exception
{
HSSFWorkbook workbook = null;
try
{
// 这里的数据即时你要从后台取得的数据
// 创建工作簿实例
workbook = new HSSFWorkbook();
// 创建工作表实例
HSSFSheet sheet = workbook.createSheet("TscExcel");
// 设置列宽
this.setSheetColumnWidth(sheet);
// 获取样式
HSSFCellStyle style = this.createTitleStyle(workbook);
//
if (dataList != null && dataList.size() > 0)
{
// 创建第一行标题,标题名字的本地信息通过resources从资源文件中获取
HSSFRow row = sheet.createRow((short) 0);// 建立新行
this.createCell(row, 0, style, HSSFCell.CELL_TYPE_STRING,
this.getText("css.tsc.asktime"));
this.createCell(row, 1, style, HSSFCell.CELL_TYPE_STRING,
this.getText("css.tsc.productname"));
this.createCell(row, 2, style, HSSFCell.CELL_TYPE_STRING,
this.getText("css.tsc.passsort"));
this.createCell(row, 3, style, HSSFCell.CELL_TYPE_STRING,
this.getText("css.tsc.askusername"));
this.createCell(row, 4, style, HSSFCell.CELL_TYPE_STRING,
this.getText("css.tsc.dtype"));
this.createCell(row, 5, style, HSSFCell.CELL_TYPE_STRING,
this.getText("css.tsc.askone"));
this.createCell(row, 6, style, HSSFCell.CELL_TYPE_STRING,
this.getText("css.tsc.department"));
this.createCell(row, 7, style, HSSFCell.CELL_TYPE_STRING,
this.getText("css.tsc.requesttime"));
this.createCell(row, 8, style, HSSFCell.CELL_TYPE_STRING,
this.getText("css.tsc.answertime"));
this.createCell(row, 9, style, HSSFCell.CELL_TYPE_STRING,
this.getText("css.tsc.requesttow"));
// 给excel填充数据
for(int i=0;i<dataList.size();i++)
{
// 将dataList里面的数据取出来,假设这里取出来的是Model,也就是某个javaBean的意思啦
Tsc model= (Tsc)dataList.get(i);
HSSFRow row1 = sheet.createRow((short) (i + 1));// 建立新行
if(model.getAsktime()!=null)
this.createCell(row1, 0, style, HSSFCell.CELL_TYPE_STRING,
model.getAsktime());
if(model.getProductname()!=null)
this.createCell(row1, 1, style, HSSFCell.CELL_TYPE_STRING,
model.getProductname());
if(model.getPasssort()!=null)
this.createCell(row1, 2, style, HSSFCell.CELL_TYPE_STRING,
model.getPasssort());
if(model.getAskusername()!=null)
this.createCell(row1, 3, style, HSSFCell.CELL_TYPE_STRING,
model.getAskusername());
if(model.getDtype()!=null)
this.createCell(row1, 4, style, HSSFCell.CELL_TYPE_STRING,
model.getDtype());
if(model.getAskone()!=null)
this.createCell(row1, 5, style, HSSFCell.CELL_TYPE_STRING,
model.getAskone());
if(model.getDepartment()!=null)
this.createCell(row1, 6, style, HSSFCell.CELL_TYPE_STRING,
model.getDepartment());
if(model.getRequesttime()!=null)
this.createCell(row1, 7, style, HSSFCell.CELL_TYPE_STRING,
model.getRequesttime());
if(model.getAnswertime()!=null)
this.createCell(row1, 8, style, HSSFCell.CELL_TYPE_STRING,
model.getAnswertime());
if(model.getRequesttow()!=null)
this.createCell(row1, 9, style, HSSFCell.CELL_TYPE_STRING,
model.getRequesttow());
}
}
else {
this.createCell(sheet.createRow(0), 0, style,
HSSFCell.CELL_TYPE_STRING, "查无资料");
}
}catch(Exception e)
{
e.printStackTrace();
}
return workbook;
}
private void setSheetColumnWidth(HSSFSheet sheet)
{
// 根据你数据里面的记录有多少列,就设置多少列
sheet.setColumnWidth((short) 0, (short) 3000);
sheet.setColumnWidth((short) 1, (short) 3000);
sheet.setColumnWidth((short) 2, (short) 3000);
sheet.setColumnWidth((short) 3, (short) 3000);
sheet.setColumnWidth((short) 4, (short) 5000);
sheet.setColumnWidth((short) 5, (short) 5000);
sheet.setColumnWidth((short) 6, (short) 5000);
sheet.setColumnWidth((short) 7, (short) 5000);
sheet.setColumnWidth((short) 8, (short) 5000);
sheet.setColumnWidth((short) 9, (short) 5000);
}
// 设置excel的title样式
private HSSFCellStyle createTitleStyle(HSSFWorkbook wb) {
HSSFFont boldFont = wb.createFont();
boldFont.setFontHeight((short) 200);
HSSFCellStyle style = wb.createCellStyle();
style.setFont(boldFont);
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("###,##0.00"));
return style;
}
// 创建Excel单元格
private void createCell(HSSFRow row, int column, HSSFCellStyle style,
int cellType,Object value) {
HSSFCell cell = row.createCell((short) column);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
if (style != null) {
cell.setCellStyle(style);
}
switch (cellType) {
case HSSFCell.CELL_TYPE_BLANK: {
}
break;
case HSSFCell.CELL_TYPE_STRING: {
cell.setCellValue(value.toString());
}
break;
case HSSFCell.CELL_TYPE_NUMERIC: {
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
// DecimalFormat format = new DecimalFormat("###,##0.00");
// cell.setCellValue(Float.parseFloat(value.toString()));
cell.setCellValue(Double.parseDouble(value.toString()));
}
break;
default:
break;
}
}
public void setCssTscService(TscService cssTscService) {
this.cssTscService = cssTscService;
}
}