In this article, I am going to explain how to merge two excel files. Some time we may need to do excel merging programmitically. If you want to do any modification by logic in merging, you can do using this program easily. To implement this program you need to download Apache POI library and have it in your build path (copy POI jars to lib folder).
There is no direct method to merge excel files using java. You have need to follow the below flow.
Click Here To Download Eclipse Project
Click Here To Download Eclipse Project
Flow of the program
- Get Excel files
- Get Work Books of those excel files
- Get sheets to be merged in those excel files
- Read every row and add it to other sheet
- Read every cell and add it to the row
- Write merged workbook to output file
Java Code
import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.util.HashMap; import java.util.Iterator; import java.util.Map; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class MergeExcel { public static void main(String[] args) { try { // excel files FileInputStream excellFile1 = new FileInputStream(new File( "C:\\excel1.xlsx")); FileInputStream excellFile2 = new FileInputStream(new File( "C:\\excel2.xlsx")); // Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook1 = new XSSFWorkbook(excellFile1); XSSFWorkbook workbook2 = new XSSFWorkbook(excellFile2); // Get first/desired sheet from the workbook XSSFSheet sheet1 = workbook1.getSheetAt(0); XSSFSheet sheet2 = workbook2.getSheetAt(0); // add sheet2 to sheet1 addSheet(sheet1, sheet2); excellFile1.close(); // save merged file File mergedFile = new File( "C:\\merged.xlsx"); if (!mergedFile.exists()) { mergedFile.createNewFile(); } FileOutputStream out = new FileOutputStream(mergedFile); workbook1.write(out); out.close(); System.out .println("Files were merged succussfully"); } catch (Exception e) { e.printStackTrace(); } } public static void addSheet(XSSFSheet mergedSheet, XSSFSheet sheet) { // map for cell styles Map<Integer, XSSFCellStyle> styleMap = new HashMap<Integer, XSSFCellStyle>(); // This parameter is for appending sheet rows to mergedSheet in the end int len = mergedSheet.getLastRowNum(); for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) { XSSFRow row = sheet.getRow(j); XSSFRow mrow = mergedSheet.createRow(len + j + 1); for (int k = row.getFirstCellNum(); k < row.getLastCellNum(); k++) { XSSFCell cell = row.getCell(k); XSSFCell mcell = mrow.createCell(k); if (cell.getSheet().getWorkbook() == mcell.getSheet() .getWorkbook()) { mcell.setCellStyle(cell.getCellStyle()); } else { int stHashCode = cell.getCellStyle().hashCode(); XSSFCellStyle newCellStyle = styleMap.get(stHashCode); if (newCellStyle == null) { newCellStyle = mcell.getSheet().getWorkbook() .createCellStyle(); newCellStyle.cloneStyleFrom(cell.getCellStyle()); styleMap.put(stHashCode, newCellStyle); } mcell.setCellStyle(newCellStyle); } switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: mcell.setCellFormula(cell.getCellFormula()); break; case HSSFCell.CELL_TYPE_NUMERIC: mcell.setCellValue(cell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_STRING: mcell.setCellValue(cell.getStringCellValue()); break; case HSSFCell.CELL_TYPE_BLANK: mcell.setCellType(HSSFCell.CELL_TYPE_BLANK); break; case HSSFCell.CELL_TYPE_BOOLEAN: mcell.setCellValue(cell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: mcell.setCellErrorValue(cell.getErrorCellValue()); break; default: mcell.setCellValue(cell.getStringCellValue()); break; } } } } }
Hi,
ReplyDeleteCan we merge .xlsx and .xls file into one file??
Does this merge tabels based on common key?
ReplyDeletee.g. Suppose this:
FirstTable.xls which contains columns as: UserID, FirstName, LastName
SecondTable.xls which contains columns as: UserID, Description
Now my question is that does your program merge these 2 tables based on UserID or not?
Hi,
ReplyDeleteNice Post for merge Excel Sheet. I think everyone can not write JAVA Code for Merging Excel Sheet. You Can use synkronizer Excel Tool for Update, Merge and compare excel sheet files.
Thanks
It merges the files but not all headers in row 1. It reads first sheet and put it in output file and then it read another sheet and put the content from second below the first sheet content in the output file. Cn you please help me so that I can have all the content from left to right
ReplyDeleteThanks for sharing This Blog on How to Merge Excel files using java. it's really useful and information blog post. I Think you can also use Synkronizer Excel tool for merge multiple excel file. using this tool, No need to write any JAVA or VBA script. Keep Sharing Good Content on your blog.
ReplyDeleteThanks for sharing.
ReplyDeleteI was stuck but finally got solution from your blog
Can you please give solution to merge multiple excel sheets
ReplyDeleteThanks in advance
Its giving null pointer exception. plz help
ReplyDeleteGood blog.Are you also searching for Help With My Nursing Paper? we are the best solution for you. We are best known for delivering nursing writing services to students without having to break the bank.
ReplyDeleteI want to always read your blogs. I love them Are you also searching for Nursing Pico Writing Help? we are the best solution for you. We are best known for delivering Nursing Pico writing services to students without having to break the bank
ReplyDeleteI am sure this article has touched all the internet people, its really really nice post on building up new webpage. Also, visit my site ํ ํ ์ฌ์ดํธ
ReplyDeleteWow, fantastic weblog structure! How long have you evver been running a blog for?
ReplyDeleteyou made blogging look easy. The total glance of our web site is magnificent, as well as the content!
์นด์ง๋ ธ
์จ๋ผ์ธ๊ฒฝ๋ง
I actually can´t help activities here. Thank you for time properly spent looking over this article. I can´t bear in mind the last moment I´ve bookmarked
ReplyDeleteanything. ์ฌ์คํ ํ
Succeed! It could be one of the most useful blogs we have ever come across on the subject. Excellent info! I’m also an expert in this topic so I can understand your effort very well. Thanks for the huge help. ๋จนํ๊ฒ์ฆ์ฌ์ดํธ
ReplyDeleteYour internet site really feels a great deal of expert touch. I'm still an amateur, so I wish to speak to a professional. My writing is still unsatisfactory, however I desire you to evaluate me by my writing. Please do that for us. ๋ฐ์นด๋ผ์ฌ์ดํธ
ReplyDeleteWhy couldn't I have the same or similar opinions as you? T^T I hope you also visit my blog and give us a good opinion.์จ๋ผ์ธ์ฌ๋กฏ
ReplyDeleteI think your writing will help me, can you come to me once and help? My site is "๋ฉ๊ฐ์ฌ๋กฏ
ReplyDeleteI was looking for another article by chance and found your article์ฌ๋กฏ์ฌ์ดํธ I am writing on this topic, so I think it will help a lot. I leave my blog address below. Please visit once.
ReplyDeleteYou made some good points there. I did a Google search about the topic and found most people will believe your blog. ๋ฉ๋ฆฌํธ์นด์ง๋ ธ
ReplyDeleteThis is the perfect post.casino trแปฑc tuyแบฟn It helped me a lot. If you have time, I hope you come to my site and share your opinions. Have a nice day.
ReplyDeleteAn outstanding post! This guide gives me all the info to get started with JavaScript module syntax. I appreciate every step you shared. Feel free to visit my website;
ReplyDelete์ผ์ค
This is an excellent post I seen thanks to share it. It is really what I wanted to see hope in future you will continue for sharing such a excellent post Feel free to visit my website;
ReplyDelete๊ตญ์ฐ์ผ๋
Good day! This post could not be written any better! Reading this post reminds me of my previous room mate! He always kept chatting about this. I will forward this page to him. Pretty sure he will have a good read. Thanks for sharing. Feel free to visit my website;
ReplyDelete์ผ๋ณธ์ผ๋
Hey, I am so thrilled I found your blog, I am here now and could just like to say thank for a tremendous post and all round interesting website. Please do keep up the great work. I cannot be without visiting your blog again and again. Feel free to visit my website;
ReplyDeleteํ๊ตญ์ผ๋
Pretty! This has been an incredibly wonderful article. Thank you for supplying this information. ํ ํ
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteComprehensive Guide for Popular Online Betting Exchanges and User IDs
ReplyDeleteStay organized and on top of your online betting game with this handy bookmarking guide. Easily access your favorite platforms and user IDs for a seamless betting experience. Explore renowned exchanges like Betbhai9, Dream Exchange, Gold365, 1win, Lotus365, Diamond Exchange, Fairbet7, World777, Sky Exchange, and Silver Exchange.
Keep your login details at your fingertips, ensuring quick and efficient access to your preferred betting destinations. Enhance your betting journey with this user-friendly bookmarking solution and enjoy a hassle-free experience every time you place your bets.
Visit Our Webpages -
Betbhai9 Exchange
Dream Exchange
Gold365 Exchange
1win Exchange
Lotus365 Exchange
Diamond Exchange id
Fairbet7 id
World777 Id
Sky Exchange id
Silver Exchange id
ReplyDeleteExperience the pinnacle of urban living with DLF City Floors. Nestled in prime locations, these meticulously crafted residences offer a perfect blend of style and convenience. Each floor is a testament to DLF's commitment to providing residents with spacious layouts and top-notch facilities.Enjoy the freedom of independent living with DLF Independent Floors. With a focus on privacy and individuality, these residences provide a unique and personalized living experience. Embrace the joy of having your own space while enjoying the benefits of a thriving community.
Visit Our Webpages
DLF city floors
Dlf Arbour
DLF Alameda Floors
DLF Imperial Residences
DLF Platinum Residences
Dlf Privana
DLF Signature Residences
Dlf The Grove
Dlf Arbour 2
dlf the camellias
Comparing Top 10 Online Betting Exchanges: MostBet, Betway, 10cric, Fairplay, 4rabet, Betwinner, Yolo247, Lotus, Tiger, Lucky7 - Uncover the Best Odds and Features.
ReplyDeleteDiscover a world of unparalleled gaming and betting excitement with MostBet Exchange, Betway Exchange, 10cric Exchange, Fairplay Exchange, 4rabet Exchange, Betwinner, Yolo247, Lotus Exchange, Tiger Exchange, and Lucky7 Exchange. Elevate your online betting experience with a diverse range of options, competitive odds, and seamless user interfaces. Explore the next level of gaming sophistication and strategic wagering across various platforms. Unleash your winning potential and indulge in the ultimate exchange experience with these top-tier betting platforms. Bet confidently, play strategically, and redefine your gaming journey with the best in the industry.
Visit our webpages
MostBet Exchange
Betway Exchange
10cric Exchange
Fairplay Exchange
4rabet Exchange
Betwinner
Yolo247
Lotus Exchange
Tiger Exchange
Lucky7 exchange
Comparing Top 10 Online Betting Exchanges: MostBet, Betway, 10cric, Fairplay, 4rabet, Betwinner, Yolo247, Lotus, Tiger, Lucky7 - Uncover the Best Odds and Features.
ReplyDeleteDiscover a world of unparalleled gaming and betting excitement with MostBet Exchange, Betway Exchange, 10cric Exchange, Fairplay Exchange, 4rabet Exchange, Betwinner, Yolo247, Lotus Exchange, Tiger Exchange, and Lucky7 Exchange. Elevate your online betting experience with a diverse range of options, competitive odds, and seamless user interfaces. Explore the next level of gaming sophistication and strategic wagering across various platforms. Unleash your winning potential and indulge in the ultimate exchange experience with these top-tier betting platforms. Bet confidently, play strategically, and redefine your gaming journey with the best in the industry.
Visit our webpages
MostBet Exchange
Betway Exchange
10cric Exchange
Fairplay Exchange
4rabet Exchange
Betwinner
Yolo247
Lotus Exchange
Tiger Exchange
Lucky7 exchange
ReplyDeleteExperience the freedom to personalize your living space. DLF Independent Floors offer a range of customizable options, allowing you to tailor your home to suit your unique taste and preferences.
DLF Floors boast avant-garde design elements that seamlessly integrate with the surrounding landscape. From spacious living areas to stylish kitchens, every detail is curated to provide residents with an unparalleled living experience.
Visit Our Webpages
DLF city floors
Dlf Arbour
DLF Alameda Floors
DLF Imperial Residences
DLF Platinum Residences
Dlf Privana
DLF Signature Residences
Dlf The Grove
Dlf Arbour 2
dlf the camellias