导出execle

最新推荐文章于 2023-06-06 10:50:35 发布

原创最新推荐文章于 2023-06-06 10:50:35 发布 · 504 阅读

0 ·

CC 4.0 BY-SA版权

javase 同时被 2 个专栏收录

13 篇文章

订阅专栏

javaEE

12 篇文章

订阅专栏

本文介绍了如何使用Apache POI库在Java中实现Excel数据的导出和导入功能，包括创建工作簿、工作表，设置表头和数据行，以及从上传的Excel文件中读取数据并保存到数据库。

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>My JSP 'index.jsp' starting page</title>
</head>

<body>
<a href="excel.action">导出Excel</a>
<hr/>
<form action="readexcel.action" method="post" enctype="multipart/form-data">
批量导入数据：<input type="file" name="excel"/>
<input type="submit" value="导入"/>
</form>

</body>
</html>

package com.kaishengit.web;

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.struts2.interceptor.ServletResponseAware;

import com.kaishengit.entity.TempUser;
import com.kaishengit.service.TempUserService;
import com.kaishengit.service.UserService;

public class ExcelAction implements ServletResponseAware{

private HttpServletResponse response;
private File excel;
private String excelContentType;
private String excelFileName;

private TempUserService tempUserService;

public String outputExcel() throws Exception {
//设置输出头
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
//设置HTTP响应的头
response.addHeader("Content-Disposition", "attachment;filename=data.xls");

ServletOutputStream stream = response.getOutputStream();

//创建工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
//创建工作标签页(sheet)
HSSFSheet sheet = workbook.createSheet("我的联系人");

//创建表头
HSSFRow row = sheet.createRow(0);

HSSFCell cell = row.createCell(0);
cell.setCellValue("姓名");

HSSFCell cell2 = row.createCell(1);
cell2.setCellValue("电话");

//创建数据行

List<TempUser> list = tempUserService.findAll();

for(int i = 0;i<list.size();i++) {
TempUser user = list.get(i);
HSSFRow newrow = sheet.createRow(i+1);
newrow.createCell(0).setCellValue(user.getName());
newrow.createCell(1).setCellValue(user.getTel());
}

workbook.write(stream);

stream.flush();
stream.close();

return null;
}

public String readExcel() throws Exception{
System.out.println("FileContentType:" + excelContentType);
System.out.println("FileName:" + excelFileName);

HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(excel));
HSSFSheet sheet = workbook.getSheet("我的联系人");

List<TempUser> list = new ArrayList<TempUser>();

int rows = sheet.getPhysicalNumberOfRows();
for(int i = 1;i<rows;i++) {
HSSFRow row = sheet.getRow(i);
int columns = row.getPhysicalNumberOfCells();

TempUser user = new TempUser();

for(int j = 0;j<columns;j++) {
HSSFCell cell = row.getCell(j);
if(cell.getColumnIndex() == 0) {
user.setName(cell.getStringCellValue());
} else if(cell.getColumnIndex() == 1) {
user.setTel(cell.getStringCellValue());
}
}

list.add(user);
}
tempUserService.save(list);
return "success";
}

public File getExcel() {
return excel;
}

public void setExcel(File excel) {
this.excel = excel;
}

public String getExcelFileName() {
return excelFileName;
}

public void setExcelFileName(String excelFileName) {
this.excelFileName = excelFileName;
}

public String getExcelContentType() {
return excelContentType;
}

public void setExcelContentType(String excelContentType) {
this.excelContentType = excelContentType;
}

public TempUserService getTempUserService() {
return tempUserService;
}

public void setTempUserService(TempUserService tempUserService) {
this.tempUserService = tempUserService;
}

public void setServletResponse(HttpServletResponse response) {
this.response = response;
}

}

package com.kaishengit.dao;

import java.util.List;

import org.hibernate.CacheMode;

import com.kaishengit.entity.TempUser;

public class TempUserDao extends BaseDao<TempUser, Integer>{

public void batchSave(List<TempUser> list) {

getSession().setCacheMode(CacheMode.GET);

int count = 0;
for(TempUser user : list) {
getSession().save(user);
count++;
if(count % 20 == 0) {
getSession().flush();
getSession().clear();
}
}

getSession().setCacheMode(CacheMode.NORMAL);
}

}