automating excel with c#

Asian · Apr 17, 2013

Hi, suppose I have 3 strings for e.g X, Y and Z and they will always be inserted directly underneath each other.
I would like to know how to maybe use a for loop to insert those 3 strings x number of times.

for e.g if I input 2 then it will do this:
x
y
z
x
y
z

HavocXphere · Apr 17, 2013

You can do that with crafty Excel formulas - no need for c#. Like so:

https://www.dropbox.com/s/gf08sth4tukgphg/Numbers X.xls

If its for educational purposes & want to learn how to automate it then you're in for a bit of a rough ride. MS does not make this easy. You'll likely also have better luck with vb than c#.

Wyzak · Apr 17, 2013

There are ways to do exactly what you want in C# but it is normally a bit of a pain. The machine that you run this app on needs to have office installed. The app needs to know what version of office is installed so that it uses the correct dlls. It will effectively open an excel workbook in the background and just hide it from view.

There is also some compatibility issues if you already have excel open at the time.

If you want you can send me a pm and I will assist you with the interops and dlls tomorrow.

DawieBotes · Apr 17, 2013

Vbscripts in excel. You can manipulate the text in each cell and even show window-like dialogue boxes to get your initial value, eg. 2. You activate the vbscript with macros.

Necuno · Apr 17, 2013

That's the old method and AFAIK openxml is what you should be using and there are quite few free projects for noncommercial use that works really good.

Wyzak said:
There are ways to do exactly what you want in C# but it is normally a bit of a pain. The machine that you run this app on needs to have office installed. The app needs to know what version of office is installed so that it uses the correct dlls. It will effectively open an excel workbook in the background and just hide it from view.

There is also some compatibility issues if you already have excel open at the time.

If you want you can send me a pm and I will assist you with the interops and dlls tomorrow.

HavocXphere · Apr 18, 2013

Blind Faith said:
That's the old method and AFAIK openxml is what you should be using and there are quite few free projects for noncommercial use that works really good.

How exactly do you propose to *control* Excel via an XML based file format? The one is a static file format, the other is an action that requires a dynamic state & some form of built in logic. idk...lets hope I'm totally misunderstanding you.

egenis · Apr 18, 2013

HavocXphere said:
How exactly do you propose to *control* Excel via an XML based file format? The one is a static file format, the other is an action that requires a dynamic state & some form of built in logic. idk...lets hope I'm totally misunderstanding you.

Some code I wrote to export table data... Uses openxml not the horrible interops.
You do need the openxml sdk installed on the target machine as well as excel.

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace Avis.RAC.TraficFines.ExcelHelper
{
public class Exporter
{
static string[] _headerColumns = new string[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA"};
static int colCnt = 0;

public static string ExportData<T>(string fileName, IEnumerable<T> dataList)
{
if (dataList == null || dataList.Count() < 1)
{
return null;
}

List<string> excelRows = FormatListData<T>(dataList);
string finalFileName = CreateFile(fileName, excelRows);

return finalFileName;
}

/// <summary>
/// Reflects the name of the properties to act as the column headers for the spreadsheet
/// Reflects the values of the properties of T contained in the list to build values for spreadsheet
/// Creates a pipe delimited list of strings from the list of T
/// </summary>
private static List<string> FormatListData<T>(IEnumerable<T> dataList)
{
List<string> excelRows = new List<string>();
List<PropertyInfo> propInfo = null;
StringBuilder rowBuilder = new StringBuilder();

var header = dataList.First();
propInfo = header.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance).ToList();

colCnt = propInfo.Count;

foreach (var prop in propInfo)
{
string name = prop.Name;
rowBuilder.Append(name + '|');
}

excelRows.Add(rowBuilder.ToString());
rowBuilder.Clear();

foreach (var item in dataList)
{
propInfo = item.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance).ToList();

foreach (var prop in propInfo)
{
var value = prop.GetValue(item, null) ?? string.Empty;
rowBuilder.Append(value.ToString() + '|');
}

excelRows.Add(rowBuilder.ToString());
rowBuilder.Clear();
}
return excelRows;
}

private static string CreateFile(string fileName, List<string> excelRows)
{
try
{
string fullFileName = "Exports\\" + fileName + ".xlsx";

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

File.Copy("Exports\\ExportTemplate.xlsx", fullFileName);

using (SpreadsheetDocument workBook = SpreadsheetDocument.Open(fullFileName, true))
{
WorkbookPart workbookPart = workBook.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

int idx = 1;

foreach (var row in excelRows)
{
Row contentRow = CreateContentRow(idx, row);
idx++;
sheetData.AppendChild(contentRow);
}
}

return fullFileName;
}
catch (Exception ex)
{
throw ex;
}
}

private static Row CreateContentRow(int index, string row)
{
try
{
Row newRow = new Row();
newRow.RowIndex = (UInt32)index;
string[] bits = row.Split(new char[]{'|'}, StringSplitOptions.None);

for (int i = 0; i < bits.Length; i++)
{
if (i >= colCnt)
{
continue;
}
Cell cell = new Cell();
cell.DataType = CellValues.InlineString;
cell.CellReference = _headerColumns + index;

InlineString inlineString = new InlineString();
Text text = new Text { Text = bits.ToString() };
inlineString.AppendChild(text);
cell.AppendChild(inlineString);
newRow.AppendChild(cell);
}

return newRow;
}
catch (Exception ex)
{
throw ex;
}
}
}
}

