Save all worksheets of a workbook as a CSV

Aspose.Cells Product Family
1

Using Aspose.Cells (currently using 4.4.1.13) in .Net, is it possible to save all the worksheets of a workbook into a CSV (or multiple CSVs)?

2

BTW, I am using VB.Net.

3

Hi,

Well, I think you may save all your worksheets to make multiple csv files (one worksheet per .csv file).

May the following sample code help your for your requirement. I used a template .xls file (you may create the file from the scratch too.), Open the template file and copy each worksheet to a new workbook and save the workbook as .csv file for your requirement.

[VB]

Dim workbook As = New Workbook()
workbook.Open("d:\test\abc.xls")
Dim i As Integer

For i = 0 To workbook.Worksheets.Count -1

Dim worksheet As Worksheet = workbook.Worksheets(i)

Dim wkb As Workbook = New Workbook()
wkb.Worksheets.Clear()
Worksheet ws = wkb.Worksheets(wkb.Worksheets.Add())
ws.Copy(worksheet)
ws.Name = worksheet.Name
wkb.Save("d:\test\test_" & i.ToString() & ".csv", FileFormatType.CSV)

Next

[C#]

Workbook workbook = new Workbook();
workbook.Open("d:\\test\\abc.xls");

for(int i = 0;i<workbook.Worksheets.Count;i++)
{
Worksheet worksheet = workbook.Worksheets[i];
Workbook wkb = new Workbook();
wkb.Worksheets.Clear();
Worksheet ws = wkb.Worksheets[wkb.Worksheets.Add()];
ws.Copy(worksheet);
ws.Name = worksheet.Name;
wkb.Save("d:\\test\\test_" + i.ToString() + ".csv",FileFormatType.CSV);
}
}

Thank you.

4

Thanks. It is working correctly.

For the record, in the VB example, this line:

Worksheet ws = wkb.Worksheets(wkb.Worksheets.Add())

Should read:

dim ws as Worksheet = wkb.Worksheets(wkb.Worksheets.Add())

5

Hi,

Good to know that it's working for your requirement.

And yes, it's typo error and thanks for correction :). Have a great day!

Thank you.

6

Thanks for the code. This works in my case too, but it is super slow.

This is what I do:

static private string CountInExcel(MemoryStream stream)

{

Workbook workbook = new Workbook();

workbook.Open(stream);

MemoryStream mem = new MemoryStream();

for(int i = 0;i<workbook.Worksheets.Count;i++)

{

Worksheet worksheet = workbook.Worksheets[i];

Workbook wkb = new Workbook();

wkb.Worksheets.Clear();

Worksheet ws = wkb.Worksheets[wkb.Worksheets.Add()];

ws.Copy(worksheet);

wkb.Save(mem, FileFormatType.TabDelimited);

}

StreamReader reader = new StreamReader(mem);

mem.Seek(0, SeekOrigin.Begin);

string text = reader.ReadToEnd();

//Call GetWordCount method to get word count of the input PDF file

return text;

}

I get the Excel in the stream object and the slow party is def. in the loop and not the StreamReader.

The document has about 10 sheet and is 2MB. For some reason the "Keep this post private" button is grayed out, so I cannot attach the document.

7

Hi,

If you are sure that you want to save the CSV file, please use Workbook.LoadData() to open the file. It will save memory , load time, copy time. See following codes:

Workbook workbook = new Workbook();
workbook.LoadData(@"F:\FileTemp\aaa.xls");
MemoryStream ms = new MemoryStream();
for (int i = 0; i < workbook.Worksheets.Count; i++)
{
Workbook wbk = new Workbook();
wbk.Worksheets[0].Copy(workbook.Worksheets[i]);
wbk.Save(ms, FileFormatType.CSV);
}
ms.Seek(0, SeekOrigin.Begin);
FileStream fs = File.Create(@"F:\FileTemp\aaa.txt");
fs.Write(ms.ToArray(), 0,(int) ms.Length);

Console.WriteLine(DateTime.Now);

And please mail your file to nanjing@aspose.com. We will check what cause slow run speed.

8

Hi,

Please try this version. We do get the exception when we run the codes with your file.

In your template file, we find 8 hidden worksheets. If you do not want to export the hidden worksheet, please changes your codes as the following :

Workbook workbook = new Workbook();
workbook.LoadData(@"F:\FileTemp\Bad_count_cause_of_multiple_sheets.xls");
MemoryStream ms = new MemoryStream();

// return;
for (int i = 0; i < workbook.Worksheets.Count; i++)
{

if (workbook.Worksheets[i].IsVisible)
{
Workbook wbk = new Workbook();
wbk.Worksheets[0].Copy(workbook.Worksheets[i]);
wbk.Save(ms, FileFormatType.CSV);
}
else
{
Console.WriteLine(workbook.Worksheets[i].Name);
}

}
ms.Seek(0, SeekOrigin.Begin);
FileStream fs = File.Create(@"F:\FileTemp\aaa.txt");
fs.Write(ms.ToArray(), 0, (int)ms.Length);

9

Thank you. Works much better now!

10

Is there a way to only export columns that are not hidden?

Delete them beforehand or something?
11

Hi,


Well, you may simply find out which columns are hidden so you would delete those columns before rendering, see the sample code segment below for your reference:
e.g
Sample code:

//…
Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];
for (int i = 0; i < worksheet.Cells.MaxDataColumn; i++)
{
if (worksheet.Cells.Columns[i].IsHidden)
{
worksheet.Cells.DeleteColumn(i);

}

}

//…

Hope, this helps you a bit.

Thank you.

12

It’s like Christmas!

Thanks a lot.
13

Hi,


You are welcome. Good to know that it figures out your issue now. Feel free to contact us any time if you need further help or have comments or quires, we will be happy to assist you soon.

Thank you.