导出excel

庆涛1992

关注

原创最新推荐文章于 2026-10-08 10:31:05 发布 315 阅读 AI 写同款· GEO 优化 ›

VB导出EXCEL表从"工程"菜单中选择"引用"栏；选择Microsoft Excel 14 .0 Object Library，然后选择"确定"。表示在工程中要引用EXCEL类型库。然后就是实现从MSHFlexGrid控件中将数据导出到EXCEL表中（注意：MSHFlexGrid控件是从第0行，第0列开始的，而EXCEL却是从第1行，第1列开始的）。代码如下： Dim i As Lo 阅读详情

/**
* 分公司营业日报表导出excel by hu_qtao 2018/04/02
*
* @param page
* @return
* @throws Exception
*/
@RequestMapping(value = "/yyrbbExcel1")
public ModelAndView yyrbbExcel1() throws Exception {
ModelAndView mv = this.getModelAndView();
PageData pd = new PageData();
pd = this.getPageData();
String user_id = Tools.getUserId();
pd.put("user_id",user_id);
List<PageData> dept = tjbbService.getDeptByUser(pd);//通过登录人获取所属机构
String today = Tools.date2Str_01(new Date());
String mdbh = dept.get(0).getString("jgid");
String ksrq = pd.getString("ksrq")==null||pd.getString("ksrq")==""?today:pd.getString("ksrq");//判断页面是否空放默认值
String jsrq = pd.getString("jsrq")==null||pd.getString("jsrq")==""?today:pd.getString("jsrq");
String jgid = pd.getString("mdbh")==null||pd.getString("mdbh")==""?mdbh:pd.getString("mdbh");
pd.put("ksrq",ksrq);
pd.put("jsrq",jsrq);
pd.put("jgid",jgid);
List<Yyrbb> yyrbb = tjbbService.getYyrbb(pd);//报表基础数据
Yyrbb ryrbbSum = tjbbService.getYyrbbAdd(pd);//通过基础数据计算的数据
Map<String, Object> dataMap = new HashMap<String, Object>();
dataMap.put("filename", "分公司营业日报表("+ksrq+"--"+jsrq+")");
List<String> titles = new ArrayList<String>();
titles.add("类型");
titles.add("笔数(次)"); //1
titles.add("服务费(元)"); //2
titles.add("笔数(次)"); //3
titles.add("利息(%)"); //4
titles.add("服务费(元)"); //5
titles.add("逾期笔数"); //6
titles.add("逾期费(元)"); //7
titles.add("笔数(次)"); //8
titles.add("利息(%)"); //9
titles.add("逾期笔数(次)"); //10
titles.add("逾期费(元)"); //11
titles.add("金额小计(元)"); //12
titles.add("笔数(次)");//13
titles.add("金额(元)");//14
dataMap.put("titles", titles);
List<PageData> varList = new ArrayList<PageData>();
for (int i = 0; i < yyrbb.size(); i++) {
PageData vpd = new PageData();
vpd.put("var1", yyrbb.get(i).getDplm_bm()); //1
vpd.put("var2", yyrbb.get(i).getJdbs()!=null?yyrbb.get(i).getJdbs():"0"); //2
vpd.put("var3", yyrbb.get(i).getJdfwf()!=null?yyrbb.get(i).getJdfwf():"0.00"); //3
vpd.put("var4", yyrbb.get(i).getXdbs()!=null?yyrbb.get(i).getXdbs():"0"); //4
vpd.put("var5", yyrbb.get(i).getXdlx()!=null?yyrbb.get(i).getXdlx():"0.00"); //5
vpd.put("var6", yyrbb.get(i).getXdfwf()!=null?yyrbb.get(i).getXdfwf():"0.00"); //6
vpd.put("var7", yyrbb.get(i).getXdyqfbs()!=null?yyrbb.get(i).getXdyqfbs():"0"); //7
vpd.put("var8", yyrbb.get(i).getXdyqfzh()!=null?yyrbb.get(i).getXdyqfzh():"0.00"); //8
vpd.put("var9", yyrbb.get(i).getShbs()!=null?yyrbb.get(i).getShbs():"0");//9
vpd.put("var10", yyrbb.get(i).getShlx()!=null?yyrbb.get(i).getShlx():"0.00");//10
vpd.put("var11", yyrbb.get(i).getShyqfbs()!=null?yyrbb.get(i).getShyqfbs():"0");//11
vpd.put("var12", yyrbb.get(i).getShyqfzh()!=null?yyrbb.get(i).getShyqfzh():"0.00");//12
vpd.put("var13", yyrbb.get(i).getJexj()!=null?yyrbb.get(i).getJexj():"0.00");//13
vpd.put("var14", yyrbb.get(i).getTxfbs()!=null?yyrbb.get(i).getTxfbs():"0");//14
vpd.put("var15", yyrbb.get(i).getTxf()!=null?yyrbb.get(i).getTxf():"0.00");//15
varList.add(vpd);
}
PageData vpdSum = new PageData();
vpdSum.put("var1", "合计"); //1
if(ryrbbSum==null){
vpdSum.put("var2", "0"); //2
vpdSum.put("var3", "0.00"); //3
vpdSum.put("var4", "0"); //4
vpdSum.put("var5", "0.00"); //5
vpdSum.put("var6", "0.00"); //6
vpdSum.put("var7", "0"); //7
vpdSum.put("var8", "0.00"); //8
vpdSum.put("var9", "0");//9
vpdSum.put("var10", "0.00");//10
vpdSum.put("var11", "0");//11
vpdSum.put("var12", "0.00");//12
vpdSum.put("var13", "0.00");//13
vpdSum.put("var14", "0");//14
vpdSum.put("var15", "0.00");//15
}else{
vpdSum.put("var2", ryrbbSum.getJdbsSum()!=null?ryrbbSum.getJdbsSum():"0"); //2
vpdSum.put("var3", ryrbbSum.getJdfwfSum()!=null?ryrbbSum.getJdfwfSum():"0.00"); //3
vpdSum.put("var4", ryrbbSum.getXdbsSum()!=null?ryrbbSum.getXdbsSum():"0"); //4
vpdSum.put("var5", ryrbbSum.getXdlxSum()!=null?ryrbbSum.getXdlxSum():"0.00"); //5
vpdSum.put("var6", ryrbbSum.getXdfwfSum()!=null?ryrbbSum.getXdfwfSum():"0.00"); //6
vpdSum.put("var7", ryrbbSum.getXdyqfbsSum()!=null?ryrbbSum.getXdyqfbsSum():"0"); //7
vpdSum.put("var8", ryrbbSum.getXdyqfzhSum()!=null?ryrbbSum.getXdyqfzhSum():"0.00"); //8
vpdSum.put("var9", ryrbbSum.getShbsSum()!=null?ryrbbSum.getShbsSum():"0");//9
vpdSum.put("var10", ryrbbSum.getShlxSum()!=null?ryrbbSum.getShlxSum():"0.00");//10
vpdSum.put("var11", ryrbbSum.getShyqfbsSum()!=null?ryrbbSum.getShyqfbsSum():"0");//11
vpdSum.put("var12", ryrbbSum.getShyqfzhSum()!=null?ryrbbSum.getShyqfzhSum():"0.00");//12
vpdSum.put("var13", ryrbbSum.getJexjSum()!=null?ryrbbSum.getJexjSum():"0.00");//13
vpdSum.put("var14", ryrbbSum.getTxfbsSum()!=null?ryrbbSum.getTxfbsSum():"0");//14
vpdSum.put("var15", ryrbbSum.getTxfSum()!=null?ryrbbSum.getTxfSum():"0.00");//15
}
varList.add(vpdSum);
dataMap.put("varList", varList);
ObjectExcelViewYyrbb erv = new ObjectExcelViewYyrbb(); //执行excel操作设计excel样式
mv = new ModelAndView(erv, dataMap);
return mv;

}

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.poi.ss.util.CellRangeAddress;
import org.springframework.web.servlet.view.document.AbstractExcelView;

