从c#中不同的数据表导出数百万个数据到单个excel文件的最快方法是什么?
本文关键字：文件 excel 单个 数据 是什么 方法 百万个 数据表 数百万 | 更新日期: 2023-09-27 18:07:53
我试图从不同的数据表导出数百万个数据到单个excel文件与多个工作表。我能够为数千条记录实现这一点，但我的方法对于数百万条数据导出似乎不太有效。
是否有任何替代方法来实现相同的数百万条记录导出到单个excel文件?(我检查了这么多论坛，但似乎都遵循我在下面的代码所做的相同方法)
下面是我的代码，用于将数据从不同的数据表导出到单个excel文件中的多个工作表。
我知道代码有点大，但是相信我，它列出了很多我在这里没有提到的部分。我的目的是展示我用来将数据推入excel文件的技术。
如果有人有更好的主意，请告诉我。提前谢谢。
private void technicalvalidation_DoWork(object sender, DoWorkEventArgs e)
{
if (MessageBox.Show("This process will take more than 10 minutes. You cannot use the tool utill processing finishes.'n'nDo you want to continue?", "Confirmation", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
int countdataset;
int loopcount = 1;
String Date = DateTime.Now.ToString("MM.dd.yyy_HH.mm.ss");
DataSet ds = new DataSet();
backgroundWorker.ReportProgress(1);
//StoredProcedure Execution for the validation engine
string connectionString = ConfigurationManager.ConnectionStrings["VEG_proj_Tool.Properties.Settings.VEG_ToolprojConnectionString"].ConnectionString;
string commandText = "proj.ValidationEngine-TechnicalValidation";
using (SqlConnection conn = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(commandText, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 600;
try
{
conn.Open();
SDA.SelectCommand = cmd;
SDA.Fill(ds);
ds.Tables[0].TableName = "DataseriesNotinDatavalues";
//ds.Tables[1].TableName = "DVDataseriesNotinDataseries";
ds.Tables[1].TableName = "DS-IDvalidation";
ds.Tables[2].TableName = "DV-IDvalidation";
ds.Tables[3].TableName = "CountryCount";
ds.Tables[4].TableName = "CountryList";
ds.Tables[5].TableName = "StateCount";
ds.Tables[6].TableName = "StateList";
ds.Tables[7].TableName = "GeoDuplicates";
countdataset = ds.Tables.Count;
}
catch (SqlException ex)
{
backgroundWorker.ReportProgress(0);
MessageBox.Show(ex.Message + "Data processing unsuccessful, please contact technical team.");
saveAction(ex.Message);
}
finally
{
conn.Close();
}
if (!Directory.Exists("C:''proj Tool''Validation Results proj")) // name of the error file and location
{
Directory.CreateDirectory("C:''proj Tool''Validation Results proj");
}
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
backgroundWorker.ReportProgress(0);
MessageBox.Show("Excel is not properly installed!!");
return;
}
try
{
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkBook.SaveAs("C:''proj Tool''Validation Results proj''TechnicalValidation" + "_" + Date + ".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
string filePath = @"C:''proj Tool''Validation Results proj''TechnicalValidation" + "_" + Date + ".xls";
xlWorkBook = xlApp.Workbooks.Open(filePath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
//It supperesses the Microsoft Compatibility message
xlWorkBook.CheckCompatibility = false;
//For sheet1------------------------------>
Excel.Worksheet excelWorkSheet = xlWorkBook.Sheets.Add();
excelWorkSheet.Name = "DataSeries Validation";
foreach (DataTable table in ds.Tables)
{
if (loopcount > 3)
{
break;
}
if (table.TableName == "DataseriesNotinDatavalues")
{
for (int i = 1; i < table.Columns.Count + 1; i++)
{
excelWorkSheet.Cells[3, i] = table.Columns[i - 1].ColumnName;
Excel.Range formatRange;
formatRange = excelWorkSheet.get_Range("A3");
formatRange.EntireRow.Font.Bold = true;
}
}
if (table.TableName == "DS-IDvalidation")
{
int li = 6;
for (int i = 1; i < table.Columns.Count + 1; i++)
{
excelWorkSheet.Cells[3, li] = table.Columns[i - 1].ColumnName;
Excel.Range formatRange;
formatRange = excelWorkSheet.get_Range("A3");
formatRange.EntireRow.Font.Bold = true;
li = li + 1;
}
}
if (table.TableName == "DV-IDvalidation")
{
int ki = 11;
for (int i = 1; i < table.Columns.Count + 1; i++)
{
excelWorkSheet.Cells[3, ki] = table.Columns[i - 1].ColumnName;
Excel.Range formatRange;
formatRange = excelWorkSheet.get_Range("A3");
formatRange.EntireRow.Font.Bold = true;
ki = ki + 1;
}
}
if (table.Rows.Count == 0)
{
if (table.TableName == "DataseriesNotinDatavalues")
{
excelWorkSheet.Cells[5, 2].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
excelWorkSheet.get_Range("B5", "D5").Merge();
excelWorkSheet.Cells[5, 2] = excelWorkSheet.get_Range("B5", "D5");
excelWorkSheet.Cells[5, 2].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excelWorkSheet.Cells[5, 2] = "No mismatch found in Datavalues";
}
if (table.TableName == "DS-IDvalidation")
{
excelWorkSheet.Cells[5, 7].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
excelWorkSheet.get_Range("G5", "I5").Merge();
excelWorkSheet.Cells[5, 7] = excelWorkSheet.get_Range("G5", "I5");
excelWorkSheet.Cells[5, 7].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excelWorkSheet.Cells[5, 7] = "No IDmismatch found in Dataseries";
}
if (table.TableName == "DV-IDvalidation")
{
excelWorkSheet.Cells[5, 12].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
excelWorkSheet.get_Range("L5", "N5").Merge();
excelWorkSheet.Cells[5, 12] = excelWorkSheet.get_Range("L5", "N5");
excelWorkSheet.Cells[5, 12].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excelWorkSheet.Cells[5, 12] = "No IDmismatch found in Datavalues";
}
}
if (table.TableName == "DataseriesNotinDatavalues")
{
int l = 4;
excelWorkSheet.get_Range("A2", "C2").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
excelWorkSheet.Cells[2, 1] = "Data Series not in Data Values";
for (int j = 0; j < table.Rows.Count; j++)
{
for (int k = 0; k < table.Columns.Count; k++)
{
excelWorkSheet.Cells[l, k + 1] = table.Rows[j].ItemArray[k].ToString();
excelWorkSheet.Cells[l, k + 1].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
}
l = l + 1;
}
}
if (table.TableName == "DS-IDvalidation")
{
int l = 4;
int la = 6;
excelWorkSheet.get_Range("F2", "H2").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
excelWorkSheet.Cells[2, 6] = "Validate each ID in data series to match its definition in the LU tables";
for (int j = 0; j < table.Rows.Count; j++)
{
for (int k = 0; k < table.Columns.Count; k++)
{
excelWorkSheet.Cells[l, la] = table.Rows[j].ItemArray[k].ToString();
excelWorkSheet.Cells[l, la].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.RosyBrown);
la = la + 1;
}
l = l + 1;
la = 6;
}
}
if (table.TableName == "DV-IDvalidation")
{
int l = 4;
int lb = 11;
excelWorkSheet.get_Range("K2", "M2").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
excelWorkSheet.Cells[2, 11] = "Validate each ID in data values to match its definition in the LU tables";
for (int j = 0; j < table.Rows.Count; j++)
{
for (int k = 0; k < table.Columns.Count; k++)
{
excelWorkSheet.Cells[l, lb] = table.Rows[j].ItemArray[k].ToString();
excelWorkSheet.Cells[l, lb].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightCyan);
lb = lb + 1;
}
l = l + 1;
lb = 11;
}
}
loopcount = loopcount + 1;
}
//For sheet2------------------------------>
Excel.Worksheet excelWorkSheet1 = xlWorkBook.Sheets.Add();
excelWorkSheet1.Name = "Geo Validation";
loopcount = 1;
foreach (DataTable table in ds.Tables)
{
if (loopcount > 3)
{
if (loopcount > 8)
{
break;
}
if (table.TableName == "CountryCount")
{
for (int i = 1; i < table.Columns.Count + 1; i++)
{
excelWorkSheet1.Cells[3, i] = table.Columns[i - 1].ColumnName;
Excel.Range formatRange;
formatRange = excelWorkSheet1.get_Range("A3");
formatRange.EntireRow.Font.Bold = true;
}
}
if (table.TableName == "CountryList")
{
for (int i = 1; i < table.Columns.Count + 1; i++)
{
excelWorkSheet1.Cells[4, i] = table.Columns[i - 1].ColumnName;
Excel.Range formatRange;
formatRange = excelWorkSheet1.get_Range("A4");
formatRange.EntireRow.Font.Bold = true;
}
}
if (table.TableName == "StateCount")
{
for (int i = 1; i < table.Columns.Count + 1; i++)
{
excelWorkSheet1.Cells[3, 3] = table.Columns[i - 1].ColumnName;
Excel.Range formatRange;
formatRange = excelWorkSheet1.get_Range("A3");
formatRange.EntireRow.Font.Bold = true;
}
}
if (table.TableName == "StateList")
{
int ji = 3;
for (int i = 1; i < table.Columns.Count + 1; i++)
{
excelWorkSheet1.Cells[4, ji] = table.Columns[i - 1].ColumnName;
Excel.Range formatRange;
formatRange = excelWorkSheet1.get_Range("A4");
formatRange.EntireRow.Font.Bold = true;
ji = ji + 1;
}
}
if (table.TableName == "GeoDuplicates")
{
int jh = 5;
for (int i = 1; i < table.Columns.Count + 1; i++)
{
excelWorkSheet1.Cells[4, jh] = table.Columns[i - 1].ColumnName;
Excel.Range formatRange;
formatRange = excelWorkSheet1.get_Range("A4");
formatRange.EntireRow.Font.Bold = true;
jh = jh + 1;
}
}
if (table.Rows.Count == 0)
{
if (table.TableName == "CountryList")
{
excelWorkSheet1.Cells[6, 1].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
excelWorkSheet1.Cells[6, 1] = "No Countries are availabel";
}
if (table.TableName == "StateList")
{
excelWorkSheet1.Cells[6, 3].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
excelWorkSheet1.Cells[6, 3] = "No states are availabel";
}
if (table.TableName == "GeoDuplicates")
{
excelWorkSheet1.Cells[6, 5].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
excelWorkSheet1.Cells[6, 5] = "No geoduplicates are availabel";
}
}
if (table.TableName == "CountryList")
{
int l = 5;
excelWorkSheet1.get_Range("A2", "C2").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
excelWorkSheet1.Cells[2, 1] = "Geograpy Country Validation";
for (int j = 0; j < table.Rows.Count; j++)
{
for (int k = 0; k < table.Columns.Count; k++)
{
excelWorkSheet1.Cells[l, k + 1] = table.Rows[j].ItemArray[k].ToString();
excelWorkSheet1.Cells[l, k + 1].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
}
l = l + 1;
}
}
if (table.TableName == "CountryCount")
{
int l = 3;
for (int j = 0; j < table.Rows.Count; j++)
{
for (int k = 0; k < table.Columns.Count; k++)
{
excelWorkSheet1.Cells[l, 2] = table.Rows[j].ItemArray[k].ToString();
}
l = l + 1;
}
}
if (table.TableName == "StateCount")
{
int l = 3;
for (int j = 0; j < table.Rows.Count; j++)
{
for (int k = 0; k < table.Columns.Count; k++)
{
excelWorkSheet1.Cells[l, 4] = table.Rows[j].ItemArray[k].ToString();
}
l = l + 1;
}
}
if (table.TableName == "StateList")
{
int lj = 5;
excelWorkSheet1.get_Range("C2", "E2").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
excelWorkSheet1.Cells[2, 3] = "Geography State Validation";
for (int j = 0; j < table.Rows.Count; j++)
{
for (int k = 0; k < table.Columns.Count; k++)
{
excelWorkSheet1.Cells[lj, 3] = table.Rows[j].ItemArray[k].ToString();
excelWorkSheet1.Cells[lj, 3].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.RosyBrown);
}
lj = lj + 1;
}
}
if (table.TableName == "GeoDuplicates")
{
int lk = 5;
excelWorkSheet1.get_Range("E2", "H2").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
excelWorkSheet1.Cells[2, 5] = "Geograpy Dimension Duplicates";
for (int j = 0; j < table.Rows.Count; j++)
{
for (int k = 0; k < table.Columns.Count; k++)
{
excelWorkSheet1.Cells[lk, 5] = table.Rows[j].ItemArray[k].ToString();
excelWorkSheet1.Cells[lk, 5].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Linen);
}
lk = lk + 1;
}
}
}
loopcount = loopcount + 1;
}
xlWorkBook.Sheets["Sheet1"].Delete();
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
catch (Exception ex)
{
backgroundWorker.ReportProgress(0);
MessageBox.Show(" There was a problem in creating excel file. Please contact the techincal team " + ex.Message);
return;
}
}
backgroundWorker.ReportProgress(2);
}
}
最快的方式导出到excel我发现是把你的数据到一个数组，然后基本上"粘贴"到excel。下面是一个例子:
public void ExportToExcel(DataTable dt)
{
Excel.Application xlApp = default(Excel.Application);
Excel.Workbook xlWorkbook = default(Excel.Workbook);
Excel.Worksheet xlWorkSheet = default(Excel.Worksheet);
object misValue = Reflection.Missing.Value;
int i = 0;
int j = 0;
int maxRow = dt.Rows.Count;
int maxColumn = dt.Columns.Count;
string[,] arr = new string[maxRow + 1, maxColumn + 1];
string callingAssembly = Reflection.Assembly.GetCallingAssembly().GetName.Name;
if (dt == null)
throw new Exception("Passed Data Table is set to nothing");
while (!(j > maxColumn - 1)) {
arr(0, j) = dt.Columns(j).ColumnName;
j += 1;
}
while (!(i > maxRow - 1)) {
j = 0;
while (!(j > maxColumn - 1)) {
arr(i + 1, j) = dt.Rows(i).Item(j).ToString;
j += 1;
}
i += 1;
}
xlApp = new Excel.Application[];
xlWorkbook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkbook.Sheets("sheet1");
xlApp.Visible = false;
xlApp.ScreenUpdating = false;
xlApp.DisplayAlerts = false;
xlWorkSheet.Range("A1").Resize(maxRow + 1, maxColumn).Value = arr;
xlWorkSheet.Cells.Columns.AutoFit();
xlWorkSheet.Name = callingAssembly;
xlApp.ScreenUpdating = true;
xlWorkbook.SaveCopyAs("somefile.xlsx");
xlApp.Quit();
ReleaseObject(xlWorkSheet);
ReleaseObject(xlWorkbook);
ReleaseObject(xlApp);
}
private void ReleaseObject(object obj)
{
Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
这一行是"粘贴":
xlWorkSheet.Range("A1").Resize(maxRow + 1, maxColumn).Value = arr;