Java web--利用java操作excel文档

我就是我 2023-02-14 15:55 83阅读 0赞

在web应用程序的开发中，如果需要将Excel文档中的信息导入数据库或将数据库的信息导出到Excel文档中，需要应用程序访问Excel文件。目前，操作Excel文档的java组件主要有Jxl和POI两种。本篇博客主要讲通过Jxl对Excel的操作。

**目录**

1.读取Excel文档

2.写入Excel文档

3.修改已有的Excel文档

4.java实现Excel表格的数据导入与导出

--------------------

### 1.读取Excel文档 ###

读取电子表时需要用电子工作簿创建Workbook对象，然后基于Workbook对象获取工作簿的工作表，形成sheet对象，最后由sheet对象获取工作表的行、列，并根据行列值完成对工作表单元的读操作。

Workbook workbook=Workbook.getWorkbook(new File("d:/student.xls"));//位置随意填写
    Sheet sheet=workbook.getSheet(0);//第一个工作表
    int r=sheet.getRows();
    int c=sheet.getColumns();
    for(int i=0;i<r;i++){
        for(int j=0;j<c;j++){
    //getCell方法第一个参数是列，第二个参数是行
          System.out.println(sheet.getCell(j,i).getCounts()+"");
        System.out.println("");
        }
    }

### 2.写入Excel文档 ###

写文档与读文档类似，写Excel文档时需要用电子工作簿创建Workbook对象，然后基于Workbook对象获取工作簿的工作表，形成sheet对象，在创建单元格并添加到sheet对象，最后将sheet对象写入Workbook对象中。

WritableWorkbook wwb=Workbook。createWorkbook(new File("位置"));
    WritableSheet ws=wwb.createSheet("TESTSHEET1",0);
    Label label=new Label(2,3,"abc");//3行4列
    ws.addCell(label);
    jxl.write.Number number=new jxl.write.Number(1,0,555.12541);
    ws.addCell(number);
    wwb.write();
    wwb.close();

### 3.修改已有的Excel文档 ###

修改已有的Excel文档

Workbook wb=Workbook.getWorkbook(new File("位置"));
    WritableWorkbook book=Workbook.createWorkbook(new File("位置")，wb);//位置可相同，可不同
    WritableSheet sheet=book.getSheet(0);
    sheet.addCell(new Label(4,1,"ab"));
    sheet.addCell(new Label(5,1,"cd"));
    sheet.addCell(new Label(4,2,"eg"));
    sheet.addCell(new Label(5,2,"gh"));
    book.write();
    book.close();

### 4.java实现Excel表格的数据导入与导出 ###

导入功能：用户选择本地的Excel文件后将其上传到服务器，然后从服务器导入。

导出功能：先将数据库数据导入服务器，在下载到客户端。

Student.java--javabean组件

package bean;
    
    public class Student {
    private String id;
    private String name;
    private String sex;
    public Student(String id, String name, String sex) {
    	super();
    	this.id = id;
    	this.name = name;
    	this.sex = sex;
    }
    public Student() {
    	super();
    	// TODO Auto-generated constructor stub
    }
    public String getId() {
    	return id;
    }
    public void setId(String id) {
    	this.id = id;
    }
    public String getName() {
    	return name;
    }
    public void setName(String name) {
    	this.name = name;
    }
    public String getSex() {
    	return sex;
    }
    public void setSex(String sex) {
    	this.sex = sex;
    }
    
    }

JdbcUtil.java--连接MySql

