「java分析excel」java分析题
本篇文章给大家谈谈java分析excel，以及java分析题对应的知识点，希望对各位有所帮助，不要忘了收藏本站喔。
本文目录一览：
- 1、java 如何解析 excel
- 2、Java对Excel解析(求助)
- 3、JAVA怎么解析excel表中的单元格是下拉框的所有值？
- 4、java 怎么对选中的excel文件进行解析 求详细实例代码
- 5、java 怎样解析 excel生成的xml文件
java 如何解析 excel
用poi，poi是apache的项目，不但能对excel操作，甚至连PDF等其他格式文件都可以任意操作，jxl好像是一个韩国棒子开发的，毕竟他个人能力有限，而且很多大公司不认jxl
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表中的单元格是下拉框的所有值？
添加依赖spire.xls.jar，用下面的代码
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
public class ReadCellContent {
public static void main(String[] args) {
//创建Workbook对象
Workbook wb = new Workbook();
//加载Excel文档
wb.loadFromFile("G:\\360MoveData\\Users\\Administrator\\Desktop\\test.xlsx");
//获取第一个表格
Worksheet worksheet = wb.getWorksheets().get(0);
//获取指定单元格内下拉列表的值
String[] values = worksheet.getCellRange(7,3).getDataValidation().getValues();
for (int i = 0; i values.length; i++) {
System.out.println(values[i]);
}
}
}
测试结果：
java 怎么对选中的excel文件进行解析 求详细实例代码
import Java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
public class ReadExcel {
public static void readExcel(File file){
try {
InputStream inputStream = new FileInputStream(file);
String fileName = file.getName();
Workbook wb = null;
// poi-3.9.jar 只可以读取2007以下的版本，后缀为：xsl
wb = new HSSFWorkbook(inputStream);//解析xls格式
Sheet sheet = wb.getSheetAt(0);//第一个工作表 ，第二个则为1，以此类推...
int firstRowIndex = sheet.getFirstRowNum();
int lastRowIndex = sheet.getLastRowNum();
for(int rIndex = firstRowIndex; rIndex = lastRowIndex; rIndex ++){
Row row = sheet.getRow(rIndex);
if(row != null){
int firstCellIndex = row.getFirstCellNum();
// int lastCellIndex = row.getLastCellNum();
//此处参数cIndex决定可以取到excel的列数。
for(int cIndex = firstCellIndex; cIndex 3; cIndex ++){
Cell cell = row.getCell(cIndex);
String value = "";
if(cell != null){
value = cell.toString();
System.out.print(value+"\t");
}
}
System.out.println();
}
}
} catch (FileNotFoundException e) {
// TODO 自动生成 catch 块
e.printStackTrace();
} catch (IOException e) {
// TODO 自动生成 catch 块
e.printStackTrace();
}
}
public static void main(String[] args) {
File file = new File("D:/test.xls");
readExcel(file);
}
}
java 怎样解析 excel生成的xml文件
java解析excel生成的xml文件的方法是使用dom4j实现的。
dom4j是一个简单的开源库，用于处理XML、 XPath和XSLT，它基于Java平台，使用Java的集合框架，全面集成了DOM，SAX和JAXP。
1、excel生成的xml样例文件：
?xml version="1.0"?
?mso-application progid="Excel.Sheet"?
Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html=""
DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"
Created2006-09-16T00:00:00Z/Created
LastSaved2016-07-25T03:26:50Z/LastSaved
Version14.00/Version
/DocumentProperties
OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office"
AllowPNG/
RemovePersonalInformation/
/OfficeDocumentSettings
ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"
WindowHeight7956/WindowHeight
WindowWidth14808/WindowWidth
WindowTopX240/WindowTopX
WindowTopY168/WindowTopY
ActiveSheet2/ActiveSheet
ProtectStructureFalse/ProtectStructure
ProtectWindowsFalse/ProtectWindows
/ExcelWorkbook
Styles
Style ss:ID="Default" ss:Name="Normal"
Alignment ss:Vertical="Bottom"/
Borders/
Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#000000"/
Interior/
NumberFormat/
Protection/
/Style
Style ss:ID="s16" ss:Name="好"
Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#006100"/
Interior ss:Color="#C6EFCE" ss:Pattern="Solid"/
/Style
Style ss:ID="s17"
Alignment ss:Horizontal="Left" ss:Vertical="Center" ss:Indent="1"
ss:WrapText="1"/
Font ss:FontName="宋体" x:CharSet="134" ss:Size="8" ss:Color="#686868"/
NumberFormat ss:Format="@"/
/Style
Style ss:ID="s18" ss:Parent="s16"
Alignment ss:Vertical="Bottom"/
/Style
Style ss:ID="s19"
NumberFormat ss:Format="yyyy/m/d\ h:mm:ss"/
/Style
/Styles
Worksheet ss:Name="Sheet1"
Table ss:ExpandedColumnCount="6" ss:ExpandedRowCount="3" x:FullColumns="1"
x:FullRows="1" ss:DefaultRowHeight="14.4"
Row
CellData ss:Type="String"工号/Data/Cell
CellData ss:Type="String"姓名 /Data/Cell
Cell ss:Index="5"Data ss:Type="String"工号/Data/Cell
CellData ss:Type="String"姓名/Data/Cell
/Row
Row
CellData ss:Type="Number"111/Data/Cell
CellData ss:Type="String"张三/Data/Cell
Cell ss:Index="5"Data ss:Type="Number"111/Data/Cell
Cell ss:Formula="=VLOOKUP(R2C5:R3C5,RC[-5]:R[1]C[-4],2)"Data
ss:Type="String"张三/Data/Cell
/Row
Row
CellData ss:Type="Number"112/Data/Cell
CellData ss:Type="String"李四/Data/Cell
Cell ss:Index="5"Data ss:Type="Number"112/Data/Cell
Cell ss:Formula="=VLOOKUP(R2C5:R3C5,RC[-5]:R[1]C[-4],2)"Data
ss:Type="String"李四/Data/Cell
/Row
/Table
WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"
PageSetup
Header x:Margin="0.3"/
Footer x:Margin="0.3"/
PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/
/PageSetup
Panes
Pane
Number3/Number
ActiveRow7/ActiveRow
ActiveCol5/ActiveCol
/Pane
/Panes
ProtectObjectsFalse/ProtectObjects
ProtectScenariosFalse/ProtectScenarios
/WorksheetOptions
/Worksheet
/Workbook
2、java解析代码：
import java.io.File;
import java.util.Iterator;
import org.dom4j.Attribute;
import org.dom4j.Document;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
public class Demo {
public static void main(String[] args) throws Exception {
SAXReader reader = new SAXReader();
Document document = reader.read(new File("person.xml"));
Element root = document.getRootElement();
Iterator it = root.elementIterator();
while (it.hasNext()) {
Element element = (Element) it.next();
//未知属性名称情况下
/*Iterator attrIt = element.attributeIterator();
while (attrIt.hasNext()) {
Attribute a = (Attribute) attrIt.next();
System.out.println(a.getValue());
}*/
//已知属性名称情况下
System.out.println("id: " + element.attributeValue("id"));
//未知元素名情况下
/*Iterator eleIt = element.elementIterator();
while (eleIt.hasNext()) {
Element e = (Element) eleIt.next();
System.out.println(e.getName() + ": " + e.getText());
}
System.out.println();*/
//已知元素名情况下
System.out.println("title: " + element.elementText("title"));
System.out.println("author: " + element.elementText("author"));
System.out.println();
}
}
}
关于java分析excel和java分析题的介绍到此就结束了，不知道你从中找到你需要的信息了吗 ？如果你还想了解更多这方面的信息，记得收藏关注本站。
发布于：2022-11-22，除非注明，否则均为原创文章，转载请注明出处。