Search

Blog

  Blog
Techemistry Blog

Combine Excel Documents Using C#

3/15/2013 12:47:00 PM -- Ted Krapf

<p>A client yesterday had a common problem, "Ted I have 100+ Excel documents that I need to be able to quickly search through, and I'd like to combine all those work books into a single .xlsx file so that I can just search in one document."<br /> <br /> </p> <p>Sounds simple enough right? &nbsp;The first thing we tried together was scouring the web for a VBA script to do this. &nbsp;Seems simple, get a list of the files in a directory, open each doc, and copy and paste its contents into the main document. &nbsp;Well after an hour of stumbling through VBA syntax, help files, blogs, etc, we just couldn't find one that would work. &nbsp;The best we found that worked would combine the all the worksheets of all the other documents into the main workbook as worksheets -- still leaving us having to manually copy and paste 100's of worksheets into one.<br /> <br /> </p> <p>We had also found an array of different commercial 'combine' tools that ranged from dollars to $1,000's. &nbsp;Needless to say, being a programmer, I wanted to see how I could do this myself and so I could share the solution with you!<br /> <br /> </p> <p>I had forgot that months ago I discovered the goodness that is <a href="https://code.google.com/p/linqtoexcel/" target="_blank">LinqToExcel</a>&nbsp;at <a href="http://www.codeplex.com" target="_blank">CodePlex</a>. &nbsp;I had found and used LinqToExcel for some basic Excel import functions I wanted to write. &nbsp;<br /> <br /> </p> <p>I had also tried numerous solutions for exporting to Excel from C#, and ended building up a simple OpenXML project/dll to handle my exports. &nbsp;Most of it was referenced from this great <a href="http://blogs.msdn.com/b/chrisquon/archive/2009/07/22/creating-an-excel-spreadsheet-from-scratch-using-openxml.aspx" target="_blank">OpenXML article on the MSDN Blogs</a>. (Thanks again Chris!)<br /> <br /> </p> <p>So back to the combine solution -- I created a simple Windows form app project in Visual studio that just had a textbox for copy/pasting in the folder of xlsx files that client wanted to combine, and a button to start the function.<br /> <br /> </p> <p>The trick to this combine method working is ensuring that the excel documents you are going to combine are:<br /> <br /> </p> <p>1.) In the same directory <br /> 2.) Have the exact same columns<br /> 3.) Have the exact same column headings (caps, spaces, etc count)<br /> 4.) The worksheet in each workbook is named the same<br /> <br /> </p> <p>First I create a class in my code that matches the columns of the excel document. &nbsp;<br /> <br /> </p> <p><span style="color: #00b050;"><strong>protected class ReconcillationReport</strong><br /> <strong>{<br /> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; public string ID { get; set; }<br /> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; public string Date_Submitted { get; set; }<br /> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; public string Commission { get; set; }<br /> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; public string First_Name { get; set; }<br /> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; public string Last_Name { get; set; }<br /> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; public string Home_Phone_Num { get; set; }<br /> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; public string Distributor_Name { get; set; }<br /> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; public string Account_Num { get; set; }<br /> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; public string Status { get; set; }<br /> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; public string Code { get; set; }<br /> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; public string START_DATE { get; set; }<br /> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; public string END_DATE { get; set; }<br /> }</strong></span></p> <p>Next I created the button click event, retrieve the source directory from the textbox (txtRecDir.Text), and copy the file names in that directory to a List&lt;&gt;. &nbsp;I then create a new ReconcillationReport List&lt;&gt;. &nbsp;Next I loop through all the files in the directory listing (skipping any files called "WOOT" in case a combine was already performed on these files. <br /> <br /> </p> <p>Then using LinqToExcel's ExcelQueryFactory, I import each workbook to be combine one at a time and serialize them into a ReconcillationReport object, which I then add to the List of objects.<br /> <br /> </p> <p>I then simply convert the List of reports into a DataSet table, and use my export library to export the final combined Excel document.<br /> <br /> </p> <p>Here is the button click event<br /> <br /> </p> <p><strong><span style="color: #00b050;">private void button1_Click(object sender, EventArgs e)<br /> &nbsp; &nbsp; &nbsp; &nbsp; {<br /> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; string path = txtRecDir.Text;<br /> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; List<string> files = Directory.GetFiles(path, "*.xlsx").ToList();<br /> <br /> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; List<reconcillationreport> rr = new List<reconcillationreport>();<br /> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; foreach (string f in files.Where(c =&gt; !c.Contains("WOOT")))<br /> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {<br /> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var excelDoc = new ExcelQueryFactory(f);<br /> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; List<reconcillationreport> r = (from c in excelDoc.Worksheet<reconcillationreport>("Reconciliation") select c).ToList();<br /> </reconcillationreport></reconcillationreport></reconcillationreport></reconcillationreport></string></span></strong><strong><span style="color: #00b050;">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; rr.AddRange(r);</span></strong><strong><span style="color: #00b050;"><br /> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }<br /> <br /> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DataSet ds = new DataSet();<br /> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ds.Tables.Add(Utils.ToDataTable(rr, "Combined"));<br /> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ExcelExport.CreateExcelFileOriginal.CreateExcelDocument(ds, path + "\\" + &nbsp;"WOOT.xlsx");<br /> <br /> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; btnRecCombind.Text = "DONE";<br /> &nbsp; &nbsp; &nbsp; &nbsp; }<br /> <br /> </span></strong></p> <p>Obviously there is no error checking, and note that this methodology requires advanced knowledge of the structure of the excel documents, but I still thought I'd share it in case it is useful to you. &nbsp;I'm sure as the client's request evolves I'll probably add the flexibility of combining dissimilar excel files to each other, but initially this simple code snippet saved the day. &nbsp;My client has cut 2 hours of excel wrangling out of every business day now -- what's that worth?&nbsp;<br /> <br /> </p> <p>Till next time,<br /> <br /> </p> <p>TK</p> <p><br /> </p>

[return to articles list]