Excel导入数据库以及数据库导出到Excel相关操作
这里采用的idea2019 和 maven项目。先说一下测试类，写一个项目都是分模块进行书写，如果一个项目足够大，那么跑一下项目都需要很长时间，如果出错那么就很得不偿失，所以需要进行写好一个模块就进行测试。至于如何建立maven项目和怎么在idea上创建maven项目可以参考其他博客，如果徐要请联系我，我会尽快推出。
- 导入依赖
<dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> </dependency> - 测试类
- 准备数据
- 直接在Excel中写入数据
- 通过代码编写
1.创建工作簿
HSSFWorkbook sheets = new HSSFWorkbook();
//实例化一个sheet
HSSFSheet sheet = wb.createSheet();
//第一行
HSSFRow row = sheet.createRow(0);
//第一行第二列单元格
HSSFCell cell = row.createCell(1);
cell.setCellValue("张");
File file = new File("C:\\Users\\张少\\Desktop\\excel\\test.xls");
//把工作簿中的东西写入Excel
try {
wb.write(file);
} catch (IOException e) {
e.printStackTrace();
}finally {
wb.close();
}
}
效果展示
4. 下面是重头戏 导出数据
由于篇幅原因 只能贴出核心代码，代码下载可以访问联系我。或者访问我的个人主页。
```java
/**
* 从数据库中把数据导出到excel
*/
@WebServlet("/poiout")
public class PoiServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doPost(req, resp);
}
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//连接数据库
response.setContentType("text/html;charset=utf-8");//防止乱码
DbUtil dbUtil = new DbUtil();
Connection con=null;
try {
con = dbUtil.getCon();//获取连接
ResultSet user = user(con);
/**
* 查询出来的只有数据，没有表头，所以要先将表头定义在一个数组中
*/
HSSFWorkbook wb = new HSSFWorkbook();
//这里是根据你自己要导出的表自行填写
String headers[]={"编号","用户名","密码","手机号码","地址","日期","队编号"};
fillexcelData(user,wb,headers);
export(response,wb,"导出数据.xls");
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
dbUtil.closeCon(con);
} catch (Exception e) {
e.printStackTrace();
}
}
}
//查询数据
public ResultSet user(Connection con) throws SQLException {
StringBuffer sb = new StringBuffer("select * from user");
PreparedStatement statement = con.prepareStatement(sb.toString());
return statement.executeQuery();
}
//导出结果集
public void fillexcelData(ResultSet res, Workbook wb, String[]headers) throws SQLException {
int rowIndex=0;
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(rowIndex++);//每调用一次就执行++，确保到下一行
//标题
for (int i=0;i<headers.length;i++){
row.createCell(i).setCellValue(headers[i]);
}
//导出数据库信息
while(res.next()){
row=sheet.createRow(rowIndex++);
for (int i=0;i<headers.length;i++){
row.createCell(i).setCellValue(res.getObject(i+1).toString());
}
}
}
//下载到本地
public void export(HttpServletResponse response,Workbook wb,String Filename) throws IOException {
//下载所需要传入的头
response.setHeader("Content-Disposition","attachment;filename="+new String(Filename.getBytes("utf-8"),"iso8859-1"));
//设置内容信息
response.setContentType("application/ynd.ms-excel;charset=utf-8");
OutputStream out= response.getOutputStream();
//把工作簿传入到流上
wb.write(out);
out.flush();
out.close();
}
}
- 导入数据
@WebServlet("/uploads")
@MultipartConfig
public class UploadServlet extends HttpServlet {
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
Part part = request.getPart("files");
InputStream is = part.getInputStream();
String path="D:\\MYcode\\Javaweb\\testpoi\\src\\main\\webapp\\up";
//拿到上传文件
//拿到文件名
//这里斜杠是让路径和名字分开“%s/%s”到up包下
String filename = String.format("%s/%s", path, getFilename(part));
common.EXCEL_PATH=filename;
FileOutputStream fos = new FileOutputStream(new File(filename));
byte[] buf = new byte[1024];
while (is.read(buf)!=-1){
fos.write(buf);
}
//上传到数据库
SaveDataToDB saveDataToDB = new SaveDataToDB();
try {
saveDataToDB.save();
} catch (Exception e) {
e.printStackTrace();
}
}
private String getFilename(Part part){
String contentheader = part.getHeader("Content-Disposition");
String[] elements = contentheader.split(";");
for(String element:elements){
if(element.trim().startsWith("filename")){
return element.substring(element.indexOf('=')+1).trim().replace("\"","");
}
}
return null;
}
}
********************************************************************
package com.zz.up.excel;
import com.zz.up.common.common;
import com.zz.up.domain.User;
import java.util.List;
public class SaveDataToDB {
public void save() throws Exception {
//读取Excel的对象
ReadExcel readExcel = new ReadExcel();//读取到了Excel对象
List<User> list = readExcel.readxls();
User user=null;
System.out.println("1");
//将list中的数据存放到数据库
for(int i=0;i<list.size();i++){
user = list.get(i);
//如果两个人一致，不允许插入
List l = DBs.selectOne(common.SELECT_USER_SQL+"'%"+user.getUsername()+"'%",user);
if(!l.contains(1)){
//插入数据
DBs.insert(common.INSERT_USER_SQL,user);
}else {
System.out.println("save name error");
}
}
}
}
********************************************************************
读取Excel文件夹，加入common类，包含路径和sql语句，插入数据库
package com.zz.up.excel;
import com.zz.up.common.common;
import com.zz.up.domain.User;
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 java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;
/*
这个是个很通用的工具类，不仅仅用在这里，类似于android上面的读取文件这些都是可以的。可以单独作为一个工具类进行处理
*/
public class ReadExcel {
//文件转成list
public List<User> readxls() throws Exception {
//将文件上传到流
FileInputStream is =new FileInputStream(common.EXCEL_PATH);
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);//工作簿
User user=null;
List<User> list = new ArrayList<User>();
//把工作簿的内容导入list
for(int numsheet=0;numsheet<hssfWorkbook.getNumberOfSheets();numsheet++){
//得到第几个工作区
HSSFSheet sheet = hssfWorkbook.getSheetAt(numsheet);
if(sheet==null){
continue;
}
for(int rownum=1;rownum<=sheet.getLastRowNum();rownum++){
HSSFRow row = sheet.getRow(rownum);
if(row!=null){
user = new User();
HSSFCell username = row.getCell(0);
HSSFCell password = row.getCell(1);
HSSFCell phone_no = row.getCell(2);
HSSFCell address = row.getCell(3);
user.setUsername(getvalue(username));
user.setPassword(getvalue(password));
user.setPhone_no(getvalue(phone_no));
user.setAddress(getvalue(address));
list.add(user);
}
}
}
return list;
}
//将单元内容转化为string类型
private String getvalue(HSSFCell hssfCell){
if(hssfCell.getCellType()==hssfCell.CELL_TYPE_BOOLEAN){
return String.valueOf(hssfCell.getBooleanCellValue());
}else if(hssfCell.getCellType()==hssfCell.CELL_TYPE_NUMERIC){
return String.valueOf(hssfCell.getNumericCellValue());
}else {
return String.valueOf(hssfCell.getStringCellValue());
}
}
}
insert操作
package com.zz.up.excel;
import com.zz.up.domain.User;
import com.zz.utils.DButils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
public class DBs {
public static List selectOne(String sql, User user) {
//JDBC需要的类
Connection connection=null;
PreparedStatement ps=null;
ResultSet resultSet=null;
List list = new ArrayList();
try{
connection= DButils.getConnnection();
ps = connection.prepareStatement(sql);
resultSet = ps.executeQuery();
while(resultSet.next()){
if(resultSet.getString("name").equals(user.getUsername())){
list.add(1);
}else{
list.add(0);
}
}
}catch(Exception e){
e.printStackTrace();
}finally {
DButils.closeAll(connection,ps,resultSet);
}
return list;
}
public static void insert(String sql, User user) {
Connection connection=null;
PreparedStatement ps=null;
try {
connection=DButils.getConnnection();
ps= connection.prepareStatement(sql);
//对应common中的占位符
ps.setString(1,user.getUsername());
ps.setString(2,user.getPassword());
ps.setString(3,user.getPhone_no());
ps.setString(4,user.getAddress());
boolean flag=ps.execute();
} catch (Exception e) {
e.printStackTrace();
}finally {
DButils.closeAll(connection,ps,null);
}
}
}
common类：
package com.zz.up.common;
/**
*
*/
public class common {
public static String EXCEL_PATH="";
public static final String INSERT_USER_SQL="insert into user(username,password,phone_no,address)values(?,?,?,?)";
public static final String SELECT_USER_SQL="select *from user where name like ";
}
user类：这个导入数据库时候字段根据自己的进行更改，主要要和数据库的保持一致，不一致可以取别名或者映射。
package com.zz.up.domain;
public class User {
private int id;
private String username;
private String password;
private String phone_no;
private String address;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getPhone_no() {
return phone_no;
}
public void setPhone_no(String phone_no) {
this.phone_no = phone_no;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
}
DButils类:
package com.zz.utils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class DButils {
public static Connection getConnnection(){
try{
Class.forName("com.mysql.jdbc.Driver");
String Url="jdbc:mysql://localhost:3306/n_text";
//这个地方用到的是mysql数据库，这个地方的密码是需要更改成你自己的。
String User="root";
String Password="0722506209";
return DriverManager.getConnection(Url,User,Password);
}catch(Exception e){
e.printStackTrace();
}
return null;
}
public static void closeAll(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet){
try {
if(connection!=null)
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
try {
if(preparedStatement!=null)
preparedStatement.close();
} catch (Exception e) {
e.printStackTrace();
}
try {
if(resultSet!=null)
resultSet.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void closeCon(Connection con)throws Exception{
if(con!=null){
con.close();
}
}
}
总结：第一篇博客，目的是分享一下学习经验，当然也希望想学到这方面的人可以学到会这些知识。
2005
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