death192 · Apr 18, 2013

Here is a Little Example with a Very nice XML Exporter Class (not saying its the best way but it will get the Job done

)

This example can be adapted to basically export anything you can put into a data table
i hope it helps a few others out there .

Code:

          Private void ExcelExport(object x,object y, object z ,int _repeatcount)
             {
               var dt = new DataTable()

              dt.Columns.add(); //this can also have a header e.g dt.Columns.add("myheader");

              for (int i = 0; i <= _repeatcount;i++)
                    {
                       dt.Rows.Add(x);
                       dt.Rows.Add(y);
                       dt.Rows.Add(z);
                    }
            var sfd = new SaveFileDialog {FileName = "results.xls"};

            if (sfd.ShowDialog() != DialogResult.OK)
            {
                return;
            }
            var ds = new DataSet();
            ds.Tables.Add(dt);         
            ExcelXmlExportHelper.ToFormattedExcel(ds, sfd.FileName);
}

Exporter Class

Code:

 internal class ExcelXmlExportHelper
    {
        public static int GlobalInsert { get; set; }

        // Get a string with excel's XML headers
        private static string GetXmlWorkbookTemplate()
        {
            var sb = new StringBuilder(818);

            sb.AppendFormat(@"<?xml version=""1.0""?>{0}", Environment.NewLine);
            sb.AppendFormat(@"<?mso-application progid=""Excel.Sheet""?>{0}", Environment.NewLine);
            sb.AppendFormat(@"<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet""{0}", Environment.NewLine);
            sb.AppendFormat(@" xmlns:o=""urn:schemas-microsoft-com:office:office""{0}", Environment.NewLine);
            sb.AppendFormat(@" xmlns:x=""urn:schemas-microsoft-com:office:excel""{0}", Environment.NewLine);
            sb.AppendFormat(@" xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet""{0}", Environment.NewLine);
            sb.AppendFormat(@" xmlns:html=""http://www.w3.org/TR/REC-html40"">{0}", Environment.NewLine);
            sb.AppendFormat(@" <ss:Styles>{0}", Environment.NewLine);
            sb.AppendFormat(@"  <ss:Style ss:ID=""Default"" ss:Name=""Normal"">{0}", Environment.NewLine);
            sb.AppendFormat(@"   <ss:Alignment ss:Vertical=""Bottom""/>{0}", Environment.NewLine);

            sb.AppendFormat(
                @"   <ss:Font ss:FontName=""Calibri"" x:Family=""Swiss"" ss:Size=""11"" ss:Color=""#000000""/>{0}",
                Environment.NewLine);
            sb.AppendFormat(@"   <ss:Interior/>{0}", Environment.NewLine);
            sb.AppendFormat(@"   <ss:NumberFormat/>{0}", Environment.NewLine);
            sb.AppendFormat(@"   <ss:Protection/>{0}", Environment.NewLine);
            sb.AppendFormat(@"  </ss:Style>{0}", Environment.NewLine);

            sb.AppendFormat(@"  <ss:Style ss:ID=""s62"">{0}", Environment.NewLine);

            sb.AppendFormat(@"   <ss:Borders>{0}", Environment.NewLine);
            sb.AppendFormat(@"   <ss:Border ss:Position=""Bottom"" ss:LineStyle=""Continuous"" ss:Weight=""1"" />{0}",
                            Environment.NewLine);
            sb.AppendFormat(@"   <ss:Border ss:Position=""Top"" ss:LineStyle=""Continuous"" ss:Weight=""1"" />{0}",
                            Environment.NewLine);
            sb.AppendFormat(@"   <ss:Border ss:Position=""Left"" ss:LineStyle=""Continuous"" ss:Weight=""1"" />{0}",
                            Environment.NewLine);
            sb.AppendFormat(@"   <ss:Border ss:Position=""Right"" ss:LineStyle=""Continuous"" ss:Weight=""1"" />{0}",
                            Environment.NewLine);
            sb.AppendFormat(@"   </ss:Borders>{0}", Environment.NewLine);

            sb.AppendFormat(
                @"   <ss:Font ss:FontName=""Calibri"" x:Family=""Swiss"" ss:Size=""11"" ss:Color=""#000000""{0}",
                Environment.NewLine);
            sb.AppendFormat(@"    ss:Bold=""1""/>{0}", Environment.NewLine);
            sb.AppendFormat(@"  </ss:Style>{0}", Environment.NewLine);
            sb.AppendFormat(@"  <ss:Style ss:ID=""s63"">{0}", Environment.NewLine);
            sb.AppendFormat(@"   <ss:NumberFormat ss:Format=""Short Date""/>{0}", Environment.NewLine);
            sb.AppendFormat(@"  </ss:Style>{0}", Environment.NewLine);

            sb.AppendFormat(@"  <ss:Style ss:ID=""s60"">{0}", Environment.NewLine);
            sb.AppendFormat(@"   <ss:Alignment ss:Vertical=""Bottom""/>{0}", Environment.NewLine);

            sb.AppendFormat(@"   <ss:Borders>{0}", Environment.NewLine);
            sb.AppendFormat(@"   <ss:Border ss:Position=""Bottom"" ss:LineStyle=""Continuous"" ss:Weight=""1"" />{0}",
                            Environment.NewLine);
            sb.AppendFormat(@"   <ss:Border ss:Position=""Top"" ss:LineStyle=""Continuous"" ss:Weight=""1"" />{0}",
                            Environment.NewLine);
            sb.AppendFormat(@"   <ss:Border ss:Position=""Left"" ss:LineStyle=""Continuous"" ss:Weight=""1"" />{0}",
                            Environment.NewLine);
            sb.AppendFormat(@"   <ss:Border ss:Position=""Right"" ss:LineStyle=""Continuous"" ss:Weight=""1"" />{0}",
                            Environment.NewLine);
            sb.AppendFormat(@"   </ss:Borders>{0}", Environment.NewLine);

            sb.AppendFormat(@"  </ss:Style>{0}", Environment.NewLine);

            sb.AppendFormat(@" </ss:Styles>{0}", Environment.NewLine);

            sb.Append(@"{0}\r\n</Workbook>");
            return sb.ToString();
        }

        // some special characters replacement (escaping)
        private static string ReplaceXmlChar(string input)
        {
            input = input.Replace("&", "&amp");
            input = input.Replace("<", "&lt;");
            input = input.Replace(">", "&gt;");
            input = input.Replace("\"", "&quot;");
            input = input.Replace("'", "&apos;");
            return input;
        }

        // get the xml formatted string for an specific data cell,
        // we translate c# types to excel data types and fix the nulls
      
        private static string GetCellXml(Type type, object cellData, bool hasBorder)
        {
            object data = (cellData is DBNull) ? "" : cellData;

            string border = "";
            if (hasBorder)
            {
                border = @" ss:StyleID=""s60""";
            }

            if (type.Name.Contains("Int") || type.Name.Contains("Double") || type.Name.Contains("Decimal") ||
                type.Name.Contains("decimal"))
            {
                return string.Format("<Cell" + border + "><Data ss:Type=\"Number\">{0}</Data></Cell>", data);
            }

            if (type.Name.Contains("Date") && data.ToString() != string.Empty)
            {
                return string.Format("<Cell ss:StyleID=\"s63\"><Data ss:Type=\"DateTime\">{0}</Data></Cell>",
                                     Convert.ToDateTime(data).ToString("yyyy-MM-dd"));
            }

            decimal nad;
            if (decimal.TryParse(cellData.ToString(), out nad))
            {
                return string.Format("<Cell" + border + "><Data ss:Type=\"Number\">{0}</Data></Cell>", data);
            }

            return string.Format("<Cell" + border + "><Data ss:Type=\"String\">{0}</Data></Cell>",
                                 ReplaceXmlChar(data.ToString()));
        }

  
        public static void ToFormattedExcel(DataSet dsInput, string filename)
        {
            GlobalInsert = 0;

            // we get the xml headers first
            string excelTemplate = GetXmlWorkbookTemplate();

            string tablas = "<Worksheet ss:Name=\"Result\">";

            tablas += "\r\n<Table>\r\n";

            tablas = dsInput.Tables.Cast<DataTable>()
                            .Aggregate(tablas, (current, dt) => current + GetExcelTableXml(dt, true));
            tablas += "\r\n</Table>\r\n";
            tablas += "\r\n</Worksheet>";

            string excelXml = string.Format(excelTemplate, tablas);

      
            try
            {
                File.Delete(filename);
                var sw = new StreamWriter(filename);

                sw.Write(excelXml);

                sw.Flush();
                sw.Close();

                sw.Dispose();
            }
            catch (Exception)
            {
                MessageBox.Show(@"Error Writing Excel file");
            }
        }

        // loop the datatable and make the excel xml for the titles
        // and the data cells
        public static string GetExcelTableXml(DataTable dt, bool hasBorder)
        {
            //write column name row
            string result = dt.Columns.Cast<DataColumn>()
                              .Aggregate("\r\n<Row>",
                                         (current, dc) =>
                                         current +
                                         string.Format(
                                             "<Cell ss:StyleID=\"s62\"><Data ss:Type=\"String\">{0}</Data></Cell>",
                                             ReplaceXmlChar(dc.ColumnName)));

            result += "\r\n</Row>";

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                result += "\r\n<Row>";

                result = dt.Columns.Cast<DataColumn>()
                           .Aggregate(result,
                                      (current, dc) =>
                                      current + GetCellXml(dc.DataType, dt.Rows[i][dc.ColumnName], hasBorder));

                result += "</Row>";
            }

            return result;
        }
    }