/**
* 导入到EXCEL 类名称：ObjectExcelView.java 类描述：
*
* @author brtpawn 作者单位：联系方式：
* @version 1.0
*/
public class ObjectExcelViewYyrbb extends AbstractExcelView {

@Override
protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception {
// TODO Auto-generated method stub
Date date = new Date();
String filename = "";
if(model.get("filename") != null && !"".equals(model.get("filename"))){
filename = (String) model.get("filename");
}else{
filename = Tools.date2Str(date, "yyyyMMddHHmmss");
}

HSSFSheet sheet;
HSSFCell cell;
response.setContentType("application/octet-stream");
response.setContentType("application/octet-stream;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename="
+ new String(filename.getBytes(),"iso-8859-1") + ".xls");
sheet = workbook.createSheet("sheet1");
List<String> titles = (List<String>) model.get("titles");
int len = titles.size();
HSSFCellStyle headerStyle = workbook.createCellStyle(); //标题样式
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
HSSFFont headerFont = workbook.createFont(); //标题字体
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerFont.setFontHeightInPoints((short) 11);
headerStyle.setFont(headerFont);
short width = 20, height = 25 * 20;
sheet.setDefaultColumnWidth(width);
// 单元格合并
// 四个参数分别是：起始行，起始列，结束行，结束列
CellRangeAddress callRangeAddress = new CellRangeAddress(0,1,0,0);//起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress1 = new CellRangeAddress(0,0,1,2);//起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress2 = new CellRangeAddress(0,0,3,7);//起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress3 = new CellRangeAddress(0,0,8,11);//起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress4 = new CellRangeAddress(0,1,12,12);//起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress5 = new CellRangeAddress(0,0,13,14);//起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress6 = new CellRangeAddress(1,1,1,1);//起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress7 = new CellRangeAddress(1,1,2,2);//起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress8 = new CellRangeAddress(1,1,3,3);//起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress9 = new CellRangeAddress(1,1,4,4);//起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress10 = new CellRangeAddress(1,1,5,5);//起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress11 = new CellRangeAddress(1,1,6,6);//起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress12 = new CellRangeAddress(1,1,7,7);//起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress13 = new CellRangeAddress(1,1,8,8);//起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress14 = new CellRangeAddress(1,1,9,9);//起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress15 = new CellRangeAddress(1,1,10,10);//起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress16 = new CellRangeAddress(1,1,11,11);//起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress17 = new CellRangeAddress(1,1,12,12);//起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress18 = new CellRangeAddress(1,1,13,13);//起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress19 = new CellRangeAddress(1,1,14,14);//起始行,结束行,起始列,结束列
sheet.addMergedRegion(callRangeAddress);
sheet.addMergedRegion(callRangeAddress1);
sheet.addMergedRegion(callRangeAddress2);
sheet.addMergedRegion(callRangeAddress3);
sheet.addMergedRegion(callRangeAddress4);
sheet.addMergedRegion(callRangeAddress5);
sheet.addMergedRegion(callRangeAddress6);
sheet.addMergedRegion(callRangeAddress7);
sheet.addMergedRegion(callRangeAddress8);
sheet.addMergedRegion(callRangeAddress9);
sheet.addMergedRegion(callRangeAddress10);
sheet.addMergedRegion(callRangeAddress11);
sheet.addMergedRegion(callRangeAddress12);
sheet.addMergedRegion(callRangeAddress13);
sheet.addMergedRegion(callRangeAddress14);
sheet.addMergedRegion(callRangeAddress15);
sheet.addMergedRegion(callRangeAddress16);
sheet.addMergedRegion(callRangeAddress17);
sheet.addMergedRegion(callRangeAddress18);
sheet.addMergedRegion(callRangeAddress19);
HSSFRow row0 = sheet.createRow(0);
String[] titles0 = {"类型","建当笔数","","续当收入","","","","","赎回收入","","","","金额小计(元）","退息费",""};
for(int i=0;i<titles0.length;i++)
{
HSSFCell cell2 = row0.createCell(i);
//加载单元格样式
cell2.setCellStyle(headerStyle);
cell2.setCellValue(titles0[i]);
}
HSSFRow row1 = sheet.createRow(1);
String[] titles1 = {"","笔数","服务费","笔数","利息","服务费","逾期笔数","逾期费","笔数","利息","逾期笔数","逾期费","","笔数","金额"};
for(int i=0;i<titles1.length;i++)
{
HSSFCell cell2 = row1.createCell(i);
//加载单元格样式
cell2.setCellStyle(headerStyle);
cell2.setCellValue(titles1[i]);
}
sheet.getRow(0).setHeight(height);

HSSFCellStyle contentStyle = workbook.createCellStyle(); //内容样式
contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
List<PageData> varList = (List<PageData>) model.get("varList");
int varCount = varList.size();
for (int i = 0; i < varCount; i++) {
PageData vpd = varList.get(i);
for (int j = 0; j < len; j++) {
String varstr = vpd.getString("var" + (j + 1)) != null ? vpd.getString("var" + (j + 1)) : "";
cell = getCell(sheet, i + 2, j);
cell.setCellStyle(contentStyle);
setText(cell, varstr);
}

}

}