java操作Excel，mdb，dbf

  本文先创建一个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);


}


}


}

大家可以参考下，希望多交流