Excel数据下载

最新推荐文章于 2024-02-22 22:39:00 发布

原创最新推荐文章于 2024-02-22 22:39:00 发布 · 702 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#excel

上传下载专栏收录该内容

2 篇文章

订阅专栏

本文介绍了一个实用的Excel导出工具类，该类提供了一系列方法来帮助开发者快速创建Excel文件，包括设置工作表、创建单元格及设定单元格宽度等功能。此外，还展示了如何在控制器类中使用此工具类实现数据导出。

一、ExcelUtil 制作工作表的工具类，其中包括三个方法
/**
* setExcel 制作工作表的方法
* createCell 创建单元格的放到
* setSheetColumnWidth 设定单元格行宽的方法
* @author Administrator
*
*/
public class ExcelUtil {
public void exportExcel(List list, List colList, String fileName){
try {
if(StringUtils.isEmpty(fileName)){
fileName=”test.xml”;
}
HSSFWorkbook workbook=setExcel(colList, list);
ByteArrayOutputStream output=new ByteArrayOutputStream();
workbook.write(output);
byte[] ba = output.toByteArray();
HttpServletResponse reponse = ServletActionContext.getResponse();
reponse.setCharacterEncoding(“utf-8”);
reponse.setContentType(“application/vnd.ms-excel”);
reponse.setHeader(“Content-Disposition”, “attachment; ” +
“filename=” + URLEncoder.encode(fileName, “UTF-8”));
OutputStream out = reponse.getOutputStream();
out.write(ba);
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}

@SuppressWarnings("unused")
private HSSFWorkbook setExcel(List<String> colList,List<List<String>> list){
    HSSFWorkbook workbook=new HSSFWorkbook();
    HSSFSheet sheet=workbook.createSheet("xiaojian");
    this.setSheetColumnWidth(sheet);
    HSSFCellStyle style=workbook.createCellStyle();
    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    style.setAlignment(HSSFCellStyle.VERTICAL_CENTER);
    HSSFCellStyle style_right = workbook.createCellStyle();
    style_right.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
    style_right.setAlignment(HSSFCellStyle.ALIGN_RIGHT);// 水平靠右
    HSSFCellStyle style_left = workbook.createCellStyle();
    style_left.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    style_left.setAlignment(HSSFCellStyle.ALIGN_LEFT);
    //工作表第一行
    HSSFRow row=sheet.createRow((short)0);
    for(int i=0;i<colList.size();i++){
        this.createCell(row, i, style, HSSFCell.CELL_TYPE_STRING, colList.get(i));
    }
    for(int i=0;i<list.size();i++){
        HSSFRow rowNow=sheet.createRow((short)(i+1));//数据行
        List model=list.get(i);
        for(int j=0;j<model.size();j++){
            this.createCell(rowNow, j, style, HSSFCell.CELL_TYPE_STRING, model.get(j));
        }
    }
    return workbook;

}
/**
 * 
 * @param row 工作表行
 * @param column 工作表 列
 * @param style 样式
 * @param cellType 传参类型
 * @param value 传入值
 */
@SuppressWarnings("unused")
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 == null ? "": value.toString());
        }
            break;
        case HSSFCell.CELL_TYPE_NUMERIC:
        {
            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            cell.setCellValue(Double.parseDouble(value == null ? "": value.toString()));
        }
            break;
        default:
            break;
    }

}
/**
 * 设定工作表实例的行宽
 * @param sheet
 */
@SuppressWarnings("unused")
private void setSheetColumnWidth(HSSFSheet sheet){
    sheet.setColumnWidth(0, 500);
    sheet.setColumnWidth(1, 500);
    sheet.setColumnWidth(2, 500);
    sheet.setColumnWidth(3, 500);   
}

}
二、控制器类，采用注解的方式纳入spring管理；List ls=new ArrayList()如果将元素加入到ls中时出现强制转换，可以将List ls=new ArrayList()；这样可以解决bean类中不同属性类型的一系列问题
package zte.com.action;
@Controller
@Scope(“prototype”)
public class exportCostAction {
private CostService costServiceImpl;
public CostService getCostServiceImpl() {
return costServiceImpl;
}
@Autowired
public void setCostServiceImpl(CostService costServiceImpl) {
this.costServiceImpl = costServiceImpl;
}
public String exportCost(){
String nameTitle=”资费列表”;
List < List> list=new ArrayList< List>();
List colList=new ArrayList();
colList.add(“资费ID”);
colList.add(“资费名称”);
colList.add(“基本时长”);
colList.add(“基本费用”);
colList.add(“单位费用”);
colList.add(“创建时间”);
colList.add(“开通时间”);
colList.add(“状态”);
List costs=costServiceImpl.findAll();
for(Cost c:costs){
List ls=new ArrayList();
ls.add(c.getId().toString());
ls.add(c.getName());
ls.add(c.getBaseDuration().toString());
ls.add(c.getBaseCost().toString());
ls.add(c.getUnitCost().toString());
ls.add(c.getStatus());
list.add(ls);
}
excelUtil.exportExcel(list, colList, nameTitle+”.xls”);
return “excel”;
}
}