Click here to Skip to main content
15,920,596 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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.
C#
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");
Posted
Updated 15-May-15 3:30am
v2
Comments
jk0391 15-May-15 9:27am    
Shouldn't you be calling xlWorkbook.SaveAs?
Real_Criffer 17-May-15 22:27pm    
i have tried it but it still slow as it is
the interop was slow on lt's own thank you Sir!

There are several free and open-source components which will let you do this without needing to have Excel installed. For example:
 
Share this answer
 
Comments
Real_Criffer 17-May-15 22:29pm    
To Rechard
Thank you for you advise for the open Source Component i did try to use eppplus
and it work like an angel pull be out from hell. the component was great and easy to work with but i can only make an 2010 excel which i understand the open Source always have a limit use and it ok with me thank you very much Sir
I've used Aspose with a great deal of success. Very simple and efficient to work with.

Aspose.Cells for .NET[^]
 
Share this answer
 
Comments
Richard Deeming 15-May-15 10:24am    
Posting links to commercial components is considered spam.
virusstorm 15-May-15 10:31am    
Where does it say that?
Richard Deeming 15-May-15 10:35am    
Why would it need to say it anywhere? You're posting a link to a product which has to be purchased, giving the makers of that product free advertising.

That's spam by anyone's definition.
virusstorm 15-May-15 10:41am    
Spam is unwanted communication. This individual asked for a suggestion so in response I said what I have used with success and provide him a link to look at it under his own accord.

If you go by that extreme, we can't talk about any of Microsoft's products or post any links to them because they are commercial.
Richard Deeming 15-May-15 10:46am    
At no point did the OP ask for suggestions of third-party commercial tools to solve their problem. Responding to the question with an unsolicited link to a third-party commercial tool is spam.

Your comment about Microsoft products is irrelevant - if someone asks a question about a Microsoft product, they usually already own it, so discussing how to use it is not spam.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900