關於exceljava的信息

本文目錄一覽：

1、Java操作excel的問題
2、淺談JAVA讀寫Excel的幾種途徑
3、Java對Excel解析(求助)
4、java怎麼讀取excel數據
5、java怎樣輸出excel文件

Java操作excel的問題

Java創建excel表格的基本步驟：

1.創建一個WritableWorkbook對象(用Workbook的createWorkbook方法創建)，要指定創建一個文件；

2.創建一個工作表WritableSheet(用workbook對象的createSheet方法創建)，注意要是WritableSheet，說明可以對其寫；

3.創建單元格，再將單元格加入到sheet里；

4.執行workbook的write()方法進行寫操作最後關閉workbook。

淺談JAVA讀寫Excel的幾種途徑

需要對Excel中的數據進行讀取操作。一、在開始進行Java讀寫Excel前，需要先下一個jxl的jar包，這個jar包中提供了相關讀寫Excel的方法，將jxl.jar放到classpath下或者在工程的buildpath中添加jxl.jar後，便可以開始Java讀寫Excel了。二、Java讀取Excel數據,首先，創建一個xls文件（如：jxltest.xls），然後在文件中添加一些數據，Excel文件創建完成後，便可以開始寫代碼讀取了。三、進行一個小小的擴展，讀取一個目錄下的所有Excel文件，讀取的每個Excel文件的數據寫入到不同的txt中。四、生成EXCEL需要手動寫查詢語句把ORACLE資料庫中的數據查詢出來，再通過操作寫到EXCEL文件裡面。通過EXCEL把數據讀取到ORACLE，同樣需要去讀取EXCEL工作薄裡面的內容，再通過INSERT語句去插入資料庫操作。

Java對Excel解析(求助)

這篇blog主要是講述java中poi讀取excel，而excel的版本包括：2003-2007和2010兩個版本，即excel的後綴名為：xls和xlsx。

讀取excel和MySQL相關： java的poi技術讀取Excel數據到MySQL

代碼如下

