How to split a excel sheet based on batchsize using C#?

Question

The below code is working fine for splitting a file for .txt,.csv,.xml eg if a file named Data contains 40 records it is splitting into 20 records as Data1 and Data2 .How to keep the first line(header) in each of the splitted file?

int i = 1;
int batchsize = 20;
string filepath = "";
string file = "";
string[]readfile=File.ReadAllLines(file);
Var chunk = readFile.Take(batchsize);
Var rem = readFile.Skip(batchsize);
While(chunk.Take(1).Count() > 0) 
{
    stringfilename="Data_" +i.String()+ ".xlsx";
    using StreamWriter sw=new StreamWriter (filename))
   {
        foreach(string line in chunk) 
       {
            sw WriteLine(line);
        }
    }
    chunk = rem.Take(batchsize);
    rem = rem.Skip(batchsize)
    i++;
}

Welcome to StackOverflow. I think that the problem is that your `Excel` file is not a text file but a ***binary file*** (therefore `File.ReadAllLines(file)` cannot be used to read it). Please, take a look at this link: https://stackoverflow.com/q/6865890/12833205. I think that it can be helpful. It shows how to read an arbitrary file chunk by chunk. — Iliar Turdushev, Jun 08 '20 at 10:54

score 0 · Answer 1 · answered Jun 08 '20 at 12:07

I think getting data from excel directly will be complex, cuz ya have multiple sheet and social philosophy for columns and rows, so you can use "EPPlus" from Nuget with version "4.5.3.3" (cuz it is free on this version)

and all your practices will be so easy to do. I hope the answer been helpful

reference: Reading excel file using EPPlus

How to split a excel sheet based on batchsize using C#?

1 Answers1