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");