Excel导入、导出
本篇是自己工作中梳理的总结，有不当之处请批评指正!!!
1 jar 包准备
2 引入ExcelUtil工具类
public File getHSSFWorkbook(String sheetName, String[] title, String[][] values){
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet(sheetName);
HSSFRow row = sheet.createRow(0);
HSSFCellStyle style = wb.createCellStyle();
HSSFCell cell = null;
for (int i = 0; i < title.length; ++i) {
cell = row.createCell(i);
cell.setCellValue(title[i]);
cell.setCellStyle(style);
}
for (i = 0; i < values.length; ++i) {
row = sheet.createRow(i + 1);
for (int j = 0; j < values[i].length; ++j)
{
row.createCell(j).setCellValue(values[i][j]);
}
}
String export_path = PropKit.get("export_path"); System.out.println("export_path---" + export_path);
deleteFile(export_path);
String file_name = export_path + "/" + DateUtil.getCurrentDate();
File f = new File(file_name);
if (!(f.exists())) {
f.mkdir();
}
file_name = file_name + "/" + System.currentTimeMillis() + ".xls"; System.out.println("file_name---" + file_name);
File wfile = new File(file_name);
try {
FileOutputStream fos = new FileOutputStream(wfile);
wb.write(fos);
}
catch (FileNotFoundException e) {
e.printStackTrace();
}
catch (IOException e) {
e.printStackTrace();
}
return wfile;
}
public void deleteFile(String path){
try{
File fileRoot = new File(path);
if (fileRoot.exists()) {
File[] fileAll = fileRoot.listFiles();
for (File file : fileAll) {
if ((!(file.isDirectory())) ||
(!(file.getName().startsWith("2")))) continue;
String nowDate = DateUtil.getCurrentDate();
if (!(file.getName().equals(nowDate))) {
new FileOperation().deleteDir(file.getPath());
}
}
}
}catch (Exception e){
e.printStackTrace();
}
}
public String getExcelContent2003(HSSFRow row, int index){
String content = "";
try {
row.getCell(index).setCellType(CellType.STRING);
content = row.getCell(index).getStringCellValue().trim();
}catch (Exception e) {
e.printStackTrace();
}
return content;
}
public String getExcelContent2007(XSSFRow row, int index){
String content = "";
try {
row.getCell(index).setCellType(CellType.STRING);
content = row.getCell(index).getStringCellValue().trim();
}
catch (Exception e) {
e.printStackTrace();
}
return content;
} ```
3 前端jsp
<button type="button" class="btn btn-info" onclick="batchImportObject()">
<span class="glyphicon glyphicon-import" aria-hidden="true"></span>导入
</button>
<button type="button" class="btn btn-info" onclick="exportObject()">
<span class="glyphicon glyphicon-export" aria-hidden="true"></span>导出
</button>
function batchImportObject() {
modelWindow.openFrameWindow("栏目统计数据导入",
"/sys/cs/project/system/data/tongji/info/catinfo_import.html?cat_id="+ cat_id, 450, 300);
}
function exportObject() {
window.open("/sys/cs/project/system/data/tongji/info/exportCatInfo/"+ cat_id);
}
4 java方法
导出：
public class ExportStatisticalInfo{
public File exportStatisticalInfo(String cat_id){
SqlSession s = IbatisSessionFactory.getInstance().openSession();
try {
JSONObject catinfo = new CatCache().getCatJSONObject(cat_id);
String titles = "序号," + catinfo.getString("title_name");
for (int i = 0; i < catinfo.getIntValue("data_num"); ++i) {
titles = titles + "," + catinfo.getString(new StringBuilder("data_name").append(i + 1).toString());
}
List info_list = new StatisticalInfoDao().getAllStatisticalInfos(s, cat_id);
String[] title = titles.split(",");
System.out.println("note_list---" + JSON.toJSONString(info_list));
String sheetName = catinfo.getString("cat_fullname");
String[][] content = new String[info_list.size()][title.length];
int i = 0;
for (Map json : info_list) {
content[i][0] = (i + 1);
content[i][1] = json.get("title_data");
for (int j = 0; j < catinfo.getIntValue("data_num"); ++j) {
content[i][(j + 2)] = json.get(new StringBuilder("data").append(j + 1).toString());
}
++i;
}
return new ExcelUtils().getHSSFWorkbook(sheetName, title, content);
}catch (Exception e) {
e.printStackTrace();
return null;
} finally {
s.close();
}
}
}
导入：
public class ImportStatisticalInfo{
public boolean importInfoData(String path, String cat_id, SettingLogsBean stl){
boolean result = false;
path = new UploadPicFunctions().getSavePath() + path;
try {
List reaultList = new ArrayList();
if (path.toLowerCase().endsWith(".xls")) {
result = readExcel2003(path, cat_id, stl);
break ;
} if (path.toLowerCase().endsWith(".xlsx"))
result = readExcel2007(path, cat_id, stl);
}catch (Exception e){
e.printStackTrace();
}
return result;
}
public boolean readExcel2003(String path, String cat_id, SettingLogsBean stl){
boolean rsflag = false;
int index = 0;
SqlSession s = IbatisSessionFactory.getInstance().openSession();
CatBean catInfo = new CatCache().getCatBean(cat_id);
if ((catInfo != null) && (!("".equals(catInfo)))) {
index = catInfo.getData_num();
}
StatisticalInfoDao statisticalInfoDao = new StatisticalInfoDao();
try {
FileInputStream fis = new FileInputStream(path);
HSSFWorkbook workbook = new HSSFWorkbook(fis);
HSSFSheet sheet = workbook.getSheetAt(0);
ExcelUtils eUtil = new ExcelUtils();
Map cat_map = new HashMap();
cat_map.put("cat_id", cat_id);
cat_map.put("add_time", DateUtil.getCurrentDateTime());
cat_map.put("add_user", stl.getUser_id());
for (int i = 0; i < 20 - index; ++i) {
cat_map.put("data" + (index + 1 + i), "");
}
for (i = 1; i < sheet.getPhysicalNumberOfRows(); ++i) {
HSSFRow row = sheet.getRow(i);
String title_data = eUtil.getExcelContent2003(row, 1);
if (!("".equals(title_data))) {
for (int j = 0; j < index; ++j){
cat_map.put("data" + (j + 1), eUtil.getExcelContent2003(row, j + 2));
}
cat_map.put("title_data", title_data);
cat_map.put("status", "0");
cat_map.put("sort_id", "999");
cat_map.put("memo", "");
rsflag = statisticalInfoDao.insertStatisticalInfoMap(s, cat_map);
if (!(rsflag)) {
break;
}
}
}
if (!(rsflag)) break label391;
PublicTableDAO.insertSettingLogs(s, "添加", "批量导入栏目信息", "", stl);
s.commit();
}catch (Exception e){
e.printStackTrace();
s.rollback();
}finally {
s.close();
}
return rsflag;
}
public boolean readExcel2007(String path, String cat_id, SettingLogsBean stl){
boolean rsflag = false;
int index = 0;
SqlSession s = IbatisSessionFactory.getInstance().openSession();
CatBean catInfo = new CatCache().getCatBean(cat_id);
if ((catInfo != null) && (!("".equals(catInfo)))) {
index = catInfo.getData_num();
}
StatisticalInfoDao statisticalInfoDao = new StatisticalInfoDao();
try {
FileInputStream fis = new FileInputStream(path);
XSSFWorkbook workbook = new XSSFWorkbook(fis);
XSSFSheet sheet = workbook.getSheetAt(0);
ExcelUtils eUtil = new ExcelUtils();
Map cat_map = new HashMap();
cat_map.put("cat_id", cat_id);
cat_map.put("add_time", DateUtil.getCurrentDateTime());
cat_map.put("add_user", stl.getUser_id());
for (int i = 0; i < 20 - index; ++i) {
cat_map.put("data" + (index + 1 + i), "");
}
for (i = 1; i < sheet.getPhysicalNumberOfRows(); ++i) {
XSSFRow row = sheet.getRow(i);
String title_data = eUtil.getExcelContent2007(row, 1);
if (!("".equals(title_data))) {
for (int j = 0; j < index; ++j){
cat_map.put("data" + (j + 1), eUtil.getExcelContent2007(row, j + 2));
}
cat_map.put("title_data", title_data);
cat_map.put("status", "0");
cat_map.put("sort_id", "999");
cat_map.put("memo", "");
rsflag = statisticalInfoDao.insertStatisticalInfoMap(s, cat_map);
if (!(rsflag)) {
break;
}
}
}
if (!(rsflag)) break label391;
PublicTableDAO.insertSettingLogs(s, "添加", "批量导入栏目信息", "", stl);
s.commit();
}catch (Exception e){
e.printStackTrace();
s.rollback();
}finally {
s.close();
}
return rsflag;
}
}
古今成大事者，不唯有超世之才，必有坚韧不拔之志！