package bean;
    
    import java.io.IOException;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.Properties;
    
    public class JdbcUtil {
    	private static String driver;
    	private static String url;
    	private static String user;
    	private static String password;
    	private static Properties pr=new Properties();
    	public JdbcUtil(){
    		super();
    	}
    	static 
    	{
    		try {
    			pr.load(JdbcUtil.class.getClassLoader().getResourceAsStream("db.properties"));
    		    driver=pr.getProperty("driver");
    		    url=pr.getProperty("url");
    		    user=pr.getProperty("user");
    		    password=pr.getProperty("password");
    		    try {
    				Class.forName(driver);
    			} catch (ClassNotFoundException e) {
    				// TODO Auto-generated catch block
    				e.printStackTrace();
    			}
    		} catch (IOException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    	}
    	public static Connection getConnection() throws SQLException
    	{
    		return DriverManager.getConnection(url,user,password);
    	}
    	public static void free(Connection conn,Statement pstmt,ResultSet rs) throws Exception
    	{
    		if(conn!=null){conn.close();}
    		if(pstmt!=null){pstmt.close();}
    		if(rs!=null){rs.close();}
    	}
    }

db.properties--用来记录数据库的数据，用以JdbcUtil调用，与xml文件作用类似

driver=com.mysql.jdbc.Driver
    url=jdbc:mysql://localhost:3306/ccc?useUnicode=true&characterEncoding=UTF-8
    user=root
    password=2411030483

StudentDao.java--用以完成功能的书写

package bean;
    
    import java.io.File;
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.util.ArrayList;
    import java.util.List;
    import jxl.Sheet;
    import jxl.Workbook;
    import jxl.write.Label;
    import jxl.write.WritableSheet;
    import jxl.write.WritableWorkbook;
    
    public class StudentDao {
    	public void exportToexcel(File excelpath,String condition) throws Exception{
    		Connection conn=JdbcUtil.getConnection();
    		String sql="select id,name,sex from student"+condition;
    		PreparedStatement pstmt=conn.prepareStatement(sql);
    		ResultSet rs=pstmt.executeQuery();
    		WritableWorkbook wwb=Workbook.createWorkbook(excelpath);
    		WritableSheet ws=wwb.createSheet("student", 0);
    		String[] titles={"学号","姓名","性别"};
    		int columnCount=titles.length;
    		for(int i=0;i<columnCount;i++){
    			ws.addCell(new Label(i,0,titles[i]));
    		}
    		int count=1;
    		while(rs.next()){
    			for(int j=0;j<columnCount;j++){
    				ws.addCell(new Label(j,count,rs.getString(j+1)));
    			}
    			count++;
    		}
    		wwb.write();
    		if(wwb!=null)wwb.close();
    		JdbcUtil.free(conn, pstmt, rs);
    	}
    	public void importFromexcel(File excelpath,int sheetno) throws Exception{
    		Connection conn=JdbcUtil.getConnection();
    		String sql="insert into student(id,name,sex) values(?,?,?)";
    		PreparedStatement pstmt=conn.prepareStatement(sql);
    		Workbook workbook=Workbook.getWorkbook(excelpath);
    		Sheet sheet=workbook.getSheet(sheetno-1);
    		int r=sheet.getRows();
    		int c=sheet.getColumns();
    		for(int i=0;i<r;i++){
    			for(int j=1;j<=c;j++){
    				pstmt.setString(j,sheet.getCell(j-1,i).getContents().trim());
    			}
    			pstmt.addBatch();
    		}
    		pstmt.executeBatch();
    		if(workbook!=null)workbook.close();
    		JdbcUtil.free(conn, pstmt, null);
    	}
    	public List<Student> query()throws Exception{
    		List<Student> studentList=new ArrayList<Student>();
    		Connection conn=JdbcUtil.getConnection();
    		String sql="select * from student";
    		PreparedStatement pstmt=conn.prepareStatement(sql);
    		ResultSet rs=pstmt.executeQuery();
    		while(rs.next()){
    			Student student=new Student();
    			student.setId(rs.getString("id"));
    			student.setName(rs.getString("name"));
    			student.setSex(rs.getString("sex"));
    			studentList.add(student);
    		}
    		JdbcUtil.free(conn, pstmt, rs);
    		return studentList;
    	}
    }

Dao.java--servlet函数，用以完成最终功能调用

package bean;
    
    import java.io.File;
    import java.io.IOException;
    import javax.servlet.ServletException;
    import javax.servlet.ServletOutputStream;
    import javax.servlet.annotation.WebServlet;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    import com.oreilly.servlet.MultipartRequest;
    import com.oreilly.servlet.ServletUtils;
    
    /**
     * Servlet implementation class Dao
     */
    @WebServlet("/daorucaochu")
    public class Dao extends HttpServlet {
    	private static final long serialVersionUID = 1L;
    
    	/**
    	 * Default constructor.
    	 */
    	public Dao() {
    		// TODO Auto-generated constructor stub
    	}
    
    	/**
    	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
    	 *      response)
    	 */
    	protected void doGet(HttpServletRequest request, HttpServletResponse response)
    			throws ServletException, IOException {
    		// TODO Auto-generated method stub
    		String action = request.getParameter("action");
    		StudentDao studentdao = new StudentDao();
    		String saveDirectory = this.getServletContext().getRealPath("/file");
    		if ("daoru".equals(action)) {
    			int maxPostSize = 5 * 1024 * 1024;
    			MultipartRequest multi = new MultipartRequest(request, saveDirectory, maxPostSize, "utf-8");
    			File excelfile = multi.getFile("file1");
    			if (excelfile != null) {
    				try {
    					studentdao.importFromexcel(excelfile, 1);
    				} catch (Exception e) {
    					// TODO Auto-generated catch block
    					e.printStackTrace();
    				}}
    				response.setCharacterEncoding("UTF-8");
    				response.sendRedirect("daorudaochu.jsp");
    			} else if ("daochu".equals(action)) {
    				String fileName = "student.xls";
    				try {
    					studentdao.exportToexcel(new File(saveDirectory + "/" + fileName), "");
    					String isofilename = new String(fileName.getBytes("gbk"), "iso-8859-1");
    					response.setContentType("application/octet-stream");
    					response.setHeader("Content-Disposition", "attachment;filename=" + isofilename);
    					ServletOutputStream out = null;
    					out = response.getOutputStream();
    					System.out.println(saveDirectory + "/" + fileName);
    					ServletUtils.returnFile(saveDirectory + "/" + fileName, out);
    					new File(saveDirectory + "/" + fileName).delete();
    				} catch (Exception e) {
    					// TODO Auto-generated catch block
    					e.printStackTrace();
    				
    			}
    		}
    	}
    
    	/**
    	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
    	 *      response)
    	 */
    	protected void doPost(HttpServletRequest request, HttpServletResponse response)
    			throws ServletException, IOException {
    		// TODO Auto-generated method stub
    		doGet(request, response);
    	}
    
    }

daorudaochu.jsp--jsp页面，用以获取显示信息

<%@ page language="java" contentType="text/html; charset=UTF-8"
        pageEncoding="UTF-8"%>
        <%@ page import="bean.StudentDao,java.util.List,bean.Student"%>
        <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
    <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title>表格数据的导入和导出</title>
    </head>
    <body>
    <%
    request.setCharacterEncoding("UTF-8");
    StudentDao studentdao=new StudentDao();
    List<Student> studentList=studentdao.query();
    pageContext.setAttribute("studentList",studentList);
    %>
    <%
        String path = request.getContextPath();
        String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
    %>
    <table>
    <tr><th>学号</th><th>姓名</th><th>性别</th></tr>
    <c:forEach items="${studentList}" var="student">
    <tr><td>${student.id}</td><td>${student.name}</td><td>${student.sex}</td></tr>
    </c:forEach>
    </table>
    <form action="http://localhost:8080/java第十五周/daorucaochu?action=daoru" method="post" enctype="multipart/form-data">
    <input type="file" name="file1"/>
    <input type="submit" value="excel->mysql(导入)"/>
    </form>
    <a href="http://localhost:8080/java第十五周/daorucaochu?action=daochu">mysql->excel(导出)</a>
    </body>
    </html>

当将信息导入服务器时，数据库会获取Excel中的信息并将其加入到数据库，jsp页面信息也会更新，导出时Excel中的信息也会更新到与数据库一致。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjM4MzM1_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjM4MzM1_size_16_color_FFFFFF_t_70 1][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjM4MzM1_size_16_color_FFFFFF_t_70 2][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjM4MzM1_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200603215836260.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjM4MzM1,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjM4MzM1_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200603215849661.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjM4MzM1,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjM4MzM1_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200603215852309.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjM4MzM1,size_16,color_FFFFFF,t_70