本文先创建一个Excel模板,好处是模板可以随便换,不用受代码的拘束,如果模板复杂生成模板的代码的逻辑也会比较复杂。如果客户需要更换模板程序员需要重新代码生成模板。所以这样写就省去很多好处。
模板存储位置如上
package com.feng.hangzhouproject.controller;
import java.net.URL;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import com.feng.hangzhouproject.entity.result.Business_result;
import com.feng.hangzhouproject.export.ExportExcel1;
import com.feng.hangzhouproject.tools.DbfUtil;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
/**
* 数据导出
*/
@Controller
@RequestMapping("/export")
public class ExportDataController {
//导出
@RequestMapping(value = "/business",produces = { "application/json;charset=UTF-8" })
@ResponseBody
public Map<String, String> export_Business(String time,String array, Model model,HttpServletRequest req) {
Map<String, String> map = new HashMap<String, String>();
//AccessUtil accessUtil =new AccessUtil();
DbfUtil dbfUtil =new DbfUtil();
try {
//字符转转成JSONArray
JSONArray jsonArray = JSONArray.fromObject(array);
List<Business_result> list1 = new ArrayList<Business_result>();
//将数组中的内容转成对象存入list1中
for(int i=0;i<jsonArray.size();i++){
Object o=jsonArray.get(i);
JSONObject jsonObject=JSONObject.fromObject(o);
Business_result br=(Business_result)JSONObject.toBean(jsonObject, Business_result.class);
list1.add(i, br);
}
//导出mdb
//accessUtil.connetAccessDB(time, list1, req);
//导出dbf
dbfUtil.writeDBF(time, list1, req);
String str=req.getSession().getServletContext().getContextPath();
//全路径
String str1=req.getSession().getServletContext().getRealPath("exportfile");
System.out.println(str);
//模板路径
URL url =model.getClass().getResource("/model/2017年2季度国控城市功能区环境声环境监测数据(杭州).xls");
String path = url.toURI().getPath();
System.out.println(path);
//获取年份
String year = time.substring(0, 4);
//获取月份
String month = time.substring(5, 7);
//判断第一季度
if(month.equals("01")||month.equals("02")||month.equals("03")){
ExportExcel1.writeResult1(path, str1+"/"+year+"年1季度国控城市功能区环境声环境监测数据(杭州).xls", list1);
map.put("excel下载地址",str+"/exportfile/"+year+"年1季度国控城市功能区环境声环境监测数据(杭州).xls");
map.put("dbf下载地址",str+"/exportfile/"+year+"年1季度国控城市功能区环境声环境监测数据(杭州).dbf");
System.out.println(map);
}
//判断第二季度
else if(month.equals("04")||month.equals("05")||month.equals("06")){
ExportExcel1.writeResult1(path, str1+"/"+year+"年2季度国控城市功能区环境声环境监测数据(杭州).xls",list1);
map.put("excel下载地址", str+"/exportfile/"+year+"年2季度国控城市功能区环境声环境监测数据(杭州).xls");
map.put("dbf下载地址",str+"/exportfile/"+year+"年2季度国控城市功能区环境声环境监测数据(杭州).dbf");
}
//判断第三季度
else if(month.equals("07")||month.equals("08")||month.equals("09")){
ExportExcel1.writeResult1(path, str1+"/"+year+"年3季度国控城市功能区环境声环境监测数据(杭州).xls", list1);
map.put("excel下载地址", str+"/exportfile/"+year+"年3季度国控城市功能区环境声环境监测数据(杭州).xls");
map.put("dbf下载地址",str+"/exportfile/"+year+"年3季度国控城市功能区环境声环境监测数据(杭州).dbf");
}
//判断第四季度
else{
ExportExcel1.writeResult1(path, str1+"/"+year+"年4季度国控城市功能区环境声环境监测数据(杭州).xls", list1);
map.put("excel下载地址", str+"/exportfile/"+year+"年4季度国控城市功能区环境声环境监测数据(杭州).xls");
map.put("dbf下载地址",str+"/exportfile/"+year+"年4季度国控城市功能区环境声环境监测数据(杭州).dbf");
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return map;
}
}
生成Excel的工具类
package com.feng.hangzhouproject.export;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.List;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import com.feng.hangzhouproject.entity.result.Business_result;
import com.feng.hangzhouproject.tools.ExcelOperateTool;
import net.sf.json.JSONArray;
public class ExportExcel1 {
/**
* 填充单元格
* @param sheet
* @param columns
* @param startRow
*/
public static void pointDataInsert(Sheet sheet, String[] columns, int startRow) {
Row row = sheet.createRow(startRow);
for (int i = 0; i < columns.length; i++) {
String value = columns[i];
if (value != null && !value.equalsIgnoreCase("null")) {
row.createCell(i, Cell.CELL_TYPE_STRING).setCellValue(value.toString());
}
}
}
public static void writeResult(String in,String out,JSONArray json) throws Exception{
ExcelOperateTool.copyExcel(new File(in), new File(out));
InputStream is = new FileInputStream(out);
//根据输入流创建Workbook对象
Workbook wb = WorkbookFactory.create(is);
Sheet sheet = wb.getSheetAt(0);
for(int i = 2;i-2<json.size();i++){
JSONArray array = json.getJSONArray(i-2);
String[] data = new String[array.size()];
Object[] ar = array.toArray();
for(int j=0;j<array.size();j++){
data[j] = ar[j]+"";
}
pointDataInsert(sheet,data,i);
}
FileOutputStream fos = new FileOutputStream(out);
wb.write(fos);
fos.flush();
fos.close();
}
public static void writeResult1(String in,String out,List<Business_result> list) throws Exception{
JSONArray b = new JSONArray();
for(int i=0;i<list.size();i++){
JSONArray list1 = new JSONArray();
list1.add(list.get(i).getSn());
list1.add(list.get(i).getStcode());
list1.add(list.get(i).getYear());
list1.add(list.get(i).getPoint_num());
list1.add(list.get(i).getPoint_num());
list1.add(list.get(i).getLongitude().toString());
list1.add(list.get(i).getLatitude().toString());
list1.add(list.get(i).getHeight().toString());
list1.add(list.get(i).getReference());
list1.add(list.get(i).getType());
list1.add(list.get(i).getMonth());
list1.add(list.get(i).getDay());
list1.add(list.get(i).getHours());
list1.add(list.get(i).getMinute());
list1.add(String.valueOf(list.get(i).getLeq()));
list1.add(String.valueOf(list.get(i).getL10()));
list1.add(String.valueOf(list.get(i).getL50()));
list1.add(String.valueOf(list.get(i).getL90()));
list1.add(String.valueOf(list.get(i).getLmax()));
list1.add(String.valueOf(list.get(i).getLmin()));
list1.add(String.valueOf(list.get(i).getSd()));
list1.add(list.get(i).getMonitor_name());
list1.add(list.get(i).getManufacturers());
list1.add(list.get(i).getEquipment_num());
list1.add(list.get(i).getRight_limits());
list1.add(list.get(i).getLeft_limits());
list1.add(String.valueOf(list.get(i).getReality_value()));
list1.add(list.get(i).getCalibration_type());
list1.add(list.get(i).getCalibration_num());
b.add(i, list1);
}
writeResult(in,out,b);
}
}
导出结果如下
生成mdb的工具类
package com.feng.hangzhouproject.tools;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.*;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import com.feng.hangzhouproject.entity.result.Business_result;
public class AccessUtil {
private Connection conn;
// 空白的文件地址
private final String blankMdbFilePath = "model/Database.mdb";
// 需要保存到的新的mdb文件路径和名
private String savedMdbFilePath = null;
// 标准的单件模式
private static AccessUtil instance = new AccessUtil();
public static AccessUtil getInstance() {
return instance;
}
// 新的文件名字
public String savedMdbFilePathAndName(String time,HttpServletRequest req) {
//获取年份
String year = time.substring(0, 4);
// 获取月份
String month = time.substring(5, 7);
String str1=req.getSession().getServletContext().getRealPath("exportfile");
if (month.equals("01") || month.equals("02") || month.equals("03")) {
savedMdbFilePath = str1+"/" + year + "年1季度国控城市功能区环境声环境监测数据(杭州).mdb";
} else if (month.equals("04") || month.equals("05") || month.equals("06")) {
savedMdbFilePath = str1+"/" + year + "年2季度国控城市功能区环境声环境监测数据(杭州).mdb";
} else if (month.equals("07") || month.equals("08") || month.equals("09")) {
savedMdbFilePath = str1+"/" + year + "年3季度国控城市功能区环境声环境监测数据(杭州).mdb";
} else {
savedMdbFilePath = str1+"/" + year + "年4季度国控城市功能区环境声环境监测数据(杭州).mdb";
}
return savedMdbFilePath;
}
/**
* <p>
* Description: 将空白mdb文件拷贝到特定目录
* </p>
*/
public void copyBlankMdbFile(String time,HttpServletRequest req) throws Exception {
InputStream is = this.getClass().getClassLoader().getResourceAsStream(blankMdbFilePath);
OutputStream out = new FileOutputStream(savedMdbFilePathAndName(time,req));
System.out.println(savedMdbFilePathAndName(time,req));
byte[] buffer = new byte[1024];
int numRead;
while ((numRead = is.read(buffer)) != -1) {
out.write(buffer, 0, numRead);
}
is.close();
out.close();
}
/**
* <p>
* Description: 打开对mdb文件的jdbc-odbc连接
* </p>
*/
public void connetAccessDB(String time, List<Business_result> list,HttpServletRequest req) throws Exception {
//拷贝文件
copyBlankMdbFile( time,req);
Class.forName("com.hxtt.sql.access.AccessDriver");
//String dbur1 = "jdbc:Access:///d:/杭州.mdb";
String database = "jdbc:Access:///" + savedMdbFilePathAndName(time,req);
conn = DriverManager.getConnection(database, "", "");
String sql = "insert into qn values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
PreparedStatement prest = conn.prepareStatement(sql);
for (int i = 0; i < list.size(); i++) {
prest.setLong(1, Long.parseLong(list.get(i).getYear()));
prest.setString(2, list.get(i).getMonitor_name());
prest.setLong(3, Long.parseLong(list.get(i).getStcode()));
prest.setString(4, list.get(i).getPoint_name());
prest.setLong(5, Long.parseLong(list.get(i).getPoint_num()));
// 噪声功能区代码
prest.setLong(6, Long.parseLong(list.get(i).getType().substring(0, 1)));
prest.setInt(7, Integer.parseInt(list.get(i).getMonth()));
prest.setInt(8, Integer.parseInt(list.get(i).getDay()));
prest.setInt(9, Integer.parseInt(list.get(i).getHours()));
prest.setDouble(10, Double.parseDouble(list.get(i).getLeq()));
prest.setDouble(11, Double.parseDouble(list.get(i).getL10()));
prest.setDouble(12, Double.parseDouble(list.get(i).getL50()));
prest.setDouble(13, Double.parseDouble(list.get(i).getL90()));
prest.addBatch();
}
prest.executeBatch();
conn.commit();
conn.close();
}
}
生成dbf的工具类
package com.feng.hangzhouproject.tools;
import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import com.feng.hangzhouproject.entity.result.Business_result;
import com.linuxense.javadbf.DBFWriter;
public class DbfUtil {
// 空白的文件地址
private final String blankDbfFilePath = "model/hangzhou.dbf";
// 需要保存到的新的mdb文件路径和名
private String savedDbfFilePath = null;
// 标准的单件模式
private static DbfUtil instance = new DbfUtil();
public static DbfUtil getInstance() {
return instance;
}
// 新的文件名字
public String savedDbfFilePathAndName(String time, HttpServletRequest req) {
// 获取年份
String year = time.substring(0, 4);
// 获取月份
String month = time.substring(5, 7);
String str1 = req.getSession().getServletContext().getRealPath("exportfile");
if (month.equals("01") || month.equals("02") || month.equals("03")) {
savedDbfFilePath = str1 + "/" + year + "年1季度国控城市功能区环境声环境监测数据(杭州).dbf";
} else if (month.equals("04") || month.equals("05") || month.equals("06")) {
savedDbfFilePath = str1 + "/" + year + "年2季度国控城市功能区环境声环境监测数据(杭州).dbf";
} else if (month.equals("07") || month.equals("08") || month.equals("09")) {
savedDbfFilePath = str1 + "/" + year + "年3季度国控城市功能区环境声环境监测数据(杭州).dbf";
} else {
savedDbfFilePath = str1 + "/" + year + "年4季度国控城市功能区环境声环境监测数据(杭州).dbf";
}
return savedDbfFilePath;
}
/**
* 拷贝DBF空文件
*
* @param time
* @param req
* @throws Exception
*/
public void copyBlankDbfFile(String time, HttpServletRequest req) throws Exception {
InputStream is = this.getClass().getClassLoader().getResourceAsStream(blankDbfFilePath);
OutputStream out = new FileOutputStream(savedDbfFilePathAndName(time, req));
System.out.println(savedDbfFilePathAndName(time, req));
byte[] buffer = new byte[1024];
int numRead;
while ((numRead = is.read(buffer)) != -1) {
out.write(buffer, 0, numRead);
}
is.close();
out.close();
}
public void writeDBF(String time, List<Business_result> list, HttpServletRequest req) throws Exception {
//InputStream fis = null;
OutputStream fos=null;
// 拷贝DBF文件
copyBlankDbfFile(time, req);
// 定义DBFWriter实例用来写DBF文件
DBFWriter writer = new DBFWriter(new File(savedDbfFilePathAndName(time, req))); ;
// 把字段信息写入DBFWriter实例,即定义表结构
for (int i = 0; i < list.size(); i++) {
//一条条的写入记录
Object[] rowData = new Object[13];
rowData[0] = Long.parseLong(list.get(i).getYear());
rowData[1] = list.get(i).getMonitor_name();
rowData[2] = Long.parseLong(list.get(i).getStcode());
rowData[3] = list.get(i).getPoint_name();
rowData[4] = Long.parseLong(list.get(i).getPoint_num());
rowData[5] = Long.parseLong(list.get(i).getType().substring(0, 1));
rowData[6] = Long.parseLong(list.get(i).getMonth());
rowData[7] = Long.parseLong(list.get(i).getDay());
rowData[8] = Long.parseLong(list.get(i).getHours());
rowData[9] = Float.parseFloat(list.get(i).getLeq());
rowData[10] = Float.parseFloat(list.get(i).getL10());
rowData[11] = Float.parseFloat(list.get(i).getL50());
rowData[12] = Float.parseFloat(list.get(i).getL90());
writer.addRecord(rowData);
}
}
}
大家可以参考下,希望多交流