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

Flow of the program

  1. Get Excel files
  2. Get Work Books of those excel files
  3. Get sheets to be merged in those excel files
  4. Read every row and add it to other sheet
  5. Read every cell and add it to the row
  6. 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;
                }
            }
        }
    }
}

100 comments:

  1. Hi,
    Can we merge .xlsx and .xls file into one file??

    ReplyDelete
  2. Does this merge tabels based on common key?
    e.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?

    ReplyDelete
  3. Hi,
    Nice 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

    ReplyDelete
  4. 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

    ReplyDelete
  5. Thanks 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.

    ReplyDelete
  6. Thanks for sharing.
    I was stuck but finally got solution from your blog

    ReplyDelete
  7. Can you please give solution to merge multiple excel sheets
    Thanks in advance

    ReplyDelete
  8. Its giving null pointer exception. plz help

    ReplyDelete
  9. Are you the one who studies this subject?? I have a headache with this subject.카지노사이트Looking at your writing was very helpful.

    ReplyDelete
  10. I need you to thank for your season of this awesome 먹튀검증!!! I definately appreciate each and every piece of it and I have you bookmarked to look at new stuff of your blog an absolute necessity read blog!!!!

    ReplyDelete
  11. I am overwhelmed by your post with such a nice topic. Usually I visit your 메이저토토사이트 and get updated through the information you include but today’s blog would be the most appreciable. Well done!

    ReplyDelete
  12. Good 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.

    ReplyDelete
  13. I 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

    ReplyDelete
  14. I would like to thank you for the efforts you have put in penning this site. I’m hoping to view the same high-grade content by you later on as well. In truth, your creative writing abilities has motivated me to get my own, personal website now. 사설놀이터

    ReplyDelete
  15. Hi there, I simply hopped over in your website by way of StumbleUpon. Now not one thing I’d typically learn, but I favored your emotions none the less. Thank you for making something worth reading. 먹튀검증업체

    ReplyDelete
  16. I am constantly surprised by the amount of information accessible on this subject. What you presented was well researched and well written to get your stand on this over to all your readers. Thanks a lot my dear. 토토사이트

    ReplyDelete
  17. I'm so happy to finally find a post with what I want. 안전놀이터순위 You have inspired me a lot. If you are satisfied, please visit my website and leave your feedback.

    ReplyDelete
  18. Of course, your article is good enough, but I thought it would be much better to see professional photos and videos together. There are articles and photos on these topics on my homepage, so please visit and share your opinions. keonhacai

    ReplyDelete
  19. 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. 안전사이트

    ReplyDelete
  20. I am sure this article has touched all the internet people, its really really nice post on building up new webpage. Also, visit my site 토토사이트

    ReplyDelete
  21. Howdy terrific blog! Does running a blog like this take a massive amount work? I have virtually no understanding of coding however I had been hoping to start my
    own blog soon. 경마

    ReplyDelete
  22. Wow, fantastic weblog structure! How long have you evver been running a blog for?
    you made blogging look easy. The total glance of our web site is magnificent, as well as the content!
    카지노
    온라인경마

    ReplyDelete
  23. 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
    anything. 사설토토

    ReplyDelete
  24. Thank you for any other informative blog. Where else may just I am getting that kind of information written in such a perfect method? I have a mission that I’m simply now working on, and I have been on the glance out for such info. 안전놀이터추천

    ReplyDelete
  25. The vacation trades offered are evaluated a variety of in the chosen and simply good value all around the world. Those hostels are normally based towards households which you’ll find accented via charming shores promoting crystal-clear fishing holes, concurrent of one’s Ocean. Hotels Discounts 메이저사이트

    ReplyDelete
  26. Hey what a brilliant post I have come across and believe me I have been searching out for this similar kind of post for past a week and hardly came across this. Thank you very much and will look for more postings from you. 먹튀검증 and I am very happy to see your post just in time and it was a great help. Thank you ! Leave your blog address below. Please visit me anytime!

    ReplyDelete
  27. 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. 먹튀검증사이트


    ReplyDelete
  28. Your 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. 바카라사이트

    ReplyDelete
  29. I always think about what is. It seems to be a perfect article that seems to blow away such worries. 먹튀검증사이트 seems to be the best way to show something. When you have time, please write an article about what means!!

    ReplyDelete
  30. Good morning!! I am also blogging with you. In my blog, articles related to are mainly written, and they are usually called 메이저사이트. If you are curious about , please visit!!

    ReplyDelete
  31. In my opinion, the item you posted is perfect for being selected as the best item of the year. You seem to be a genius to combine 먹튀사이트 and . Please think of more new items in the future!

    ReplyDelete
  32. It's very interesting. And it's fun. This is a timeless article. I also write articles related to , and I run a community related to 메이저놀이터. For more information, please feel free to visit !!

    ReplyDelete
  33. Excellent read, I just passed this onto a friend who was doing a little research on that. And he actually bought me lunch as I found it for him smile Therefore let me rephrase that: Thank you for lunch. 메이저사이트

    ReplyDelete
  34. What a nice post! I'm so happy to read this. 안전놀이터모음 What you wrote was very helpful to me. Thank you. Actually, I run a site similar to you. If you have time, could you visit my site? Please leave your comments after reading what I wrote. If you do so, I will actively reflect your opinion. I think it will be a great help to run my site. Have a good day.

    ReplyDelete
  35. Thanks for the fantastic blog. Where do you get that kind of information written in such a perfect way? Right now I'm doing a noisy presentation and I'm looking for such great information. 온라인바카라

    ReplyDelete
  36. I've been using WordPress on a number of websites for about a year and am worried about switching to another platform. I have heard good things about keonhacai. Is there a way I can transfer all my wordpress content into it? Any help would be really appreciated!

    ReplyDelete
  37. Youre so right. Im there with you. Your weblog is definitely worth a read if anyone comes throughout it. Im lucky I did because now Ive received a whole new view of this. 샌즈카지노


    ReplyDelete
  38. Why 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.온라인슬롯


    ReplyDelete

  39. Hello there! Quick question that’s completely off topic.
    Do you know how to make your site mobile friendly? My website looks weird when viewing from my iphone.
    I’m trying to find a template or plugin that might
    be able to resolve this issue. If you have any recommendations, please share.
    Thank you!

    website:경마


    ReplyDelete
  40. When I read your article on this topic, my first thought seems to be profound and difficult. My site has a discussion board for articles and photos similar to this topic. If you leave a discussion thread on the topic, it will be reflected. 샌즈카지노

    ReplyDelete
  41. I think your writing will help me, can you come to me once and help? My site is "메가슬롯


    ReplyDelete
  42. What a post I've been looking for! I'm very happy to finally read this post. 먹튀검증 Thank you very much. Can I refer to your post on my website? Your post touched me a lot and helped me a lot. If you have any questions, please visit my site and read what kind of posts I am posting. I am sure it will be interesting.

    ReplyDelete
  43. I 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.


    ReplyDelete
  44. I would like to thank you for the efforts you have put in penning this site. I’m hoping to view the same high-grade content by you later on as well. In truth, your creative writing abilities has motivated me to get my own, personal website now. 사설놀이터

    ReplyDelete
  45. You made some good points there. I did a Google search about the topic and found most people will believe your blog. 메리트카지노


    ReplyDelete
  46. I finally found what I was looking for! I'm so happy. 먹튀검증 Your article is what I've been looking for for a long time. I'm happy to find you like this. Could you visit my website if you have time? I'm sure you'll find a post of interest that you'll find interesting.

    ReplyDelete
  47. This 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.


    ReplyDelete
  48. An 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
  49. Hello. We provide helpful information in your life. Take a look and go.This is an impressive post. Come here and take a look at my information.Feel free to visit my website; 일본야동

    ReplyDelete
  50. 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
  51. 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
  52. 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
  53. First of all, thank you for letting me see this information. I think this article can give me a lot of inspiration. I would appreciate 바카라사이트 if you could post more good contents in the future.

    ReplyDelete
  54. We are looking for a lot of data on this item. In the meantime, this is the perfect article I was looking for . Please post a lot about items related to 바카라사이트!!! I am waiting for your article. And when you are having difficulty writing articles, I think you can get a lot of help by visiting my .

    ReplyDelete
  55. I've been troubled for several days with this topic. 바카라사이트, But by chance looking at your post solved my problem! I will leave my blog, so when would you like to visit it?

    ReplyDelete
  56. Hello ! I am the one who writes posts on these topics카지노사이트 I would like to write an article based on your article. When can I ask for a review?

    ReplyDelete
  57. As the Internet develops further in the future, I think we need to collect materials that people might be interested in. Among the data to be collected, your 메가슬롯 will also be included.

    ReplyDelete
  58. I came to this site with the introduction of a friend around me and I was very impressed when I found your writing. I'll come back often after bookmarking! casino trực tuyến

    ReplyDelete
  59. From one day, I noticed that many people post a lot of articles related to 온라인슬롯 . Among them, I think your article is the best among them!!I

    ReplyDelete
  60. That's a really impressive new idea! 메이저토토사이트추천 It touched me a lot. I would love to hear your opinion on my site. Please come to the site I run once and leave a comment. Thank you.

    ReplyDelete
  61. Hello. I'm subscribed to your posts. You inspire me a lot. 바카라사이트 I am so grateful.

    ReplyDelete
  62. Excellent read, I just passed this onto a friend who was doing a little research on that. And he actually bought me lunch as I found it for him smile Therefore let me rephrase that: Thank you for lunch. 메이저사이트

    ReplyDelete
  63. I think a lot of articles related to are disappearing someday. That's why it's very hard to find, but I'm very fortunate to read your writing. When you come to my site, I have collected articles related to 크레이지슬롯 .

    ReplyDelete
  64. It has fully emerged to crown Singapore's southern shores and undoubtedly placed her on the global map of residential landmarks. I still scored the more points than I ever have in a season for GS. I think you would be hard pressed to find somebody with the same consistency I have had over the years so I am happy with that. 메이저토토사이트

    ReplyDelete
  65. This is such a great resource that you are providing and you give it away for free. I love seeing blog that understand the value. Im glad to have found this post as its such an interesting one! I am always on the lookout for quality posts and articles so i suppose im lucky to have found this! I hope you will be adding more in the future. 토토사이트추천

    ReplyDelete
  66. Great information, thanks for sharing it with us 토토사이트

    ReplyDelete
  67. This content is simply exciting and creative. I have been deciding on an institutional move and this has helped me with one aspect. 경마사이트

    ReplyDelete
  68. The details mentioned within the write-up are a number of the top accessible 파친코사이트

    ReplyDelete
  69. Pretty! This has been an incredibly wonderful article. Thank you for supplying this information. 토토

    ReplyDelete
  70. I think your website has a lot of useful knowledge. I'm so thankful for this website.
    I hope that you continue to share a lot of knowledge.
    This is my website.
    넷마블머니상

    ReplyDelete
  71. That was an excellent article. You made some great points and I am grateful for for your information! Take care!
    스포츠토토핫

    ReplyDelete
  72. Great information, thanks for sharing it with us
    메이저토토사이트

    ReplyDelete
  73. Keep up the excellent work and delivering in the group!
    토토사이트웹

    ReplyDelete
  74. I have read so many posts regarding the blogger lovers except this piece of writing is actually a good article, keep it up.
    스포츠토토핫

    ReplyDelete
  75. Wonderful, what a weblog it is! This webpage gives valuable data to us, keep it up.
    메이저토토사이트

    ReplyDelete
  76. It was an awesome post to be sure. I completely delighted in understanding it in my noon. Will without a doubt come and visit this blog all the more frequently. A debt of gratitude is in order for sharing
    토토사이트웹

    ReplyDelete
  77. Thanks for sharing your thoughts on dobry fryzjer.
    Regards 스포츠토토핫

    ReplyDelete
  78. Why 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
    메이저토토사이트

    ReplyDelete
  79. I got this site from my friend who shared with me concerning this
    website and now this time I am visiting this website and reading very informative articles or reviews here.
    토토사이트웹

    ReplyDelete
  80. Your article is very interesting. I think this article has a lot of information needed, looking forward to your new posts.
    바카라사이트윈

    ReplyDelete
  81. Some really useful stuff on here, keep up posting. Cheers.
    토토사이트링크

    ReplyDelete
  82. Having a hard time looking for good and trusted site? I can offer you more and learn more by clicking the link :???We have daebak event everyday!!
    스포츠토토핫

    ReplyDelete
  83. whoah this blog is wonderful i really like studying your articles.
    Keep up the good work! You already know, many persons are searching around for
    this information, you can aid them greatly.
    메이저토토사이트

    ReplyDelete
  84. Wow, amazing blog layout! How long have you been blogging for?
    you made blogging look easy. The overall look of your site is magnificent, let alone the content!
    토토사이트웹

    ReplyDelete
  85. This comment has been removed by the author.

    ReplyDelete
  86. Such an amazing and helpful post this is. I really really love it. It’s so good and so awesome. I am just amazed. I hope that you continue to do your work like this in the future also.
    스포츠토토핫

    ReplyDelete
  87. Great Post for Beginner to understand. I finally found great post here. Thanks for information.keep sharing more articles.
    메이저토토사이트

    ReplyDelete
  88. That is a good tip especially to those new to the blogosphere. Brief but very accurate info… Many thanks for sharing this one. A must read article!
    토토사이트웹

    ReplyDelete
  89. Your post is very interesting to me. Reading was so much fun. I think the reason reading is fun is because it is a post related to that I am interested in. Articles related to 블랙잭 you are the best. I would like you to write a similar post about !

    ReplyDelete
  90. I really enjoy your web’s topic. Very creative and friendly for users. Definitely bookmark this and follow it everyday.
    스포츠토토핫

    ReplyDelete
  91. Such an amazing and helpful post this is. I really really love it. It’s so good and so awesome. I am just amazed. I hope that you continue to do your work like this in the future also.
    메이저토토사이트

    ReplyDelete
  92. Great Post for Beginner to understand. I finally found great post here. Thanks for information.keep sharing more articles.
    토토사이트웹

    ReplyDelete
  93. I got this site from my friend who shared with me concerning this
    website and now this time I am visiting this website and reading very informative articles or reviews here.
    바카라사이트윈

    ReplyDelete
  94. It's fantastic that you are getting ideas from this article as well as from our argument made at this time.
    토토사이트링크

    ReplyDelete
  95. Fantastic work! This really can be the kind of data which needs to really be shared round the internet. Shame on Google for perhaps not placement this specific informative article much higher! 먹튀검증사이트

    ReplyDelete
  96. Hard to ignore such an amazing article like this. You really amazed me with your writing talent. Thank for you shared again.안전놀이터

    ReplyDelete
  97. I'm writing a paper on the subject of your article. I feel like I've found a very precious treasure. Thanks to you, I think I can finish my thesis safely. Please come to my blog and read my post related to yours. 먹튀검증업체

    ReplyDelete
  98. Please keep on posting such quality articles as this is a rare thing to find these days. I am always searching online for posts that can help me. watching forward to another great blog. Good luck to the author! all the best! 스포츠토토사이트

    ReplyDelete
  99. Javelin missiles are among the items promised to Ukraine by the US in a new $800m (£608m) military assistance package announced by President Joe Biden on Wednesday. -> 카지노 커뮤니티

    ReplyDelete

Blogroll

Popular Posts