Is if possible to use c# to access excel files?

Question

Hi simple question I want to go into a folder look for the excel files. Then go into each excel file and change red font coloring to black using c#. Is this Possible?

namespace Excel_font_color_change
{
    public partial class Form1 : Form
    {
        public Form1()
        {

            InitializeComponent();
        }



        private void button1_Click(object sender, EventArgs e)
        {
            List<string> HtmlPathList = new List<string>();
            string folderToSearch;

            FolderBrowserDialog fbd = new FolderBrowserDialog();
            fbd.ShowNewFolderButton = true;//allow user to create new folders through this dialog
            fbd.RootFolder = Environment.SpecialFolder.MyDocuments;//defaults to my computer
            System.Windows.Forms.DialogResult dr = fbd.ShowDialog();//make sure user clicks ok
            if (dr == DialogResult.OK)
            {
                folderToSearch = fbd.SelectedPath;//gets folder path
                try
                {
                    var allFiles = from files in Directory.EnumerateFiles(folderToSearch, "*.xls*", SearchOption.AllDirectories)
                                   select Path.GetFullPath(files);//gets all files with htm & htm + something for extensions
                    foreach (string filepath in allFiles)
                    {
                        HtmlPathList.Add(filepath);//adds each filepath found to the list
                    }
                }
                catch (UnauthorizedAccessException UAEx) { Console.WriteLine(UAEx.Message); }//error handling
                catch (PathTooLongException PathEx) { Console.WriteLine(PathEx.Message); }//error handling
                Console.WriteLine("1");
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {

        }
    }

This is what I have so far, I want the second button to take the file path that are in HtmlPathList and edit the font colour to black if it is red. I am looking thru How to read data of an Excel file using C#? right now.

Yes. How far have you got so far? You can enumerate files in a directory and then use something like EPPlus to open and modify the Excel files. — Simon, May 03 '13 at 10:35
@TimSchmelter You should add it as an answer since the question is _Is this Possible?_ -_- — Soner Gönül, May 03 '13 at 10:35
Take a look at Open XML if you don't want/have office installed. — juharr, May 03 '13 at 10:39
I have office installed and I have populating a array with excel files from a folder/subfolders. — Cmasterd, May 03 '13 at 10:48

score 0 · Answer 1 · answered May 03 '13 at 11:01

0

Check out this library. It works for xlsx only though.

http://www.microsoft.com/en-us/download/details.aspx?id=5124

If you want to read old xls files you can use Interop assemblies.

http://www.microsoft.com/en-us/download/details.aspx?id=3508

answered May 03 '13 at 11:01

Dwoolk

1,491
13
8

score 0 · Answer 2 · answered May 04 '13 at 13:09

This solution requires a reference to the Excel Interop Assemblies (Excel must be installed on the machine that is executing the utility as the Interop Assemblies are executing excel in the background):

    using Microsoft.Office.Interop.Excel;

    /// <summary>
    /// sets a cell range's font color
    /// </summary>
    /// <param name="filename"></param>
    /// <param name="startCell"></param>
    /// <param name="endCell"></param>
    /// <param name="color"></param>
    public void setCellRangeFontColor(string filename, string startCell, string endCell, string color)
    {
        Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

        if (xlApp == null)
        {
            MessageBox.Show("EXCEL could not be started. Check that your office installation and project references are correct.");
            return;
        }
        //xlApp.Visible = true;

        //Workbook wb = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
        Workbook wb = xlApp.Workbooks.Open(filename,
                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);
        Worksheet ws = (Worksheet)wb.Worksheets[1];

        if (ws == null)
        {
            MessageBox.Show("Worksheet could not be created. Check that your office installation and project references are correct.");
        }

        ws.get_Range(startCell, endCell).Font.Color = System.Drawing.ColorTranslator.ToOle(Color.FromName(color));

        wb.Close(true, Type.Missing, Type.Missing);

        //wb.Save();
        xlApp.Quit();

        releaseObject(ws);
        releaseObject(wb);
        releaseObject(xlApp);
    }

    public static void releaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch (Exception ex)
        {
            obj = null;
            //MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
        }
        finally
        {
            GC.Collect();
        }
    }

Is if possible to use c# to access excel files?

2 Answers2