Split a large Excel file into multiple, based on row count

Question

I have a C# console application which needs a large Excel to be split into multiple Excel files based on the row count. The code below shows a source file with only 51 rows (including the header column rows) but the final source file will have 100,000+ rows.

The code is trying to skip the very first (header) row and then should copy from rows 2 through 11 and so on--I have the target files set to only 10 rows per file, to make developing faster.

Question So how do I copy rows 2 through 11 and subsequent 10 rows from the source Excel file and paste to multiple target Excel files so that the target files each will have 10 rows?

Here is the almost newly written code. It is loosely based on copying of specific range of excel cells from one worksheet to another worksheet and https://social.msdn.microsoft.com/Forums/vstudio/en-US/afd01976-63d0-4f96-9ba4-e3e2b6cf8d55/excel-with-c-how-to-specify-a-range-?forum=vsto

Now I am able to write 5 Excel files. But the first file has 9 rows (starting from row 2) while 2nd file has only 3 rows, starting with row 10, the 3rd has 13 rows starting, again, with row 10; the last two files have incrementally more rows, both starting with row 10.

So something wrong with my For Loop? Or the way I am selecting the ranges?

     string startPath = System.IO.Path.GetDirectoryName(System.Diagnostics.Process.GetCurrentProcess().MainModule.FileName);
            string filePath_source = Path.Combine(startPath, @"Source_Files\Offers_Source_Temp.xlsx");
            string filePath_copiedinto = Path.Combine(startPath, @"Source_Files\ToBeCopiedInto.xlsx");
           app = new Excel.Application();
           app.DisplayAlerts = false;
           book = app.Workbooks.Open(filePath_source);
            sheet = (Excel.Worksheet)book.Worksheets.get_Item((1));              
            int iRowCount = sheet.UsedRange.Rows.Count;
            int maxrows = 10;//change this to something like 50,000 later.  01/16/18
            int maxloops = iRowCount / maxrows;
            int beginrow = 2; //skipping the header row.
            Excel.Application destxlApp;
            Excel.Workbook destworkBook;
            Excel.Worksheet destworkSheet;
            Excel.Range destrange;
            string srcPath;
            string destPath;
            //Opening of first worksheet and copying
            srcPath = filePath_source;
            for (int i = 1; i <= maxloops; i++)   {
                Excel.Range rng = (Excel.Range)sheet.Range[sheet.Cells[beginrow, 1], sheet.Cells[maxrows, 3]];
                rng.Copy(Type.Missing);
                //opening of the second worksheet and pasting
                destPath = filePath_copiedinto; 
                destxlApp = new Excel.Application();
                destxlApp.DisplayAlerts = false;
                destworkBook = destxlApp.Workbooks.Open(destPath, 0, false);
                destworkSheet = destworkBook.Worksheets.get_Item(1);
                destrange = destworkSheet.Cells[1, 1];
                destrange.Select();
                destworkSheet.Paste(Type.Missing, Type.Missing);                   
                destworkBook.SaveAs(startPath + "\\Output_Files\\" + beginrow + ".xlsx");                    
                destworkBook.Close(true, null, null);
                destxlApp.Quit();
                beginrow = beginrow + maxrows;
                string blah = null;

            }

I don't know how to copy from rows 2 through 11 and so on and then paste into new target files. — IrfanClemson
– IrfanClemson, Commented Jan 17, 2018 at 13:52
Updated the Question to try to clearly ask what's needed. Thanks. — IrfanClemson
– IrfanClemson, Commented Jan 17, 2018 at 13:55
I have the target files set to only 10 rows per file, to make developing faster What you trying to do with After splitting the large file into multiple. And I don't think, Read the large file and make it into 5 different files will make developing faster. — Kavin
– Kavin, Commented Jan 17, 2018 at 14:11
My source excel file is too large to be fed into another process and so I need to split it to some chunks. Currently, doing it manually but that's not long term solution. Thanks. — IrfanClemson
– IrfanClemson, Commented Jan 17, 2018 at 14:13

CodeTherapist · Accepted Answer · 2018-01-17 14:11:12Z

2

