poi 操作excel

action:

package com.sanyuan.activity.arrange.action;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


import javax.annotation.Resource;


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.springframework.stereotype.Controller;


import com.sanyuan.activity.BaseAction;
import com.sanyuan.activity.arrange.service.ExportExcelService;
import com.sanyuan.activity.model.vo.ActivityWorksExportView;


@Controller
public class CreateWorksExcelAction extends BaseAction{

private InputStream excelFile;
@SuppressWarnings("unused")
private String downloadFileName;
@Resource
private ExportExcelService exportExcelService;
public void setExportExcelService(ExportExcelService exportExcelService){
this.exportExcelService=exportExcelService;
}
public ExportExcelService  getExportExcelService(){
return exportExcelService;
}
public String  export() throws Exception {
List<ActivityWorksExportView> dataList = this.exportExcelService.getExportViewList();
HSSFWorkbook workbook = exportExcel(dataList);
ByteArrayOutputStream output = new ByteArrayOutputStream();
workbook.write(output);
byte[] ba = output.toByteArray();
excelFile = new ByteArrayInputStream(ba);
output.flush();
output.close();
return "excel";
}

public HSSFWorkbook exportExcel(List<ActivityWorksExportView> dataList) throws Exception {
HSSFWorkbook workbook = null;
try {
// 这里的数据即时你要从后台取得的数据
// 创建工作簿实例
workbook = new HSSFWorkbook();
List<String> citys= new ArrayList<String>();
citys.add("无锡市");
citys.add("苏州市");
citys.add("常州市");
citys.add("扬州市");
citys.add("南通市");
citys.add("泰州市");
citys.add("淮安市");
citys.add("宿迁市");
citys.add("镇江市");
citys.add("徐州市");
citys.add("盐城市");
citys.add("连云港市");
citys.add("南京市");
for(int j=0;j<citys.size();j++){
// 创建工作表实例
HSSFSheet sheet = workbook.createSheet(citys.get(j));
// 设置列宽
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.createCell(row, 1, style, HSSFCell.CELL_TYPE_STRING,"作品科目");
this.createCell(row, 2, style, HSSFCell.CELL_TYPE_STRING,"作品名称");
this.createCell(row, 3, style, HSSFCell.CELL_TYPE_STRING, "学校");
this.createCell(row, 4, style, HSSFCell.CELL_TYPE_STRING,"第一作者姓名");
this.createCell(row, 5, style, HSSFCell.CELL_TYPE_STRING,"第一作者身份证");
this.createCell(row, 6, style, HSSFCell.CELL_TYPE_STRING,"第二作者姓名");
this.createCell(row, 7, style, HSSFCell.CELL_TYPE_STRING,"第二作者身份证");
this.createCell(row, 8, style, HSSFCell.CELL_TYPE_STRING,"作品文件存储目录");
// 给excel填充数据
List<ActivityWorksExportView> newList=new ArrayList<ActivityWorksExportView>();
for (int i = 0; i < dataList.size(); i++) {
// 将dataList里面的数据取出来，假设这里取出来的是Model,也就是某个javaBean的意思啦
ActivityWorksExportView model1 = (ActivityWorksExportView) dataList.get(i);
String path = model1.getSavePath();

if(path.substring(0, path.indexOf("市")+1).equals(citys.get(j))){
newList.add(model1);
}
}

for(int g=0;g<newList.size();g++){
ActivityWorksExportView model =newList.get(g);
HSSFRow row1 = sheet.createRow((short) (g + 1));// 建立新行
this.createCell(row1, 0, style, HSSFCell.CELL_TYPE_STRING,
g + 1);
if (model.getSubjectName() != null)
this.createCell(row1, 1, style,HSSFCell.CELL_TYPE_STRING,model.getSubjectName());
if (model.getWorksName() != null)
this.createCell(row1, 2, style,HSSFCell.CELL_TYPE_STRING, model.getWorksName());
if (model.getSchoolName() != null)
this.createCell(row1, 3, style,HSSFCell.CELL_TYPE_STRING,model.getSchoolName());
if (model.getFirstAuthor() != null)
this.createCell(row1, 4, style,HSSFCell.CELL_TYPE_STRING,model.getFirstAuthor());
if (model.getFirstAuthorIdNo() != null)
this.createCell(row1, 5, style,HSSFCell.CELL_TYPE_STRING,model.getFirstAuthorIdNo());
if (model.getSecondAuthor() != null)
this.createCell(row1, 6, style,HSSFCell.CELL_TYPE_STRING,model.getSecondAuthor());
if (model.getSecondAuthorInNo() != null)
this.createCell(row1, 7, style,HSSFCell.CELL_TYPE_STRING,model.getSecondAuthorInNo());
if (model.getSavePath() != null)
this.createCell(row1, 8, style,HSSFCell.CELL_TYPE_STRING, model.getSavePath());
}
newList.clear();
} 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(0, 1500);
sheet.setColumnWidth(1, 3500);
sheet.setColumnWidth(2, 7000);
sheet.setColumnWidth(3, 7000);
sheet.setColumnWidth(4, 3000);
sheet.setColumnWidth(5, 5000);
sheet.setColumnWidth(6, 3000);
sheet.setColumnWidth(7, 5000);
sheet.setColumnWidth(8, 10000);
}


// 设置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(column);
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);
cell.setCellValue(Double.parseDouble(value.toString()));
}
break;
default:
break;
}
}
public String getDownloadFileName() {
SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");


String downloadFileName = (sf.format(new Date()).toString()) + ".xls";


try {
downloadFileName = new String(downloadFileName.getBytes(),


"ISO8859-1");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
return downloadFileName;
}


public void setDownloadFileName(String downloadFileName) {
this.downloadFileName = downloadFileName;
}


public InputStream getExcelFile() {
return excelFile;
}


public void setExcelFile(InputStream excelFile) {
this.excelFile = excelFile;

}

｝

xml

<action name="exportExcel" class="createWorksExcelAction" method="export">
<result name="excel" type="stream">  
                <param name="contentType">application/vnd.ms-excel</param>  
                <param name="contentDisposition">attachment;filename="${downloadFileName}"</param>  
                <param name="bufferSize">1024</param>
                 <param name="inputName">excelFile</param>  
            </result>  
</action>