/**

package com.b510.common;

/**

* @author Hongten

* @created 2014-5-21

public class Common {

public static final String OFFICE_EXCEL_2003_POSTFIX = “xls”;

public static final String OFFICE_EXCEL_2010_POSTFIX = “xlsx”;

public static final String EMPTY = “”;

public static final String POINT = “.”;

public static final String LIB_PATH = “lib”;

public static final String STUDENT_INFO_XLS_PATH = LIB_PATH + “/student_info” + POINT + OFFICE_EXCEL_2003_POSTFIX;

public static final String STUDENT_INFO_XLSX_PATH = LIB_PATH + “/student_info” + POINT + OFFICE_EXCEL_2010_POSTFIX;

public static final String NOT_EXCEL_FILE = ” : Not the Excel file!”;

public static final String PROCESSING = “Processing…”;

}

/**

package com.b510.excel;

import java.io.FileInputStream;

import java.io.IOException;

import java.io.InputStream;

import java.util.ArrayList;

import java.util.List;

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.poi.xssf.usermodel.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.b510.common.Common;

import com.b510.excel.util.Util;

import com.b510.excel.vo.Student;

/**

* @author Hongten

* @created 2014-5-20

public class ReadExcel {

/**

* read the Excel file

* @param path the path of the Excel file

* @return

* @throws IOException

public ListStudent readExcel(String path) throws IOException {

if (path == null || Common.EMPTY.equals(path)) {

return null;

} else {

String postfix = Util.getPostfix(path);

if (!Common.EMPTY.equals(postfix)) {

if (Common.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {

return readXls(path);

} else if (Common.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {

return readXlsx(path);

}

} else {

System.out.println(path + Common.NOT_EXCEL_FILE);

}

return null;

}

/**

* Read the Excel 2010

* @param path the path of the excel file

* @return

* @throws IOException

public ListStudent readXlsx(String path) throws IOException {

System.out.println(Common.PROCESSING + path);

InputStream is = new FileInputStream(path);

XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);

Student student = null;

ListStudent list = new ArrayListStudent();

// Read the Sheet

for (int numSheet = 0; numSheet xssfWorkbook.getNumberOfSheets(); numSheet++) {

XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);

if (xssfSheet == null) {

continue;

}

// Read the Row

for (int rowNum = 1; rowNum = xssfSheet.getLastRowNum(); rowNum++) {

XSSFRow xssfRow = xssfSheet.getRow(rowNum);

if (xssfRow != null) {

student = new Student();

XSSFCell no = xssfRow.getCell(0);

XSSFCell name = xssfRow.getCell(1);

XSSFCell age = xssfRow.getCell(2);

XSSFCell score = xssfRow.getCell(3);

student.setNo(getValue(no));

student.setName(getValue(name));

student.setAge(getValue(age));

student.setScore(Float.valueOf(getValue(score)));

list.add(student);

}

return list;

}

/**

* Read the Excel 2003-2007

* @param path the path of the Excel

* @return

* @throws IOException

public ListStudent readXls(String path) throws IOException {

System.out.println(Common.PROCESSING + path);

InputStream is = new FileInputStream(path);

HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);

Student student = null;

ListStudent list = new ArrayListStudent();

// Read the Sheet

for (int numSheet = 0; numSheet hssfWorkbook.getNumberOfSheets(); numSheet++) {

HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);

if (hssfSheet == null) {

continue;

}

// Read the Row

for (int rowNum = 1; rowNum = hssfSheet.getLastRowNum(); rowNum++) {

HSSFRow hssfRow = hssfSheet.getRow(rowNum);

if (hssfRow != null) {

student = new Student();

HSSFCell no = hssfRow.getCell(0);

HSSFCell name = hssfRow.getCell(1);

HSSFCell age = hssfRow.getCell(2);

HSSFCell score = hssfRow.getCell(3);

student.setNo(getValue(no));

student.setName(getValue(name));

student.setAge(getValue(age));

student.setScore(Float.valueOf(getValue(score)));

list.add(student);

}

return list;

}

@SuppressWarnings(“static-access”)

private String getValue(XSSFCell xssfRow) {

if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {

return String.valueOf(xssfRow.getBooleanCellValue());

} else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {

return String.valueOf(xssfRow.getNumericCellValue());

} else {

return String.valueOf(xssfRow.getStringCellValue());

}

@SuppressWarnings(“static-access”)

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());

}

/**

package com.b510.excel.client;

import java.io.IOException;

import java.util.List;

import com.b510.common.Common;

import com.b510.excel.ReadExcel;

import com.b510.excel.vo.Student;

/**

* @author Hongten

* @created 2014-5-21

public class Client {

public static void main(String[] args) throws IOException {

String excel2003_2007 = Common.STUDENT_INFO_XLS_PATH;

String excel2010 = Common.STUDENT_INFO_XLSX_PATH;

// read the 2003-2007 excel

ListStudent list = new ReadExcel().readExcel(excel2003_2007);

if (list != null) {

for (Student student : list) {

System.out.println(“No. : ” + student.getNo() + “, name : ” + student.getName() + “, age : ” + student.getAge() + “, score : ” + student.getScore());

}

System.out.println(“======================================”);

// read the 2010 excel

ListStudent list1 = new ReadExcel().readExcel(excel2010);

if (list1 != null) {

for (Student student : list1) {

System.out.println(“No. : ” + student.getNo() + “, name : ” + student.getName() + “, age : ” + student.getAge() + “, score : ” + student.getScore());

}

/**

package com.b510.excel.util;

import com.b510.common.Common;

/**

* @author Hongten

* @created 2014-5-21

public class Util {

/**

* get postfix of the path

* @param path

* @return

public static String getPostfix(String path) {

if (path == null || Common.EMPTY.equals(path.trim())) {

return Common.EMPTY;

}

if (path.contains(Common.POINT)) {

return path.substring(path.lastIndexOf(Common.POINT) + 1, path.length());

}

return Common.EMPTY;

}

/**

package com.b510.excel.vo;

/**

* Student

* @author Hongten

* @created 2014-5-18

public class Student {

/**

* id

private Integer id;

/**

* 學號

private String no;

/**

* 姓名

private String name;

/**

* 學院

private String age;

/**

* 成績

private float score;

public Integer getId() {

return id;

}

public void setId(Integer id) {

this.id = id;

}

public String getNo() {

return no;

}

public void setNo(String no) {

this.no = no;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getAge() {

return age;

}

public void setAge(String age) {

this.age = age;

}

public float getScore() {

return score;

}

public void setScore(float score) {

this.score = score;

}

java怎麼讀取excel數據

引入poi的jar包，大致如下：

讀取代碼如下，應該能看得明白吧

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.io.InputStream;

import java.math.BigDecimal;

import java.text.DecimalFormat;

import java.text.SimpleDateFormat;

import org.apache.poi.xssf.usermodel.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtil2007 {

/**讀取excel文件流的指定索引的sheet

* @param inputStream excel文件流

* @param sheetIndex 要讀取的sheet的索引

* @return

* @throws FileNotFoundException

* @throws IOException

public static XSSFSheet readExcel(InputStream inputStream,int sheetIndex) throws FileNotFoundException, IOException

{

return readExcel(inputStream).getSheetAt(sheetIndex);

}

/**讀取excel文件的指定索引的sheet

* @param filePath excel文件路徑

* @param sheetIndex 要讀取的sheet的索引

* @return

* @throws IOException

* @throws FileNotFoundException

public static XSSFSheet readExcel(String filePath,int sheetIndex) throws FileNotFoundException, IOException

{

return readExcel(filePath).getSheetAt(sheetIndex);

}

/**讀取excel文件的指定索引的sheet

* @param filePath excel文件路徑

* @param sheetName 要讀取的sheet的名稱

* @return

* @throws IOException

* @throws FileNotFoundException

public static XSSFSheet readExcel(String filePath,String sheetName) throws FileNotFoundException, IOException

{

return readExcel(filePath).getSheet(sheetName);

}

/**讀取excel文件，返回XSSFWorkbook對象

* @param filePath excel文件路徑

* @return

* @throws FileNotFoundException

* @throws IOException

public static XSSFWorkbook readExcel(String filePath) throws FileNotFoundException, IOException

{

XSSFWorkbook wb=new XSSFWorkbook(new FileInputStream(filePath));

return wb;

}

/**讀取excel文件流，返回XSSFWorkbook對象

* @param inputStream excel文件流

* @return

* @throws FileNotFoundException

* @throws IOException

public static XSSFWorkbook readExcel(InputStream inputStream) throws FileNotFoundException, IOException

{

XSSFWorkbook wb=new XSSFWorkbook(inputStream);

return wb;

}

/***讀取excel中指定的單元格，並返回字元串形式的值

* 1.數字

* 2.字元

* 3.公式（返回的為公式內容，非單元格的值）

* 4.空

* @param st 要讀取的sheet對象

* @param rowIndex 行索引

* @param colIndex 列索引

* @param isDate 是否要取的是日期（是則返回yyyy-MM-dd格式的字元串）

* @return

public static String getCellString(XSSFSheet st,int rowIndex,int colIndex,boolean isDate){

String s=””;

XSSFRow row=st.getRow(rowIndex);

if(row == null) return “”;

XSSFCell cell=row.getCell(colIndex);

if(cell == null) return “”;

if (cell.getCellType() == 0) {//數字

if(isDate)s=new SimpleDateFormat(“yyyy-MM-dd”).format(cell.getDateCellValue());

else s = trimPointO(String.valueOf(getStringValue(cell)).trim());

}else if (cell.getCellType() == 1){//字元（excel中的空格，不是全形，也不是半形，不知道是神馬,反正就是” “這個）

s=cell.getRichStringCellValue().getString().replaceAll(” “, ” “).trim();

// s=cell.getStringCellValue();//07API新增，好像跟上一句一致

}

else if (cell.getCellType() == 2){//公式

s=cell.getCellFormula();

}

else if (cell.getCellType() == 3){//空

s=””;

}

return s;

}

/**如果數字以 .0 結尾，則去掉.0

* @param s

* @return

public static String trimPointO(String s) {

if (s.endsWith(“.0”))

return s.substring(0, s.length() – 2);

else

return s;

}

/**處理科學計數法和百分比模式的數字單元格

* @param cell

* @return

public static String getStringValue(XSSFCell cell) {

String sValue = null;

short dataFormat = cell.getCellStyle().getDataFormat();

double d = cell.getNumericCellValue();

BigDecimal b = new BigDecimal(Double.toString(d));

//百分比樣式的

if (dataFormat == 0xa || dataFormat == 9) {

b=b.multiply(new BigDecimal(100));

//String temp=b.toPlainString();

DecimalFormat df=new DecimalFormat(“0.00”);//保留兩位小數的百分比格式

sValue = df.format(b) + “%”;

}else{

sValue = b.toPlainString();

}

return sValue;

}

java怎樣輸出excel文件

//java生成簡單的Excel文件

package beans.excel;

import java.io.IOException;

import java.io.OutputStream;

import jxl.Workbook;

import jxl.write.Label;

import jxl.write.WritableSheet;

import jxl.write.WritableWorkbook;

import jxl.write.WriteException;

public class SimpleExcelWrite {

public void createExcel(OutputStream os) throws WriteException,IOException{

//創建工作薄

WritableWorkbook workbook = Workbook.createWorkbook(os);

//創建新的一頁

WritableSheet sheet = workbook.createSheet(“First Sheet”,0);

//創建要顯示的內容,創建一個單元格，第一個參數為列坐標，第二個參數為行坐標，第三個參數為內容

Label xuexiao = new Label(0,0,”學校”);

sheet.addCell(xuexiao);

Label zhuanye = new Label(1,0,”專業”);

sheet.addCell(zhuanye);

Label jingzhengli = new Label(2,0,”專業競爭力”);

sheet.addCell(jingzhengli);

Label qinghua = new Label(0,1,”清華大學”);

sheet.addCell(qinghua);

Label jisuanji = new Label(1,1,”計算機專業”);

sheet.addCell(jisuanji);

Label gao = new Label(2,1,”高”);

sheet.addCell(gao);

Label beida = new Label(0,2,”北京大學”);

sheet.addCell(beida);

Label falv = new Label(1,2,”法律專業”);

sheet.addCell(falv);

Label zhong = new Label(2,2,”中”);

sheet.addCell(zhong);

Label ligong = new Label(0,3,”北京理工大學”);

sheet.addCell(ligong);

Label hangkong = new Label(1,3,”航空專業”);

sheet.addCell(hangkong);

Label di = new Label(2,3,”低”);

sheet.addCell(di);

//把創建的內容寫入到輸出流中，並關閉輸出流

workbook.write();

workbook.close();

os.close();

}

原創文章，作者：小藍，如若轉載，請註明出處：https://www.506064.com/zh-tw/n/291912.html

關於exceljava的信息

本文目錄一覽：

Java操作excel的問題

淺談JAVA讀寫Excel的幾種途徑

Java對Excel解析(求助)

java怎麼讀取excel數據

java怎樣輸出excel文件

相關推薦

發表回復