I would suggest to use OpenXml library to do that task. It is dependency free and supports the whole OpenXml structure. Here a starting point how to read/write the rows:

using System;
using System.Linq;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

// Open the document for editing.
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false))
{
    WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
    WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
    SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();

    foreach (Row r in sheetData.Elements<Row>())
    {

    }
}

Now, writing is very similar:

using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(fileName),
    SpreadsheetDocumentType.Workbook))
{
    // create the workbook
    spreadSheet.AddWorkbookPart();
    spreadSheet.WorkbookPart.Workbook = new Workbook ();     // create the worksheet
    spreadSheet.WorkbookPart.AddNewPart<WorksheetPart>();
    spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet = new Worksheet();

    // create sheet data
    spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.AppendChild(new SheetData());
    // create row
    spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.First().AppendChild(new Row());
}

edited Jan 17, 2018 at 14:11

answered Jan 17, 2018 at 14:07

CodeTherapist

2,80616 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

IrfanClemson Over a year ago

Thank you. I think I am close enough and this application will be for me alone on my desktop where Excel is already installed. So If I can figure out the copy/paste of rows, starting from the row numbers till ending row numbers then I should be good.

IrfanClemson Over a year ago

Please see a repost of the code; I think I am closer. Thanks.

marc_s · Accepted Answer · 2019-12-11 19:04:58Z

Got it! In my revised code in the Question, I came close but had some problem in the For Loop; fixed it per the code below. So here is the almost complete code. Thanks everyone for your help!!

  try
        {               
            string startPath = System.IO.Path.GetDirectoryName(System.Diagnostics.Process.GetCurrentProcess().MainModule.FileName);
            string filePath_source = Path.Combine(startPath, @"Source_Files\Offers_Source_Temp.xlsx");
            string filePath_copiedinto = Path.Combine(startPath, @"Source_Files\ToBeCopiedInto.xlsx");
           app = new Excel.Application();
           app.DisplayAlerts = false;
           book = app.Workbooks.Open(filePath_source);
            sheet = (Excel.Worksheet)book.Worksheets.get_Item((1));              
            int iRowCount = sheet.UsedRange.Rows.Count;              
            int countColumns = sheet.UsedRange.Columns.Count;
            int maxrows = 10;//change this to something like 50,000 later.  01/16/18
            int maxloops = iRowCount / maxrows;
            int beginrow = 2; //skipping the header row.
            Excel.Application destxlApp;
            Excel.Workbook destworkBook;
            Excel.Worksheet destworkSheet;
            Excel.Range destrange;
            string srcPath;
            string destPath;
            //Opening of first worksheet and copying
            srcPath = filePath_source;
            for (int i = 1; i <= maxloops; i++)   {
                ///  Excel.Range rng = (Excel.Range)sheet.Range[sheet.Cells[beginrow, 1], sheet.Cells[maxrows, 3]];
                Excel.Range startCell = sheet.Cells[beginrow, 1];//not sure the second parameter needed?
                Excel.Range endCell = sheet.Cells[beginrow+maxrows-1, 3];//not sure the second parameter needed?
                Excel.Range rng = sheet.Range[startCell, endCell];
                rng = rng.EntireRow;//so second parameters above should not be needed. But doesn't work without it!
                rng.Copy(Type.Missing);
                //opening of the second worksheet and pasting
                destPath = filePath_copiedinto; 
                destxlApp = new Excel.Application();
                destxlApp.DisplayAlerts = false;
                destworkBook = destxlApp.Workbooks.Open(destPath, 0, false);
                destworkSheet = destworkBook.Worksheets.get_Item(1);
                destrange = destworkSheet.Cells[1, 1];
                destrange.Select();
                destworkSheet.Paste(Type.Missing, Type.Missing);                   
                destworkBook.SaveAs(startPath + "\\Output_Files\\" + beginrow + ".xlsx");                    
                destworkBook.Close(true, null, null);
                destxlApp.Quit();
                beginrow = beginrow + maxrows;


            }//for loop
}

Collectives™ on Stack Overflow

Split a large Excel file into multiple, based on row count

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related