java操作excel常用的两种方式

Excel是我们平时工作中比较常用的用于存储二维表数据的,JAVA也可以直接对Excel进行操作,在这篇博客中将为大家介绍两种操作Excel的方式,分别为:jxl和poi。

(一)jxl
写Excel

  1. import java.io.File;
  2. import java.io.IOException;
  3. import jxl.Workbook;
  4. import jxl.write.Label;
  5. import jxl.write.WritableSheet;
  6. import jxl.write.WritableWorkbook;
  7. import jxl.write.WriteException;
  8. /**
  9. * jxl写Excel
  10. *
  11. * @author jianggujin
  12. *
  13. */
  14. public class JxlWriteDemo
  15. {
  16. public static void main(String[] args) throws IOException, WriteException
  17. {
  18. File xlsFile = new File("jxl.xls");
  19. // 创建一个工作簿
  20. WritableWorkbook workbook = Workbook.createWorkbook(xlsFile);
  21. // 创建一个工作表
  22. WritableSheet sheet = workbook.createSheet("sheet1", 0);
  23. for (int row = 0; row < 10; row++)
  24. {
  25. for (int col = 0; col < 10; col++)
  26. {
  27. // 向工作表中添加数据
  28. sheet.addCell(new Label(col, row, "data" + row + col));
  29. }
  30. }
  31. workbook.write();
  32. workbook.close();
  33. }
  34. }

读Excel

  1. import java.io.File;
  2. import java.io.IOException;
  3. import jxl.Sheet;
  4. import jxl.Workbook;
  5. import jxl.read.biff.BiffException;
  6. /**
  7. * jxl读excel
  8. *
  9. * @author jianggujin
  10. *
  11. */
  12. public class JxlReadDemo
  13. {
  14. public static void main(String[] args) throws BiffException, IOException
  15. {
  16. File xlsFile = new File("jxl.xls");
  17. // 获得工作簿对象
  18. Workbook workbook = Workbook.getWorkbook(xlsFile);
  19. // 获得所有工作表
  20. Sheet[] sheets = workbook.getSheets();
  21. // 遍历工作表
  22. if (sheets != null)
  23. {
  24. for (Sheet sheet : sheets)
  25. {
  26. // 获得行数
  27. int rows = sheet.getRows();
  28. // 获得列数
  29. int cols = sheet.getColumns();
  30. // 读取数据
  31. for (int row = 0; row < rows; row++)
  32. {
  33. for (int col = 0; col < cols; col++)
  34. {
  35. System.out.printf("%10s", sheet.getCell(col, row)
  36. .getContents());
  37. }
  38. System.out.println();
  39. }
  40. }
  41. }
  42. workbook.close();
  43. }
  44. }

(二)poi
写Excel

  1. import java.io.File;
  2. import java.io.FileOutputStream;
  3. import java.io.IOException;
  4. import org.apache.poi.hssf.usermodel.HSSFRow;
  5. import org.apache.poi.hssf.usermodel.HSSFSheet;
  6. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  7. /**
  8. * Poi写Excel
  9. *
  10. * @author jianggujin
  11. *
  12. */
  13. public class PoiWriteDemo
  14. {
  15. public static void main(String[] args) throws IOException
  16. {
  17. // 创建工作薄
  18. HSSFWorkbook workbook = new HSSFWorkbook();
  19. // 创建工作表
  20. HSSFSheet sheet = workbook.createSheet("sheet1");
  21. for (int row = 0; row < 10; row++)
  22. {
  23. HSSFRow rows = sheet.createRow(row);
  24. for (int col = 0; col < 10; col++)
  25. {
  26. // 向工作表中添加数据
  27. rows.createCell(col).setCellValue("data" + row + col);
  28. }
  29. }
  30. File xlsFile = new File("poi.xls");
  31. FileOutputStream xlsStream = new FileOutputStream(xlsFile);
  32. workbook.write(xlsStream);
  33. }
  34. }

读Excel

  1. import java.io.File;
  2. import java.io.IOException;
  3. import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
  4. import org.apache.poi.ss.usermodel.Row;
  5. import org.apache.poi.ss.usermodel.Sheet;
  6. import org.apache.poi.ss.usermodel.Workbook;
  7. import org.apache.poi.ss.usermodel.WorkbookFactory;
  8. /**
  9. * Poi写Excel
  10. *
  11. * @author jianggujin
  12. *
  13. */
  14. public class PoiReadDemo
  15. {
  16. public static void main(String[] args) throws IOException,
  17. InvalidFormatException
  18. {
  19. File xlsFile = new File("poi.xls");
  20. // 获得工作簿
  21. Workbook workbook = WorkbookFactory.create(xlsFile);
  22. // 获得工作表个数
  23. int sheetCount = workbook.getNumberOfSheets();
  24. // 遍历工作表
  25. for (int i = 0; i < sheetCount; i++)
  26. {
  27. Sheet sheet = workbook.getSheetAt(i);
  28. // 获得行数
  29. int rows = sheet.getLastRowNum() + 1;
  30. // 获得列数,先获得一行,在得到改行列数
  31. Row tmp = sheet.getRow(0);
  32. if (tmp == null)
  33. {
  34. continue;
  35. }
  36. int cols = tmp.getPhysicalNumberOfCells();
  37. // 读取数据
  38. for (int row = 0; row < rows; row++)
  39. {
  40. Row r = sheet.getRow(row);
  41. for (int col = 0; col < cols; col++)
  42. {
  43. System.out.printf("%10s", r.getCell(col).getStringCellValue());
  44. }
  45. System.out.println();
  46. }
  47. }
  48. }
  49. }

依赖包

  1. net.sourceforge.jexcelapi
  2. jxl
  3. 2.6.10