Ancalagon · Apr 18, 2013

I've done a fair amount of work with the OpenXML SDK.

Its clunky, but it works. Word seems to have a more logical format than Excel, but even that isnt very logical. Unzip and XLSX file and have a look at it.

Just have a look at samples - once you get the hang of it, it isnt too bad.

Interop is the old way of doing things, and also not really supported anymore, and especially not in a server context.

guest2013-1 · Apr 18, 2013

What's the point of using OpenXML if the target needs Excel installed as well?

Ancalagon · Apr 18, 2013

AcidRaZor said:
What's the point of using OpenXML if the target needs Excel installed as well?

They dont need to have it installed to use OpenXML.

I suspect that is not what you mean - the advantage is that it can be deployed in server environments where Excel is not needed or required, is broadly compatible with other office suites, and is also the Microsoft recommended way of interacting with Excel files at the moment. This means it is recent and up to date.

In fact I remember in a server context, it was our only choice. Perhaps if the app is a desktop app, then Interop would be okay.

guest2013-1 · Apr 18, 2013

egenis said:
Some code I wrote to export table data... Uses openxml not the horrible interops.
You do need the openxml sdk installed on the target machine as well as excel.

Ancalagon said:
They dont need to have it installed to use OpenXML.

I suspect that is not what you mean - the advantage is that it can be deployed in server environments where Excel is not needed or required, is broadly compatible with other office suites, and is also the Microsoft recommended way of interacting with Excel files at the moment. This means it is recent and up to date.

