使用OLEDB导出excel [英] Export excel with OLEDB
本文介绍了使用OLEDB导出excel的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在编写一个检查excel文件数据的项目
一切工作文件直到保存过程
i需要使用像cell.backcolor对齐等单元格样式导出datagridview。
i使用互操作来执行此过程，需要5分钟才能保存单张15 * 15排
任何人都可以建议任何图书馆来完成这个过程吗？
i我试图找出oledb是否可以做这种工作？
i只是发现oledb可以导出但我找不到任何关于设置excell单元格格式的文档
任何人都可以帮帮我。
这是我使用Interop保存代码。
private void button10_Click（ object sender，EventArgs e）
{
if （dataGridView2.Rows.Count == 0 ）
{
MessageBox.Show（ 抱歉！，没有保存， 警告!!!!< /跨度>）;
return ;
}
Cursor.Current = Cursors.WaitCursor;
Excel.Application xlApp;
Excel.Workbook xlWorkBook = null ;
Excel.Worksheet xlWorkSheet = null ;
object misValue = System.Reflection.Missing.Value;
System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo（ en-US）;
pathZ = openFileDialog6.FileName;
xlApp = new Excel.Application（）;
xlWorkBook = xlApp.Workbooks.Open（pathZ，Type.Missing，Type.Missing，Type.Missing，Type.Missing，Type.Missing，Type.Missing，Type.Missing，Type.Missing，Type.Missing， Type.Missing，Type.Missing，Type.Missing，Type.Missing，Type.Missing）;
xlWorkSheet =（Excel.Worksheet）xlWorkBook.Worksheets [ 车両诸元];
int i = 0 ;
int j = 0 ;
xlApp.Calculation = XlCalculation.xlCalculationManual;
for （i = 0 ; i < dataGridView2.RowCount - 1 ; i ++）
{
for （j = 0 ; j < dataGridView2.ColumnCount; j ++）
{
DataGridViewCell cell = dataGridView2 [j，i];
xlWorkSheet.Cells [i + 2 ，j + 2 ] = cell.Value;
范围rng =（Excel.Range）xlWorkSheet.Cells [i + 2 ，j + 2 ];
rng.Interior.Color = ColorTranslator.ToOle（（dataGridView2.Rows [i] .Cells [j] .Style.BackColor））;
rng.NumberFormat = @;
rng.WrapText = true ;
rng.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
rng.Font.Bold = true ;
BorderAround（rng，System.Drawing.ColorTranslator.ToOle（System.Drawing.Color.FromArgb（ 79 ， 129 ， 189 ）））;
if （dataGridView2.Rows [i] .Cells [j] .Style.BackColor == Color.Salmon）
{
}
else
{
rng.Interior.Color = System.Drawing.ColorTranslator.ToOle（Color.White ）;
}
}
}
xlApp.DisplayAlerts = false ;
xlWorkBook.Close（ true ，misValue，misValue）;
xlApp.Quit（）;
openFileDialog6.Dispose（）;
releaseObject（xlWorkSheet）;
releaseObject（xlWorkBook）;
releaseObject（xlApp）;
Cursor.Current = Cursors.Default;
MessageBox.Show（ Save Complete）;
解决方案
有几个免费和开源组件可以让你在不需要安装Excel的情况下执行此操作。例如：
- http://epplus.codeplex.com/ [< a href =http://epplus.codeplex.com/target =_ blanktitle =New Window> ^ ]
- http://closedxml.codeplex.com/ [ ^ ]
- https://github.com/tonyqus/npoi [ ^ ]
- OpenXML SDK [ ^ ];
我使用过Aspose取得了很大的成功。使用非常简单高效。
Aspose .Cells for .NET [ ^
I am writing a project that check data base on an excel file
everything work file untill the save process
i need to export datagridview with cell style like cell.backcolor alignment etc.
i use interop to do this process and it take like 5 min to save a single sheet with just 15 * 15 row
Can anyone sugguest any library to do this process?
i am trying to find if oledb can do this kind of work?
i just find out that oledb can export to but i did't find any doc that tell about setting the format of excell 's cell
can any one Help me please.
Here is my Save CODE using Interop.
private void button10_Click(object sender, EventArgs e)
{
if (dataGridView2.Rows.Count == 0)
{
MessageBox.Show("Sorry!,There is NOTHING to save", "Warning!!!!");
return;
}
Cursor.Current = Cursors.WaitCursor;
Excel.Application xlApp;
Excel.Workbook xlWorkBook = null;
Excel.Worksheet xlWorkSheet = null;
object misValue = System.Reflection.Missing.Value;
System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
pathZ = openFileDialog6.FileName;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Open(pathZ, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets["車両諸元"];
int i = 0;
int j = 0;
xlApp.Calculation = XlCalculation.xlCalculationManual;
for (i = 0; i < dataGridView2.RowCount - 1; i++)
{
for (j = 0; j < dataGridView2.ColumnCount; j++)
{
DataGridViewCell cell = dataGridView2[j, i];
xlWorkSheet.Cells[i + 2, j + 2] = cell.Value;
Range rng = (Excel.Range)xlWorkSheet.Cells[i + 2, j + 2];
rng.Interior.Color = ColorTranslator.ToOle((dataGridView2.Rows[i].Cells[j].Style.BackColor));
rng.NumberFormat = "@";
rng.WrapText = true;
rng.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
rng.Font.Bold = true;
BorderAround(rng, System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(79, 129, 189)));
if (dataGridView2.Rows[i].Cells[j].Style.BackColor == Color.Salmon)
{
}
else
{
rng.Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.White);
}
}
}
xlApp.DisplayAlerts = false;
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
openFileDialog6.Dispose();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
Cursor.Current = Cursors.Default;
MessageBox.Show("Save Complete"); 解决方案
There are several free and open-source components which will let you do this without needing to have Excel installed. For example:
I've used Aspose with a great deal of success. Very simple and efficient to work with.
Aspose.Cells for .NET[^]
这篇关于使用OLEDB导出excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
