Problem with Excel object

Question

0.00/5 (No votes)

See more:

Hi All,

I am using Excel object to convert csv file to excel with formatting
when Excel object is open if i open any excel file otside of application
it goes throws error.

perhaps outside excel i m trying to open , opens in same workbook what i
created.

what is the best way to open Excel and work book to open exclusively.

function is attached here

help is appreciable

'Thank You'

C#

public string Convert(DataTable oDataTable, string directoryPath, string fileName)
      {
          object missing = Type.Missing;
          string fullpath = "";

          if (directoryPath.Substring(directoryPath.Length - 1, 1) == @"\" || directoryPath.Substring(directoryPath.Length - 1, 1) == "/")
          {
              fullpath = directoryPath + fileName;
          }
          else
          {
              fullpath = directoryPath + @"\" + fileName;
          }

          if (File.Exists(fullpath))
          {
              File.Delete(fullpath);
          }

          Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
         // Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Add(Type.Missing);
          Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Add(missing);

          DataTable table = oDataTable;
          int ColumnIndex = 0;
          foreach (DataColumn col in table.Columns)
          {
              ColumnIndex++;
              excel.Cells[1, ColumnIndex] = col.ColumnName;
          }
          int rowIndex = 0;

          excel.Cells.NumberFormat = "@";
          string colhecks = "";




          foreach (DataRow row in table.Rows)
          {
              rowIndex++;
              ColumnIndex = 0;

              foreach (DataColumn col in table.Columns)
              {
                  ColumnIndex++;
                  excel.Cells[rowIndex + 1, ColumnIndex] = row.ItemArray[ColumnIndex - 1];

              }

              //// c11 // code added to confirm numeric columns decimal precision 4 times
              try
              {
                  ColumnIndex = 0;
                  foreach (DataColumn col in table.Columns)
                  {
                      ColumnIndex++;
                         string colcode = GetChar(ColumnIndex);
                      if (IsNumeric(row.ItemArray[ColumnIndex - 1].ToString()))
                      {

                          if (row.ItemArray[ColumnIndex - 1].ToString().IndexOf(".") > 0)
                          {
                              if (colhecks.IndexOf(colcode) < 0)
                              {


                                  excel.Cells.get_Range(colcode.ToString() + "1").EntireColumn.NumberFormat = "#.0000";
                                  colhecks += colcode;


                              }
                          }
                          else
                          {
                              if (colhecks.IndexOf(colcode) < 0)
                              {
                                  if (row.ItemArray[ColumnIndex - 1].ToString().Length > 13)
                                  {
                                      excel.Cells.get_Range(colcode.ToString() + "1").EntireColumn.NumberFormat = "@";
                                  }
                                  else
                                  {
                                      excel.Cells.get_Range(colcode.ToString() + "1").EntireColumn.NumberFormat = "0";
                                  }
                              }
                          }

                      }
                      excel.Cells[rowIndex + 1, ColumnIndex] = row.ItemArray[ColumnIndex - 1];

                      /// code to increase width of columns
                      excel.Cells.get_Range(colcode.ToString() + "1").EntireColumn.AutoFit();

                      /// code to increase width of columns specific
                      if (excel.Cells.get_Range(colcode.ToString() + "1").Text.ToString().ToUpper().Contains("RATE") || excel.Cells.get_Range(colcode.ToString() + "2").Text.ToString().ToUpper().Contains("RATE"))
                      {
                          try
                          {
                              int width = System.Convert.ToInt32(excel.Cells.get_Range(colcode.ToString() + "1").EntireColumn.ColumnWidth) + 2;
                              if (width < 25)
                                  excel.Cells.get_Range(colcode.ToString() + "1").EntireColumn.ColumnWidth = width.ToString();
                          }
                          catch
                          {

                          }
                      }
                      /// end code to increase width of columns specific
                      /// end code to increase width of columns

                  }
              }
              catch (Exception ex)
              {

              }
              //// end c11 //

          }


          Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveSheet;
          //worksheet.Activate();
          //worksheet.Columns.AutoFit();
          workbook.Windows[1].Zoom = 80;
          char[] chrSplit = { '.' };
          string[] strFile = fileName.Split(chrSplit);
          worksheet.Name = strFile[0].ToString();
          workbook.SaveAs(fullpath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
          workbook.Close(Type.Missing, Type.Missing, Type.Missing);
          workbook = null;
          excel.Quit();

          // Clean up
          // NOTE: When in release mode, this does the trick
          GC.WaitForPendingFinalizers();
          GC.Collect();

          return fullpath;
      }

Posted 13-Mar-13 0:30am

Deepak Namdeo

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Accepted Answer · 2013-03-13T03:39:00

Solution 1

When you first open your excel application use these settings

C#

excel.IgnoreRemoteRequests = true;
excel.Visible = false;

As an aside instead of using icrosoft.Office.Interop.Excel throughout your code e.g.

Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();

try putting

using xl = Microsoft.Office.Interop.Excel;

and then you can use

xl.Application excel = new xl.Application();

which makes things a little clearer

Posted 13-Mar-13 3:39am

CHill60

Comments

Deepak Namdeo 14-Mar-13 1:14am

Thanks CHill60 for providing valuable information

can you please help me to find the way to perform
the function faster coz when over then 5 files are in
conversion process it takes a lot time
can not use throug XML in excel object
coz formatting of workbook is not available

CHill60 14-Mar-13 8:07am

You could try having the instance of Excel at the class level and only instantiating it once, clearing down the data each time and only having to do the garbage collection once when you're finished to get rid of any orphaned excel objects.
Instead of doing the cell by cell insertion of data you could just open the .csv file in excel (i.e. instead of xl.Workbook workbook = excel.Application.Workbooks.Add(missing); try xl.Workbook workbook = excel.Application.Workbooks.Open(csvFileName, missing, ... . Then you only have to go through the columns of the dataTable to determine the formatting

Deepak Namdeo 15-Mar-13 8:00am

Thanks a lot
Information is very useful i have applied in my project