Java parsing Excel, Excel with merge unit

Source: Internet
Author: User

First, Mavn import the jar package

<!--parse the jar package that Excel needs to import begin--
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.11</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.11</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.11</version>
</dependency>
<dependency>
<groupId>stax</groupId>
<artifactId>stax-api</artifactId>
<version>1.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>2.6.0</version>
</dependency>
<!--parse the jar package that Excel needs to import end---


Second, the code is as follows:

Package Excel;

Import Java.io.File;
Import java.io.IOException;

Import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
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;
Import Org.apache.poi.ss.usermodel.WorkbookFactory;
Import org.apache.poi.ss.util.CellRangeAddress;
Import Org.junit.Test;

/**
*
* @author Wcyong
*
* @date 2013-6-21
*/
public class Testreadmergeregionexcel {


@Test
public void Testreadexcel () {
Readexceltoobj ("C:\\users\\administrator\\desktop\\11.xls");
}

/**
* Read Excel data
* @param path
*/
private void Readexceltoobj (String path) {

Workbook wb = null;
try {
WB = Workbookfactory.create (The new File (path));
Readexcel (WB, 0, 0, 0);
} catch (Invalidformatexception e) {
E.printstacktrace ();
} catch (IOException e) {
E.printstacktrace ();
}
}

/**
* Read Excel File
* @param WB
* @param sheetindex Sheet page subscript: starting from 0
* @param startreadline start reading line: starting from 0
* @param tailline to remove last fetched rows
*/
private void Readexcel (Workbook wb,int sheetindex, int startreadline, int tailline) {

Sheet Sheet = Wb.getsheetat (Sheetindex);
row row = null;

for (int i=startreadline; I<sheet.getlastrownum ()-tailline+1; i++) {

row = Sheet.getrow (i);
for (Cell C:row) {
C.setcelltype (cell.cell_type_string);
Boolean ismerge = ismergedregion (sheet, I, C.getcolumnindex ());
Determine if you have merged cells
if (Ismerge) {
String rs = getmergedregionvalue (sheet, Row.getrownum (), C.getcolumnindex ());
System.out.print (rs + "");
}else {
System.out.print (C.getrichstringcellvalue () + "");
}
}
System.out.println ();

}

}

/**
* Gets the value of the merged cell
* @param sheet
* @param row
* @param column
* @return
*/
Public String Getmergedregionvalue (Sheet Sheet, int row, int column) {

int sheetmergecount = Sheet.getnummergedregions ();

for (int i = 0; i < Sheetmergecount; i++) {
Cellrangeaddress CA = sheet.getmergedregion (i);
int firstcolumn = Ca.getfirstcolumn ();
int lastcolumn = Ca.getlastcolumn ();
int firstrow = Ca.getfirstrow ();
int lastrow = Ca.getlastrow ();

if (row >= firstrow && row <= lastrow) {

if (column >= firstcolumn && column <= lastcolumn) {
Row frow = Sheet.getrow (firstrow);
Cell Fcell = Frow.getcell (Firstcolumn);
Return Getcellvalue (Fcell);
}
}
}

return null;
}

/**
* Determine the merged rows
* @param sheet
* @param row
* @param column
* @return
*/
Private Boolean Ismergedrow (Sheet sheet,int row, int column) {

int sheetmergecount = Sheet.getnummergedregions ();
for (int i = 0; i < Sheetmergecount; i++) {
Cellrangeaddress range = sheet.getmergedregion (i);
int firstcolumn = Range.getfirstcolumn ();
int lastcolumn = Range.getlastcolumn ();
int firstrow = Range.getfirstrow ();
int lastrow = Range.getlastrow ();
if (row = = FirstRow && row = = lastrow) {
if (column >= firstcolumn && column <= lastcolumn) {
return true;
}
}
}
return false;
}

/**
* Determines whether the specified cell is a merged cell
* @param sheet
* @param row subscript
* @param column subscript
* @return
*/
Private Boolean ismergedregion (Sheet sheet,int row, int column) {

int sheetmergecount = Sheet.getnummergedregions ();
for (int i = 0; i < Sheetmergecount; i++) {

Cellrangeaddress range = sheet.getmergedregion (i);
int firstcolumn = Range.getfirstcolumn ();
int lastcolumn = Range.getlastcolumn ();
int firstrow = Range.getfirstrow ();
int lastrow = Range.getlastrow ();
if (row >= firstrow && row <= lastrow) {
if (column >= firstcolumn && column <= lastcolumn) {
return true;
}
}
}
return false;
}

/**
* Determine if the sheet page contains merged cells
* @param sheet
* @return
*/
Private Boolean hasmerged (Sheet Sheet) {
return sheet.getnummergedregions () > 0? True:false;
}

/**
* Merge Cells
* @param sheet
* @param firstrow start line
* @param lastrow End Line
* @param firstcol Start column
* @param lastcol End Column
*/
private void Mergeregion (Sheet Sheet, int firstrow, int lastrow, int firstcol, int lastcol) {
Sheet.addmergedregion (New Cellrangeaddress (FirstRow, LastRow, Firstcol, lastcol));
}

/**
* Get the value of a cell
* @param cell
* @return
*/
Public String Getcellvalue (cell cell) {

if (cell = = null) return "";

if (cell.getcelltype () = = cell.cell_type_string) {

return Cell.getstringcellvalue ();

}else if (cell.getcelltype () = = Cell.cell_type_boolean) {

Return string.valueof (Cell.getbooleancellvalue ());

}else if (cell.getcelltype () = = Cell.cell_type_formula) {

return Cell.getcellformula ();

}else if (cell.getcelltype () = = Cell.cell_type_numeric) {

Return string.valueof (Cell.getnumericcellvalue ());

}
Return "";
}
}

Java parsing Excel, Excel with merge cells

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.