You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by sunnykeerthi <su...@gmail.com> on 2016/05/24 09:04:16 UTC
Unable to understand how to merge Rows in POI
Hi,
I'm writing a program where I need to merge rows in Excel sheet. Currently,
I'm able to merge the starting rows, but when coming to the end, I'm unable
to know where it is going wrong. Below is my code.
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
public class RowsMerge {
public static void main(String[] args) throws IOException {
FileInputStream fin = new FileInputStream(
new File("C:\\D\\Sheets\\Sample Sheets\\dummy.xls"));
HSSFWorkbook workbook = new HSSFWorkbook(fin);
HSSFSheet sheet = workbook.getSheetAt(0);
int row = sheet.getPhysicalNumberOfRows();
String currentLawName, currentCountry, currentAssociate,
previousLawName, previousCountry, previousAssociate;
String currentPages, previousPages;
int startIndex = 1, finalIndex = 0, tempNum = 0;
System.out.println(row);
for (int i = 2; i < (row - 1); i++) {
currentAssociate = sheet.getRow(i).getCell(0).toString();
currentLawName = sheet.getRow(i).getCell(1).toString();
currentCountry = sheet.getRow(i).getCell(2).toString();
currentPages = sheet.getRow(i).getCell(3).toString();
previousAssociate = sheet.getRow(i - 1).getCell(0).toString();
previousLawName = sheet.getRow(i - 1).getCell(1).toString();
previousCountry = sheet.getRow(i - 1).getCell(2).toString();
previousPages = sheet.getRow(i - 1).getCell(3).toString();
if (currentAssociate.equals(previousAssociate) &&
currentCountry.equals(previousCountry)
&& currentLawName.equals(previousLawName) &&
currentPages.equals(previousPages)) {
finalIndex += 1;
} else {
sendRangeToMergeCells(startIndex, finalIndex, sheet);
startIndex = i;
finalIndex = 0;
}
}
FileOutputStream fileOut = new
FileOutputStream("C:\\D\\Sheets\\Sample Sheets\\dummy.xls");
workbook.write(fileOut);
fileOut.close();
}
private static void sendRangeToMergeCells(int startIndex, int
finalIndex, HSSFSheet sheet) {
System.out.println(startIndex + "\t" + (startIndex + finalIndex));
CellRangeAddress region = CellRangeAddress
.valueOf("D" + (startIndex + 1) + ":D" + ((startIndex +
finalIndex) + 1));
sheet.addMergedRegion(region);
}
}
Below is my Excel Sheet
SourceExcel:
<http://apache-poi.1045710.n5.nabble.com/file/n5723107/ExcelSrc.png>
Current O/P:
<http://apache-poi.1045710.n5.nabble.com/file/n5723107/currentOP.png>
Expected o/p:
<http://apache-poi.1045710.n5.nabble.com/file/n5723107/expectedOP.png>
please let me know where am I going wrong and how can I fix this.
Thanks
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Unable-to-understand-how-to-merge-Rows-in-POI-tp5723107.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Unable to understand how to merge Rows in POI
Posted by sunnykeerthi <su...@gmail.com>.
Thanks Sir , this worked
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Unable-to-understand-how-to-merge-Rows-in-POI-tp5723107p5723119.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Unable to understand how to merge Rows in POI
Posted by "Murphy, Mark" <mu...@metalexmfg.com>.
You are not calling the merge method after your for loop when finalIndex > 0.
Immediately following the for loop you need to add:
If (finalIndex > 0) {
sendRangeToMergeCells(startIndex, finalIndex, sheet);
}
-----Original Message-----
From: sunnykeerthi [mailto:sunnykeerthi@gmail.com]
Sent: Tuesday, May 24, 2016 5:04 AM
To: user@poi.apache.org
Subject: Unable to understand how to merge Rows in POI
Hi,
I'm writing a program where I need to merge rows in Excel sheet. Currently, I'm able to merge the starting rows, but when coming to the end, I'm unable to know where it is going wrong. Below is my code.
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
public class RowsMerge {
public static void main(String[] args) throws IOException {
FileInputStream fin = new FileInputStream(
new File("C:\\D\\Sheets\\Sample Sheets\\dummy.xls"));
HSSFWorkbook workbook = new HSSFWorkbook(fin);
HSSFSheet sheet = workbook.getSheetAt(0);
int row = sheet.getPhysicalNumberOfRows();
String currentLawName, currentCountry, currentAssociate, previousLawName, previousCountry, previousAssociate;
String currentPages, previousPages;
int startIndex = 1, finalIndex = 0, tempNum = 0;
System.out.println(row);
for (int i = 2; i < (row - 1); i++) {
currentAssociate = sheet.getRow(i).getCell(0).toString();
currentLawName = sheet.getRow(i).getCell(1).toString();
currentCountry = sheet.getRow(i).getCell(2).toString();
currentPages = sheet.getRow(i).getCell(3).toString();
previousAssociate = sheet.getRow(i - 1).getCell(0).toString();
previousLawName = sheet.getRow(i - 1).getCell(1).toString();
previousCountry = sheet.getRow(i - 1).getCell(2).toString();
previousPages = sheet.getRow(i - 1).getCell(3).toString();
if (currentAssociate.equals(previousAssociate) &&
currentCountry.equals(previousCountry)
&& currentLawName.equals(previousLawName) &&
currentPages.equals(previousPages)) {
finalIndex += 1;
} else {
sendRangeToMergeCells(startIndex, finalIndex, sheet);
startIndex = i;
finalIndex = 0;
}
}
FileOutputStream fileOut = new
FileOutputStream("C:\\D\\Sheets\\Sample Sheets\\dummy.xls");
workbook.write(fileOut);
fileOut.close();
}
private static void sendRangeToMergeCells(int startIndex, int finalIndex, HSSFSheet sheet) {
System.out.println(startIndex + "\t" + (startIndex + finalIndex));
CellRangeAddress region = CellRangeAddress
.valueOf("D" + (startIndex + 1) + ":D" + ((startIndex +
finalIndex) + 1));
sheet.addMergedRegion(region);
}
}
Below is my Excel Sheet
SourceExcel:
<http://apache-poi.1045710.n5.nabble.com/file/n5723107/ExcelSrc.png>
Current O/P:
<http://apache-poi.1045710.n5.nabble.com/file/n5723107/currentOP.png>
Expected o/p:
<http://apache-poi.1045710.n5.nabble.com/file/n5723107/expectedOP.png>
please let me know where am I going wrong and how can I fix this.
Thanks
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Unable-to-understand-how-to-merge-Rows-in-POI-tp5723107.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org