Search This Blog

Sunday, January 28, 2007

Export Data to Excel Sheet

I have notice that there is a lot of traffic to my Blog because of a post I have about exporting Excel chart to an image. I thought that it would be good to share more stuff about Excel automation in C#. Below you can find a method to export data into excel sheet. This function uses a list view as the data source.


using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.Globalization;
using System.Threading;
using System.Drawing;
using ExcelAutomation = Microsoft.Office.Interop.Excel;

namespace ExcelUtils
{
public static class Excel
{
private static void BorderAroundCell(ExcelAutomation.Range CellRange)
{

CellRange.BorderAround(ExcelAutomation.XlLineStyle.xlContinuous,
ExcelAutomation.XlBorderWeight.xlThin,
ExcelAutomation.XlColorIndex.xlColorIndexAutomatic,
Type.Missing);

}

public static void ReportFromListView(string reportName, ListView
listView)
{

ExcelAutomation.Application excelApp = new ExcelAutomation.ApplicationClass();
excelApp.UserControl = true;
CultureInfo oldCultureInfo = Thread.CurrentThread.CurrentCulture;
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
ExcelAutomation.Workbook workbook = excelApp.Workbooks.Add(Type.Missing);
ExcelAutomation.Worksheet worksheet = (ExcelAutomation.Worksheet)workbook.Worksheets.get_Item(1);
worksheet.Name = reportName;
//Headers
foreach (ColumnHeader columnHeader in listView.Columns)
{
worksheet.Cells[1, columnHeader.Index + 1] = columnHeader.Text;
}
string[] letters = new string[26]{"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"};
for (int i = 0; i < listView.Columns.Count; i++)

{
string headerCell = letters.GetValue(i) + "1";
worksheet.get_Range(headerCell, headerCell).Font.Bold = true;
BorderAroundCell(worksheet.get_Range(headerCell, headerCell));
worksheet.get_Range(headerCell, headerCell).Interior.ColorIndex = 36;
}
//Content
for (int i = 0; i < listView.Items.Count; i++)

{
for (int j = 0; j < listView.Columns.Count; j++)
{
string dataCell = letters.GetValue(j) + (i + 2).ToString();
worksheet.Cells[i + 2, j + 1] = listView.Items[i].SubItems[j].Text;
BorderAroundCell(worksheet.get_Range(dataCell, dataCell));
}
}
worksheet.Columns.AutoFit();
worksheet.Columns.HorizontalAlignment = ExcelAutomation.XlHAlign.xlHAlignLeft;
excelApp.Visible = true;
Thread.CurrentThread.CurrentCulture = oldCultureInfo;
}
}
}

2 comments:

Anonymous said...

Great Man !!! - Thanks !!

Anonymous said...

This code is great, but when I have less than 6 rows in the listView, and when I try to export the information, there is an error:
InvalidArgument=Value of '1' is not valid for 'index'
Parameter name: index.