java操作Excel文件

JAVA操作Excel:

(1)数据库连接类:

package com.together.nms.dt.data.model.hibernate.db;

import java.sql.*;

public class SheetDataSource {

 private static Connection con;

 @SuppressWarnings("unused")
 private static final String DatabaseName = "uchome";

 private static final String userName = "root";

 private static final String password = "root";

 public SheetDataSource() {
  try {
   Class.forName("com.mysql.jdbc.Driver");
   try {
    con = DriverManager
      .getConnection(
        "jdbc:mysql://localhost:3306/uchome?characterEncoding=gbk",
        userName, password);
   } catch (SQLException e) {
    e.printStackTrace();
   }
  } catch (ClassNotFoundException e) {
   e.printStackTrace();
  }
 }

 public static ResultSet selectAllDataFromDB() throws SQLException {
  new SheetDataSource();
  Statement stmt = con.createStatement();
  return stmt.executeQuery("select * from shop_right");
 }
}

 

(2)操作Excel文件的核心类:

/**
 *  程序功能:将数据库表导入到Excel中
 *  做成日期:2011-07-01
 */
package com.together.nms.dt.data.model.hibernate.dao;

import java.beans.Statement;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.swing.JOptionPane;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFooter;
import org.apache.poi.hssf.usermodel.HSSFHeader;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.landmass.nms.data.model.common.db.ConnectionManager;
import com.together.nms.dt.data.model.hibernate.db.SheetDataSource;

public class ExportExcel {
 // 连接对象
 Connection conn = null;
 // 执行sql语句对象
 Statement stmtStatement = null;
 // 数据集对象
 ResultSet rsResultSet = null;

 // 初始化构造函数
 public ExportExcel() {
 }

 // 创建表头
 public static final String[] tableHeader = { "权限ID", "SHOP_RLEARNINGSTAGE",
   "SHOP_RLEARNINGSTAGEID", "SHOP_RGRADE", "SHOP_RGRADEID",
   "SHOP_RSUBJECT", "SHOP_RSUBJECTID", "SHOP_REDITION",
   "SHOP_REDITIONID", "SHOP_RDIFFERENCE", "SHOP_RDIFFERENCEID",
   "SHOP_MUPATEEDITIONDATE", "SHOP_MUPATEEDITIONDATEID", "SHOP_BOOKID" };

 // 创建工作薄
 public static HSSFWorkbook demoWorkBook = new HSSFWorkbook();

 // 创建表
 public static HSSFSheet demoSheet = demoWorkBook.createSheet("权限表");

 // 表头的单元格个数目
 public static final short cellNumber = (short) tableHeader.length;

 // 数据库表的列数
 public static final int columNumber = 14;

 /**
  * 创建表头
  */
 public static void createTableHeader() {

  HSSFHeader header = demoSheet.getHeader();
  header.setCenter("权限表");
  HSSFRow headerRow = demoSheet.createRow((short) 0);
  for (int i = 0; i < cellNumber; i++) {
   HSSFCell headerCell = headerRow.createCell((short) i);
   headerCell.setEncoding(HSSFCell.ENCODING_UTF_16);
   headerCell.setCellValue(tableHeader[i]);
  }
 }

 // 创建行
 public static void createTableRow(List<String> cells, short rowIndex) {

  // 创建第rowIndex行
  HSSFRow row = demoSheet.createRow((short) rowIndex);
  for (short i = 0; i < cells.size(); i++) {
   // 创建第i个单元格
   HSSFCell cell = row.createCell((short) i);
   cell.setEncoding(HSSFCell.ENCODING_UTF_16);
   cell.setCellValue(cells.get(i));
  }
 }

