导包：
jar地址：
https://pan.baidu.com/s/1RtejTpZTfbkbCh1h7hKGqg
主要工具类：
package com.tcwl.vsmp.loanmgt.utils;
import java.io.BufferedOutputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import com.tcwl.vsmp.loanmgt.common.LoanConstant;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
/**
*
* @author Administrator
*
*/
public class LoanExcelUtil
{
//
/**
* 创建excel工作簿
*/
private static Workbook wb = null;
/**
*
*/
private static int hh = 0;
/**
* 借款查询导出（） {申请类型}
*/
public static final String[] BUYGOODS = LoanConstant.BUYGOODS;
/**
* 借款查询导出（）
* @param list list
* @param keys 健
* @param columnNames 值
* @return 结果
*/
public static Workbook createWorkBookJinXin(List<Map<String, Object>> list,
String[] keys, String[] columnNames)
{
// 创建excel工作簿
wb = new HSSFWorkbook();
long allDataSize = (list.size() - 1);
long mus = 60000;
long avg = allDataSize / mus;
long mod = allDataSize % mus;
avg = mod == 0 ? avg : avg + 1;
for (int si = 0; si < avg; si++)
{
// 创建第一个sheet（页），并命名
Sheet sheet = wb.createSheet(
list.get(0).get("sheetName").toString() + "_" + si);
// HSSFCellStyle setBorder = (HSSFCellStyle) wb.createCellStyle();
// 设置背景色：
// setBorder.setFillForegroundColor((short) 13);// 设置背景色
// setBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
for (int i = 0; i < keys.length; i++)
{
sheet.setColumnWidth((short) i, (short) (35.7 * 150));
}
// 创建两种单元格格式
CellStyle cs = wb.createCellStyle();
CellStyle cs2 = wb.createCellStyle();
// 创建两种字体
Font f = wb.createFont();
Font f2 = wb.createFont();
// 创建第一种字体样式（用于列名）
f.setFontHeightInPoints((short) 10);
f.setColor(IndexedColors.BLACK.getIndex());
f.setBoldweight(Font.BOLDWEIGHT_BOLD);
// 创建第二种字体样式（用于值）
f2.setFontHeightInPoints((short) 10);
f2.setColor(IndexedColors.BLACK.getIndex());
// Font f3=wb.createFont();
// f3.setFontHeightInPoints((short) 10);
// f3.setColor(IndexedColors.RED.getIndex());
// 设置第一种单元格的样式（用于列名）
cs.setFont(f);
cs.setBorderLeft(CellStyle.BORDER_THIN);
cs.setBorderRight(CellStyle.BORDER_THIN);
cs.setBorderTop(CellStyle.BORDER_THIN);
cs.setBorderBottom(CellStyle.BORDER_THIN);
cs.setAlignment(CellStyle.ALIGN_CENTER);
cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER); // 设置垂直对齐方式
// 单元格背景色
cs.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
cs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// 设置第二种单元格的样式（用于值）
cs2.setFont(f2);
cs2.setBorderLeft(CellStyle.BORDER_THIN);
cs2.setBorderRight(CellStyle.BORDER_THIN);
cs2.setBorderTop(CellStyle.BORDER_THIN);
cs2.setBorderBottom(CellStyle.BORDER_THIN);
cs2.setAlignment(CellStyle.ALIGN_CENTER);
// ============================表头行begin================================//
// 创建第一行
Row header1 = sheet.createRow((short) 0);
// ============================表头行end==================================//
// 设置列名
if (columnNames.length <= 0)
{
hh = 0;
for (int i = 0; i < columnNames.length; i++)
{
Cell cell = header1.createCell(i);
cell.setCellValue(columnNames[i]);
cell.setCellStyle(cs);
}
}
else
{
hh = 0;
// 创建第二行表头
Row header2 = sheet.createRow((short) 1);
// 表头 第一行
for (int i = 0; i < columnNames.length; i++)
{
if (columnNames[i] != "")
{
Cell cell = header1.createCell(i);
cell.setCellValue(columnNames[i]);
cell.setCellStyle(cs);
}
}
}
int beginNum = Integer.valueOf(Long.valueOf(si * mus).toString());
// 设置每行每列的值
for (int i = 1; i <= mus + 1; i++)
{
if (i + beginNum > allDataSize)
{
break;
}
// Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
// 创建一行，在页sheet上
Row row1 = sheet.createRow(i + hh);
// 在row行上创建一个方格
for (short j = 0; j < keys.length; j++)
{
Cell cell = row1.createCell(j);
if("buyGoods_Id".equals(keys[j])){
cell.setCellValue( BUYGOODS[Integer.parseInt(
list.get(i + beginNum).get(keys[j]) == null ? " "
: list.get(i + beginNum).get(keys[j]).toString())
]);
cell.setCellStyle(cs2);
}else{
cell.setCellValue(
list.get(i + beginNum).get(keys[j]) == null ? " "
: list.get(i + beginNum).get(keys[j])
.toString());
cell.setCellStyle(cs2);
}
}
}
}
return wb;
}
/**
*
* @param list list
* @param keys 健
* @param columnNames 值
* @return 结果
*/
public static Workbook createWorkBook(List<Map<String, Object>> list,
String[] keys, String[] columnNames)
{
// 创建excel工作簿
wb = new HSSFWorkbook();
long allDataSize = (list.size() - 1);
long mus = 60000;
long avg = allDataSize / mus;
long mod = allDataSize % mus;
avg = mod == 0 ? avg : avg + 1;
for (int si = 0; si < avg; si++)
{
// 创建第一个sheet（页），并命名
Sheet sheet = wb.createSheet(
list.get(0).get("sheetName").toString() + "_" + si);
// HSSFCellStyle setBorder = (HSSFCellStyle) wb.createCellStyle();
// 设置背景色：
// setBorder.setFillForegroundColor((short) 13);// 设置背景色
// setBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
for (int i = 0; i < keys.length; i++)
{
sheet.setColumnWidth((short) i, (short) (35.7 * 150));
}
// 创建两种单元格格式
CellStyle cs = wb.createCellStyle();
CellStyle cs2 = wb.createCellStyle();
// 创建两种字体
Font f = wb.createFont();
Font f2 = wb.createFont();
// 创建第一种字体样式（用于列名）
f.setFontHeightInPoints((short) 10);
f.setColor(IndexedColors.BLACK.getIndex());
f.setBoldweight(Font.BOLDWEIGHT_BOLD);
// 创建第二种字体样式（用于值）
f2.setFontHeightInPoints((short) 10);
f2.setColor(IndexedColors.BLACK.getIndex());
// Font f3=wb.createFont();
// f3.setFontHeightInPoints((short) 10);
// f3.setColor(IndexedColors.RED.getIndex());
// 设置第一种单元格的样式（用于列名）
cs.setFont(f);
cs.setBorderLeft(CellStyle.BORDER_THIN);
cs.setBorderRight(CellStyle.BORDER_THIN);
cs.setBorderTop(CellStyle.BORDER_THIN);
cs.setBorderBottom(CellStyle.BORDER_THIN);
cs.setAlignment(CellStyle.ALIGN_CENTER);
cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER); // 设置垂直对齐方式
// 单元格背景色
cs.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
cs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// 设置第二种单元格的样式（用于值）
cs2.setFont(f2);
cs2.setBorderLeft(CellStyle.BORDER_THIN);
cs2.setBorderRight(CellStyle.BORDER_THIN);
cs2.setBorderTop(CellStyle.BORDER_THIN);
cs2.setBorderBottom(CellStyle.BORDER_THIN);
cs2.setAlignment(CellStyle.ALIGN_CENTER);
// ============================表头行begin================================//
// 创建第一行
Row header1 = sheet.createRow((short) 0);
// ============================表头行end==================================//
// 设置列名
if (columnNames.length <= 0)
{
hh = 0;
for (int i = 0; i < columnNames.length; i++)
{
Cell cell = header1.createCell(i);
cell.setCellValue(columnNames[i]);
cell.setCellStyle(cs);
}
}
else
{
hh = 0;
// 创建第二行表头
Row header2 = sheet.createRow((short) 1);
// 表头 第一行
for (int i = 0; i < columnNames.length; i++)
{
if (columnNames[i] != "")
{
Cell cell = header1.createCell(i);
cell.setCellValue(columnNames[i]);
cell.setCellStyle(cs);
}
}
}
int beginNum = Integer.valueOf(Long.valueOf(si * mus).toString());
// 设置每行每列的值
for (int i = 1; i <= mus + 1; i++)
{
if (i + beginNum > allDataSize)
{
break;
}
// Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
// 创建一行，在页sheet上
Row row1 = sheet.createRow(i + hh);
// 在row行上创建一个方格
for (short j = 0; j < keys.length; j++)
{
Cell cell = row1.createCell(j);
cell.setCellValue(
list.get(i + beginNum).get(keys[j]) == null ? " "
: list.get(i + beginNum).get(keys[j])
.toString());
cell.setCellStyle(cs2);
}
}
}
return wb;
}
/**
* 保存生成的文件
*
* @param name
* example: saveAaSpecName("e:/like.xls");
*/
public void saveAsExcelByName(String name)
{
OutputStream out;
try
{
out = new FileOutputStream(name);
wb.write(out);
System.out.println(out);
}
catch (FileNotFoundException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
catch (IOException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
*
* @param list 数据
* @param keys 数据key值集合
* @param columnNames 列名
* @param sheetName sheet名
* @param fileName 文件名
* @param response 返回
* @throws IOException
*/
public static void saveAsExcelByName(List<Map<String, Object>> list,
String[] keys, String[] columnNames, String sheetName,
String fileName, HttpServletResponse response) throws IOException
{
Map<String, Object> sheetname = new HashMap<String, Object>();
sheetname.put("sheetName", sheetName);
list.add(0, sheetname);
createWorkBook(list, keys, columnNames);
// wb.write(response.getOutputStream());
response.addHeader("Content-Disposition",
"attachment;filename=" + fileName + ".xls");
OutputStream os = new BufferedOutputStream(response.getOutputStream());
wb.write(os);
response.setContentType("application/vnd.ms-excel;charset=utf8");
os.flush();
os.close();
}
/**
* 借款查询导出（）
* @param list 数据
* @param keys 数据key值集合
* @param columnNames 列名
* @param sheetName sheet名
* @param fileName 文件名
* @param response 返回
* @throws IOException
*/
public static void saveAsExcelByNameJinXin(List<Map<String, Object>> list,
String[] keys, String[] columnNames, String sheetName,
String fileName, HttpServletResponse response) throws IOException
{
Map<String, Object> sheetname = new HashMap<String, Object>();
sheetname.put("sheetName", sheetName);
list.add(0, sheetname);
/* createWorkBook(list, keys, columnNames);*/
createWorkBookJinXin(list, keys, columnNames);
// wb.write(response.getOutputStream());
response.addHeader("Content-Disposition",
"attachment;filename=" + fileName + ".xls");
OutputStream os = new BufferedOutputStream(response.getOutputStream());
wb.write(os);
response.setContentType("application/vnd.ms-excel;charset=utf8");
os.flush();
os.close();
}
}
控制类代码:
此方法用的数据用map封装，但也可以自己改为用实体类封装；
/**
*
* @param start_date 开始
* @param end_date 结束
* @param license_plate 车牌
* @param borrower_name 借款人
* @param amount 金额
* @param capital_source 字节来源
* @param response 返回
*/
@RequestMapping("/exportLoanByMapNew")
@ResponseBody
public void exportLoanQueryDatasNew(
@RequestParam("start_date") String start_date,
@RequestParam("end_date") String end_date,
@RequestParam("license_plate") String license_plate,
@RequestParam("borrower_name") String borrower_name,
@RequestParam("capital_source") Integer capital_source,
@RequestParam("amount") Double amount,
@RequestParam("order_no") String order_no,
@RequestParam("status") String status,
/* @RequestParam("buyGoodApply_start_date") String buyGoodApply_start_date,
@RequestParam("buyGoodApply_end_date") String buyGoodApply_end_date,*/
@RequestParam("buyGoods_Id") Integer buyGoods_Id,
@RequestParam("buyGood_Status_Id") Integer buyGood_Status_Id,
HttpServletResponse response)
{
Map<String, Object> queryMap = new HashMap<String, Object>();
queryMap.put("start_date", start_date);
queryMap.put("end_date", end_date);
queryMap.put("license_plate", license_plate);
queryMap.put("borrower_name", borrower_name);
queryMap.put("amount", amount);
queryMap.put("capital_source", capital_source);
queryMap.put("order_no",order_no );
queryMap.put("status",status );
/* queryMap.put("buyGoodApply_start_date",buyGoodApply_start_date );
queryMap.put("buyGoodApply_end_date",buyGoodApply_end_date );*/
queryMap.put("buyGoods_Id",buyGoods_Id );
queryMap.put("buyGood_Status_Id",buyGood_Status_Id );
String[] columnNames =
{ "编号", "借款单号", "客户姓名", "借款状态"
, "申请类型","申请类型状态"
, "借款时间", "借款金额", "已借款天数", "利息"
, "合计总还" };
String[] keys =
{ "id", "order_no", "borrower_name", "status_desc"
,"buyGood","ds_dic_value"
,"create_time", "amount", "days", "interest", "total_sum" };
//数据封装在map中
List<Map<String, Object>> rl = service.exprotLoanDatasNew(queryMap);
if(rl != null && 0 < rl.size()){
for (int i = 0; i < rl.size(); i++) {
System.out.println("==========================="+rl.get(i).toString());
}
}
try
{
/* LoanExcelUtil.saveAsExcelByName(rl, keys, columnNames, "loandata",
"loandata", response);*/
//调用导出工具类方法
LoanExcelUtil.saveAsExcelByNameJinXin(rl, keys, columnNames, "loandata",
"loandata", response);
}
catch (IOException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
}
至此：把代码拷贝过去就能用；
补充：
简洁版！
以下是将工具类与处里类集成放一个类中，方便大家学习；
直接拷贝过去就能用，注意底层传过来的数据格式要与类中接收的一致；
package com.tegen.servlet;
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import com.tegen.entity.HPVRatio;
import com.tegen.service.ExportService;
import com.tegen.service.HPVRatioService;
import com.tegen.uitl.PageControler;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
public class ExportServlet extends HttpServlet {
private static Workbook wb = null;
private static int hh = 0;
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
this.doPost(request, response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//设置请求和响应的编码格式
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
String totale=request.getParameter("totale");
HPVRatio h=new HPVRatio();
h.setSampleInfo(totale);
//获取当前页码
String pageIndex=request.getParameter("pageIndex");
//如果没有值的话，赋值为1
if(pageIndex==null)
{
pageIndex="1";
}
int currentPage=Integer.parseInt(pageIndex);
//每页显示用户条数
int pagesize=8;
HPVRatioService hpvrvice=new HPVRatioService();
PageControler pc=new PageControler();
//获取要查询的表的数据总条数
int count=hpvrvice.getCount();
//获得总页数
int totalPages=pc.getTotalPages(count,pagesize);
//分页查询
/*List<HPVRatio> hpv=new ArrayList<HPVRatio>();*/
/*hpv=hpvrvice.queryHpvByPage(totale, currentPage, pagesize);*/
/* List<Map<String, Object>> exportHpv = hpvrvice.ExportHpvByPage(totale, 1, count);*/
List<Map<String, Object>> exportHpv = hpvrvice.ExportAll();
if(exportHpv == null || exportHpv.size() == 0){
return;
}
//自己改成中文字段名
String[] columnNames =
{ "编号" ,"字段名1" ,"字段名2" ,"字段名3" , "字段名4"
};
//数据库字段名
String[] keys =
{ "ID" ,"SourceType" ,"Number" ,"SampleInfo" , "Globin"
};
saveAsExcelByNameJinXin(exportHpv,
keys,columnNames, "Test",
"Test", response);
/* saveAsExcelByNameJinXin(exportHpv,
keys, columnNames, "",
"", response);*/
}
public void saveAsExcelByNameJinXin(List<Map<String, Object>> list,
String[] keys, String[] columnNames, String sheetName,
String fileName, HttpServletResponse response) throws IOException
{
Map<String, Object> sheetname = new HashMap<String, Object>();
sheetname.put("sheetName", sheetName);
list.add(0, sheetname);
/* createWorkBook(list, keys, columnNames);*/
createWorkBookJinXin(list, keys, columnNames);
// wb.write(response.getOutputStream());
response.addHeader("Content-Disposition",
"attachment;filename=" + fileName + ".xls");
OutputStream os = new BufferedOutputStream(response.getOutputStream());
wb.write(os);
response.setContentType("application/vnd.ms-excel;charset=utf8");
os.flush();
os.close();
}
public static Workbook createWorkBookJinXin(List<Map<String, Object>> list,
String[] keys, String[] columnNames)
{
// 创建excel工作簿
wb = new HSSFWorkbook();
long allDataSize = (list.size() - 1);
long mus = 60000;
long avg = allDataSize / mus;
long mod = allDataSize % mus;
avg = mod == 0 ? avg : avg + 1;
for (int si = 0; si < avg; si++)
{
// 创建第一个sheet（页），并命名
Sheet sheet = wb.createSheet(
list.get(0).get("sheetName").toString() + "_" + si);
// HSSFCellStyle setBorder = (HSSFCellStyle) wb.createCellStyle();
// 设置背景色：
// setBorder.setFillForegroundColor((short) 13);// 设置背景色
// setBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
for (int i = 0; i < keys.length; i++)
{
sheet.setColumnWidth((short) i, (short) (35.7 * 150));
}
// 创建两种单元格格式
CellStyle cs = wb.createCellStyle();
CellStyle cs2 = wb.createCellStyle();
// 创建两种字体
Font f = wb.createFont();
Font f2 = wb.createFont();
// 创建第一种字体样式（用于列名）
f.setFontHeightInPoints((short) 10);
f.setColor(IndexedColors.BLACK.getIndex());
f.setBoldweight(Font.BOLDWEIGHT_BOLD);
// 创建第二种字体样式（用于值）
f2.setFontHeightInPoints((short) 10);
f2.setColor(IndexedColors.BLACK.getIndex());
// 设置第一种单元格的样式（用于列名）
cs.setFont(f);
cs.setBorderLeft(CellStyle.BORDER_THIN);
cs.setBorderRight(CellStyle.BORDER_THIN);
cs.setBorderTop(CellStyle.BORDER_THIN);
cs.setBorderBottom(CellStyle.BORDER_THIN);
cs.setAlignment(CellStyle.ALIGN_CENTER);
cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER); // 设置垂直对齐方式
// 单元格背景色
cs.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
cs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// 设置第二种单元格的样式（用于值）
cs2.setFont(f2);
cs2.setBorderLeft(CellStyle.BORDER_THIN);
cs2.setBorderRight(CellStyle.BORDER_THIN);
cs2.setBorderTop(CellStyle.BORDER_THIN);
cs2.setBorderBottom(CellStyle.BORDER_THIN);
cs2.setAlignment(CellStyle.ALIGN_CENTER);
// ============================表头行begin================================//
// 创建第一行
Row header1 = sheet.createRow((short) 0);
// ============================表头行end==================================//
// 设置列名
if (columnNames.length <= 0)
{
hh = 0;
for (int i = 0; i < columnNames.length; i++)
{
Cell cell = header1.createCell(i);
cell.setCellValue(columnNames[i]);
cell.setCellStyle(cs);
}
}
else
{
hh = 0;
// 创建第二行表头
Row header2 = sheet.createRow((short) 1);
// 表头 第一行
for (int i = 0; i < columnNames.length; i++)
{
if (columnNames[i] != "")
{
Cell cell = header1.createCell(i);
cell.setCellValue(columnNames[i]);
cell.setCellStyle(cs);
}
}
}
int beginNum = Integer.valueOf(Long.valueOf(si * mus).toString());
// 设置每行每列的值
for (int i = 1; i <= mus + 1; i++)
{
if (i + beginNum > allDataSize)
{
break;
}
// Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
// 创建一行，在页sheet上
Row row1 = sheet.createRow(i + hh);
// 在row行上创建一个方格
for (short j = 0; j < keys.length; j++)
{
Cell cell = row1.createCell(j);
cell.setCellValue(
list.get(i + beginNum).get(keys[j]) == null ? " "
: list.get(i + beginNum).get(keys[j])
.toString());
cell.setCellStyle(cs2);
}
}
}
return wb;
}
}
扫一扫
338
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