In fact I remember in a server context, it was our only choice. Perhaps if the app is a desktop app, then Interop would be okay.

I was referring to the above quote

diabolus · Apr 18, 2013

If you want to control Excel workbooks without actually being in Excel, then i've found the quickest route to get going is using Python and the xlrd/xlwt libraries (without needing Office on the PC) .

It's ironically much easier than doing it in C#, although i've found in the long run, you can do more with the same code much easier if you gone the C# route (i.e. like turning it into a web service or an Excel Add-In, it's the same code, just different packaging/ui) . So C# is not that much of a pain, but Python being a scripting language (as opposed to compiled like C#) implies you can debug and troubleshoot these things much easier, and with Excel it can be a pain . Especially if you're traversing across sheets and picking data based on data in other cells etc etc.

semaphore · Apr 18, 2013

http://www.tmssoftware.com/site/flexcelnet.asp

Its not free, but i bought a license.

egenis · Apr 18, 2013

egenis said:
You do need the openxml sdk installed on the target machine as well as excel.

AcidRaZor said:
I was referring to the above quote

Oops.. Was supposed to say don't require excel...

Asian · Apr 18, 2013

I'm going to try make this as short as possible.
Basically assuming I insert for example:

Row 1 column A = [XX="variable"]
Row 2 column A = yyyyyyy
Row 3 column A = [XX="variable"]
Row 4 column A = yyyyyyy

Now i have to basically do that over a hundred times.

I know the basics of making excel work with C#. I've added the necessary references and what I've done to try and save me time is added a list of strings that will replace the "variable" from the above into list<string>. I was thinking of using a for loop and just replace the "variable" with the strings that's in the list. The problem I'm having is getting it to insert it in that specific order.

for example this is what I've done so far.

int length = list.count();

for (int i = 0; i < length; i++)
{
worksheet.cells[i+1,1] = [xx= + list[i] + ]
}

the problem is how to insert the second string consecutively after the first one.

is there no way of using a second for loop maybe?

kripstoe · Apr 25, 2013

Make a screenshot of excel using of a small sample of the data and post here. The above is confusing / lack detail.

Join the MyBroadband community

Get started

automating excel with c#

Asian

Member

HavocXphere

Honorary Master

Wyzak

Expert Member

DawieBotes

Member

Necuno

Court Jester

HavocXphere

Honorary Master

egenis

Expert Member

death192

Well-Known Member

Ancalagon

Honorary Master

guest2013-1

guest

Ancalagon

Honorary Master

guest2013-1

guest

diabolus

Executive Member

semaphore

Honorary Master

egenis

Expert Member

Asian

Member

kripstoe

Expert Member