 // 创建整个Excel表格
 @SuppressWarnings("unused")
 public static void createExcelSheeet() throws SQLException {

  createTableHeader(); // 创建表头
  ConnectionManager conn = new ConnectionManager();
  ResultSet rs = SheetDataSource.selectAllDataFromDB(); // 取得数据库连接
  int rowIndex = 1;
  while (rs.next()) {
   List<String> list = new ArrayList<String>();
   for (int i = 1; i <= columNumber; i++) {
    list.add(rs.getString(i));
   }
   createTableRow(list, (short) rowIndex);
   rowIndex++;
  }
 }

 // 导出表格
 public void exportExcel(HSSFSheet sheet, OutputStream os)
   throws IOException {
  sheet.setGridsPrinted(true);
  HSSFFooter footer = sheet.getFooter();
  footer.setRight("Page " + HSSFFooter.page() + " of "
    + HSSFFooter.numPages());
  demoWorkBook.write(os);
 }

 // 测试主函数
 @SuppressWarnings("static-access")
 public static void main(String[] args) {
  // 定义excel名称
  String fileName = "D:\\shop_right.xls";
  // 文件输出流
  FileOutputStream fos = null;
  try {
   ExportExcel pd = new ExportExcel();
   pd.createExcelSheeet();
   fos = new FileOutputStream(fileName);
   pd.exportExcel(demoSheet, fos);
   JOptionPane.showMessageDialog(null, "EXCEL表格已成功导出到 : " + fileName);
  } catch (Exception e) {
   JOptionPane.showMessageDialog(null, "EXCEL表格导出出错,错误信息 :" + e
     + "\n错误原因可能是表格已经打开。");
   e.printStackTrace();
  } finally {
   try {
    fos.close();
   } catch (Exception e) {
    e.printStackTrace();
   }
  }
 }
}

这里使用的是Apathe的poi对excel进行操作.

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Java可以使用多种库来操作Excel文件,其中包括EasyExcel和Easypoi。EasyExcel是阿里巴巴开源的一个简单易用的Excel操作工具,可以方便地读取和写入Excel文件。你可以使用EasyExcel的read方法来读取Excel文件,如下所示: ```java String filePath = "/Users/lixin/Desktop/easyexcel-user1.xls"; List<DemoData> list = EasyExcel.read(filePath).head(UserEntity.class).sheet().doReadSync(); System.out.println(JSONArray.toJSONString(list)); ``` 这段代码使用EasyExcel读取了指定路径的Excel文件,并将数据转换为DemoData对象的列表。你可以根据需要对读取到的数据进行进一步的处理和操作。\[1\] 另外,如果你需要将Excel文件导入到数据库中,你可以使用Easypoi库。Easypoi对定制化的导出支持非常丰富,适用于需要导出样式千差万别的Excel文件。下面是一个使用Easypoi将Excel文件导入到数据库的示例代码: ```java public ActionForward excelToDb(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) { UploadExcelForm uploadexcelForm = (UploadExcelForm) form; FormFile ff = uploadexcelForm.getFexcel(); System.out.println("载入的文件对象为:" + ff + "<end"); InputStream in = null; try { in = ff.getInputStream(); List list = employegongzicard.excelToDb(in); request.setAttribute("failedlist", list); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return mapping.findForward("exceltodb"); } ``` 这段代码使用Easypoi将上传的Excel文件转换为输入流,并调用excelToDb方法将数据导入到数据库中。你可以根据具体的需求对导入的数据进行处理和操作。\[2\] 综上所述,如果你的项目需求对导出样式有较高的要求,可以使用Easypoi;如果并发量和数据量较大,可以使用EasyExcel。\[3\] #### 引用[.reference_title] - *1* *3* [使用Java操作excel的几种方法](https://blog.csdn.net/qq1808814025/article/details/115294105)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^control_2,239^v3^insert_chatgpt"}} ] [.reference_item] - *2* [javaExcel操作 ](https://blog.csdn.net/lenhan12345/article/details/1769869)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^control_2,239^v3^insert_chatgpt"}} ] [.reference_item] [ .reference_list ]
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值