I need assistance with a function that exports multiple CSV sheets in a directory. I'm sorry but my code is too long, I will try to add it to help me with it.
static private int rowsPerSheet = 100000;
static private DataTable ResultsData = new DataTable();
private static void ExportToCSV(bool firstTime)
{
const string fileName = @"C:\Export\MyExcel.csv";
//Delete the file if it exists.
if (firstTime && File.Exists(fileName))
{
File.Delete(fileName);
}
uint sheetId = 1;
if (firstTime)
{
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
Create(fileName, SpreadsheetDocumentType.Workbook);
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
var worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
var sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
var bold1 = new Bold();
CellFormat cf = new CellFormat();
Sheets sheets;
sheets = spreadsheetDocument.WorkbookPart.Workbook.
AppendChild<Sheets>(new Sheets());
var sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = sheetId,
Name = "Sheet" + sheetId
};
sheets.Append(sheet);
var headerRow = new Row();
foreach (DataColumn column in ResultsData.Columns)
{
var cell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(column.ColumnName)
};
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (DataRow row in ResultsData.Rows)
{
var newRow = new Row();
foreach (DataColumn col in ResultsData.Columns)
{
var cell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(row[col].ToString())
};
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
workbookpart.Workbook.Save();
spreadsheetDocument.Close();
}
else
{
var spreadsheetDocument = SpreadsheetDocument.Open(fileName, true);
var workbookpart = spreadsheetDocument.WorkbookPart;
if (workbookpart.Workbook == null)
workbookpart.Workbook = new Workbook();
var worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
var sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
var sheets = spreadsheetDocument.WorkbookPart.Workbook.Sheets;
if (sheets.Elements<Sheet>().Any())
{
//Set the new sheet id
sheetId = sheets.Elements<Sheet>().Max(s => s.SheetId.Value) + 1;
}
else
{
sheetId = 1;
}
var sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = sheetId,
Name = "Sheet" + sheetId
};
sheets.Append(sheet);
var headerRow = new Row();
foreach (DataColumn column in ResultsData.Columns)
{
var cell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(column.ColumnName)
};
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (DataRow row in ResultsData.Rows)
{
var newRow = new Row();
foreach (DataColumn col in ResultsData.Columns)
{
var cell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(row[col].ToString())
};
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
workbookpart.Workbook.Save();
spreadsheetDocument.Close();
}
}
private void Button5_Click(object sender, EventArgs e)
{
my1();
my2();
string queryString = "select * from " + comboBox1.Text + "";
using (var connection =
new SqlConnection(@"Data Source =***"))
{
var command = new SqlCommand(queryString, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
int c = 0;
bool firstTime = true;
DataTable dtSchema = reader.GetSchemaTable();
var listCols = new List<DataColumn>();
if (dtSchema != null)
{
foreach (DataRow drow in dtSchema.Rows)
{
string columnName = Convert.ToString(drow["ColumnName"]);
var column = new DataColumn(columnName, (Type)(drow["DataType"]));
column.Unique = (bool)drow["IsUnique"];
column.AllowDBNull = (bool)drow["AllowDBNull"];
column.AutoIncrement = (bool)drow["IsAutoIncrement"];
listCols.Add(column);
ResultsData.Columns.Add(column);
}
}
while (reader.Read())
{
DataRow dataRow = ResultsData.NewRow();
for (int i = 0; i < listCols.Count; i++)
{
dataRow[(listCols[i])] = reader[i];
}
ResultsData.Rows.Add(dataRow);
c++;
if (c == rowsPerSheet)
{
c = 0;
ExportToCSV(firstTime);
ResultsData.Clear();
firstTime = false;
}
}
if (ResultsData.Rows.Count > 0)
{
ExportToCSV(firstTime);
ResultsData.Clear();
}
reader.Close();
}
MessageBox.Show("Successfully Completed");
}
private void my1()
{
var path = "C:\\";
var folder = Path.Combine(path, "Export");
Directory.CreateDirectory(folder);
}
private void my2()
{
try
{
var app = new Microsoft.Office.Interop.Excel.Application();
var wb = app.Workbooks.Add();
wb.SaveAs(@"C:\Export\MyExcel.csv");
wb.Close();
}
catch (Exception )
{
Console.WriteLine("' You Canseld The Export'");
}
}
Really, it takes from me along time to write this code to export the excel sheet and spread it like I want of rows, so it work good with a small table but when I try to export a large table, it takes along of time and it didn't finish.
I have like 10.000.000 rows with 20 columns and more than that in another table, so I try to export it to CSV every sheet have 100.000 row only.
Any help or any other ideas are welcomed.
