Change color in excel file

Question

I will change the color of some Excel Cells and I use the following function for it:

  private void FormatFile(Excel.Borders _borders)
        {
            _borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
            _borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
            _borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
            _borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
            _borders.Color = ConsoleColor.Black;

            _range = _xlWorkSheet.get_Range("A1:AG100");
            _range.EntireColumn.AutoFit();
            _range.EntireRow.AutoFit();

            Excel.Range colorRange;
            colorRange = _xlWorkSheet.get_Range("y1", "ag100");
            colorRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
        }

It works fine but when the Program will save the file it comes this screen:

Do you have an idea how to change it that I don't become that window ?

I save the file with this function:

   internal string SaveFile(string writePath)
    {
        FormatCells(_xlWorkSheet.Cells.Borders);
        string fileName = string.Format("{0}_{1}.xlsx","running_", DateTime.Now.ToString("yyyyMMdd_HHmmss"));
        _xlWorkBook.SaveAs(Path.Combine(writePath, fileName), Excel.XlFileFormat.xlWorkbookNormal, misValue,
           misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
        _xlWorkBook.Close(true, misValue, misValue);
        _xlApp.Quit();

        ReleaseObject(_xlWorkSheet);
        ReleaseObject(_xlWorkBook);
        ReleaseObject(_xlApp);
        return fileName;
    }

Instead of using Office Automation, use the Open XML SDK or a library like EPPlus to create an actual `xlsx` file directly. This way you won't need to have Excel installed on the user's computer. — Panagiotis Kanavos, Jan 05 '16 at 12:46
As for your question, use `xlOpenXMLWorkbook` instead of `xlWorkbookNormal`, which is appropriate for `xls` files — Panagiotis Kanavos, Jan 05 '16 at 12:49

score 3 · Accepted Answer · edited May 23 '17 at 12:04

3

Save the file as the newer format xlsx instead of xls.

As covered in greater detail here: Exporting to .xlsx using Microsoft.Office.Interop.Excel SaveAs Error

To programatically save in the XLSX file format you need to use

Excel.XlFileFormat.xlOpenXMLWorkbook

As the file format.

edited May 23 '17 at 12:04

Community

1
1

answered Jan 05 '16 at 12:33

Graeme

1,643
15
27

I've updated the question with the save function...is that right ? – kb_ Jan 05 '16 at 12:36
3

Ah, I miss-understood your original question sorry, I thought you were simply clicking on save in the File menu. I haven't tried saving from within a function so I am not certain. From a quick search it seems you need to use: Excel.XlFileFormat.xlOpenXMLWorkbook which I found here: http://stackoverflow.com/questions/9769703/exporting-to-xlsx-using-microsoft-office-interop-excel-saveas-error – Graeme Jan 05 '16 at 12:42
1

@invidicult - the numerical equivalent to xlOpenXMLWorkbook is 51 if you have any trouble with that constant. You might also consider removing the **.XLSX** from the constructed filename. If you save the workbook correctly, Excel will add .xlsx by itself. – Jan 05 '16 at 12:50
1

You're welcome, I have updated my answer to include the actual answer now too :) (make it more useful) – Graeme Jan 05 '16 at 12:56

Change color in excel file

1 Answers1