一,把相关参数从前台传递给后台
@SuppressWarnings("unchecked")
@RequestMapping(value = "/outExcel", method = RequestMethod.POST)
public void outExcel(
//前台相关参数除了columns是集合其他都是字符串
@RequestParam(value = "columns", required = false, defaultValue = "") String columns,//表格标题栏
@RequestParam(value = "brand", required = false, defaultValue = "") String brand,//数据库查询参数
@RequestParam(value = "prodcode", required = false, defaultValue = "") String prodcode,//数据库查询参数
@RequestParam(value = "cargoname", required = false, defaultValue = "") String cargoname,//数据库查询参数
@RequestParam(value = "yesorno", required = false, defaultValue = "") String yesorno,//数据库查询参数
@RequestParam(value = "desiner", required = false, defaultValue = "") String desiner,//数据库查询参数
@RequestParam(value = "tabnum", required = false, defaultValue = "") String tabnum,//数据库查询参数
@RequestParam(value = "fname", required = false, defaultValue = "") String fname,//表格名称
@RequestParam(value = "exporttype", required = false, defaultValue = "Grid") String exporttype,//生成表格类型
HttpServletRequest request, HttpServletResponse response) {
try {
String results = "";
OutputStream out = response.getOutputStream();
response.reset();// 清空输出流
response.setCharacterEncoding("UTF-8");// 设置相应内容的编码格式
fname = java.net.URLEncoder.encode(fname, "UTF-8");
response.setHeader("Content-Disposition", "attachment;filename="
+ new String(fname.getBytes("UTF-8"), "GBK") + ".xlsx");
response.setContentType("application/ms-excel");// 定义输出类型
net.sf.json.JSONArray listColums = null;
net.sf.json.JSONArray mapResults = null;
results = getExcelResults(brand, prodcode, cargoname, yesorno,
desiner, tabnum);
if (!"".equals(columns) && !"".equals(results)) {
listColums = net.sf.json.JSONArray.fromObject(columns);
mapResults = net.sf.json.JSONArray.fromObject(results
.toString());
ExcelUtils.writeExcelTest(out, fname, listColums, mapResults);
}
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//查询数据库数据
private String getExcelResults(String brand, String prodcode,
String cargoname, String yesorno, String desiner, String tabnum) {
String year = DateUtils.getCurrentYear();
String results = "";
String sqlc = "SELECT prod_code,sku,brand,type_code,tag_price,national_code,dtbt_pep,deal_status,explain,plan_time,prod_type,prod_size,prod_color,prod_season,prod_priority,create_time,prod_sex,prod_time,prod_list_time,class_name,arive_property,same_prod,make_time,desin_nature,prod_name,on_marked,main_id from desin_plan where prod_type<>-1 ";
if (!"".equals(tabnum)) {
sqlc += " AND prod_type='" + tabnum + "'";
}
/*
* if (!"".equals(brand)) { sqlc += " AND brand='" + brand + "'"; }
*/
if (!"".equals(prodcode)) {
sqlc += " AND prod_code IN ('" + prodcode + "')";
}
if (!"".equals(cargoname)) {
sqlc += " AND prod_name LIKE '%" + cargoname + "%'";
}
if (!"".equals(yesorno)) {
sqlc += " AND desin_nature = '" + yesorno + "'";
}
if (!"".equals(desiner)) {
sqlc += " AND dtbt_pep = '" + desiner + "'";
}
if (!"".equals(year)) {
sqlc += " AND create_time BETWEEN '" + year + "' AND '"
+ (Integer.parseInt(year) + 1) + "'";
}
// System.out.println("\n sqlc===" + sqlc);
results = jdbcWrap.queryJson(sqlc);
try {
Thread.sleep(2000);
} catch (InterruptedException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return results;
}
二、写入Excel表格
/**
* 创建Excel文件
*
* @param out
* filepath 文件全路径
* @param sheetName
* 新Sheet页的名字
* @param titles
* 表头
* @param values
* 每行的单元格
*/
public static boolean writeExcelTest(OutputStream out, String sheetName,
List<Map<String, String>> titles, List<Map<String, Object>> values)
throws IOException {
boolean success = false;
Workbook workbook;
workbook = new XSSFWorkbook();
// 生成一个表格
Sheet sheet;
if (StringUtils.isBlank(sheetName)) {
// name 为空则使用默认值
sheet = workbook.createSheet();
} else {
sheet = workbook.createSheet(sheetName);
}
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth((short) 15);
// 生成样式
Map<String, CellStyle> styles = createStyles(workbook);
// 创建标题行
Row row = sheet.createRow(0);
// 存储标题在Excel文件中的序号
Map<String, Integer> titleOrder = Maps.newHashMap();
for (int i = 0; i < titles.size(); i++) {
Cell cell = row.createCell(i);
cell.setCellStyle(styles.get("header"));
String title = titles.get(i).get("header");
String name = titles.get(i).get("field");
cell.setCellValue(title);
titleOrder.put(name, i);
}
// System.out.print("\n titleOrder=" + titleOrder);
// 写入正文
Iterator<Map<String, Object>> iterator = values.iterator();
// 行号
int index = 1;
while (iterator.hasNext()) {
row = sheet.createRow(index);
Map<String, Object> value = iterator.next();
// System.out.print("\n value==" + value);
for (Map.Entry<String, Object> map : value.entrySet()) {
// 获取列名
String title = map.getKey();
// System.out.print("\n map=" + map);
// 根据列名获取序号
int i = titleOrder.get(title);
// System.out.print("\n i==" + i);
// 在指定序号处创建cell
Cell cell = row.createCell(i);
// 设置cell的样式
if (index % 2 == 1) {
cell.setCellStyle(styles.get("cellA"));
} else {
cell.setCellStyle(styles.get("cellB"));
}
// 获取列的值
Object object = map.getValue();
// 判断object的类型
SimpleDateFormat simpleDateFormat = new SimpleDateFormat(
"yyyy-MM-dd HH:mm:ss");
if (object instanceof Double) {
cell.setCellValue((Double) object);
} else if (object instanceof Date) {
String time = simpleDateFormat.format((Date) object);
cell.setCellValue(time);
} else if (object instanceof Calendar) {
Calendar calendar = (Calendar) object;
String time = simpleDateFormat.format(calendar.getTime());
cell.setCellValue(time);
} else if (object instanceof Boolean) {
cell.setCellValue((Boolean) object);
} else {
if (object != null) {
cell.setCellValue(object.toString());
}
}
}
index++;
}
try {
workbook.write(out);
success = true;
} finally {
if (out != null) {
out.close();
}
if (workbook != null) {
workbook.close();
}
}
return success;
}
搞定收工!!!!!!!!!!!!!!!!!!!!!