I need to
- create a excel file,
- which will multiple worksheets.
- every worksheet will have a header row.
Excel file name, worksheet name, header row column names will be provided dynamically.
ScriptMain.cs file
namespace ST_50e9d5ec3f17426585feb2e6ab8d41bb{ [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute] public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase { public static StringBuilder informationLog = new StringBuilder(); //Log log; public void Main() {
//Dts.TaskResult = (int)ScriptResults.Success; try { SqlConnection sqlConnection = new SqlConnection(); sqlConnection = ConnectToDataSource(“ADONET_IntegratedCare”);
DataTable dtControl = new DataTable(); dtControl =GetControlInfo(sqlConnection);
ClearFolder(dtControl); SQLToExcel(sqlConnection, dtControl);
//MoveFile(dtControl); sqlConnection.Close(); dtControl.Clear(); } catch(Exception x) { StringBuilder errorMessage = new StringBuilder(); Exception e = x; while (e != null) { errorMessage.AppendFormat(“Exception occured [{0}]\n”, e.Source, e.Message); errorMessage.AppendFormat(“Stack trace: \n{0}\n”, e.StackTrace); errorMessage.AppendLine(); e = e.InnerException; } //log.Error(“Adhoc – exception”, errorMessage.ToString()); } } } }
ScriptMainDataAccess.cs
using Microsoft.SqlServer.Dts.Runtime;
using System;
using System.Data;
using System.Data.SqlClient;
namespace ST_50e9d5ec3f17426585feb2e6ab8d41bb
{
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region private SqlConnection ConnectToDataSource(SqlConnection sqlConnection)
private SqlConnection ConnectToDataSource(String dataSource)
{
SqlConnection sqlConnection = new SqlConnection();
try
{
using (ConnectionManager adonetConnection = (ConnectionManager)Dts.Connections[dataSource])
{
sqlConnection = (SqlConnection)adonetConnection.AcquireConnection(Dts.Transaction);
if (sqlConnection.State == ConnectionState.Open)
{
sqlConnection.Close();
}
sqlConnection.Open();
//Log.Information(“Adhoc”, String.Format(“Connect to the data source: {0}.{1}”, sqlConnection.DataSource, sqlConnection.Database));
};
}
catch (Exception e)
{
throw e;
}
return sqlConnection;
}
#endregion
#region private DataTable GetControlInfo(SqlConnection sqlConnection)
private DataTable GetControlInfo(SqlConnection sqlConnection)
{
SqlDataAdapter da = new SqlDataAdapter();
DataSet dsControl = new DataSet();
DataTable dtControl = new DataTable();
SqlCommand sqlCommand;
try
{
String controlStatement = Dts.Variables[“$Package::ControlStatement”].Value.ToString();
String dataType = Dts.Variables[“$Package::DataType”].Value.ToString();
sqlCommand = new SqlCommand(controlStatement, sqlConnection);
da.SelectCommand = sqlCommand;
da.Fill(dsControl, “Adhoc_DataTable”);
dtControl = dsControl.Tables[“Adhoc_DataTable”];
//log.Information(“Adhoc – Information”, “Get control information from the package parameter: $Package::ControlStatement”);
}
catch (Exception e)
{
throw e;
}
return dtControl;
}
#endregion
}
}
ScriptMainFile.cs
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
namespace ST_50e9d5ec3f17426585feb2e6ab8d41bb
{
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region private void ClearFolder(DataTable dtControl)
private void ClearFolder(DataTable dtControl)
{
//delete all the files from a directory
try
{
foreach (DataRow dr in dtControl.Rows)
{
String path = dr[“DataProcessingDirectory”].ToString();
if (Directory.Exists(path))
{
DirectoryInfo di = new DirectoryInfo(path);
foreach (FileInfo file in di.GetFiles())
{
file.Delete();
//log.Information(“Adhoc-information”, string.Format(“Deleted file {0} “, path+file));
}
}
else
{
Directory.CreateDirectory(path);
};
}
}
catch (Exception e)
{
throw e;
}
}
#endregion
#region private void MoveFile(DataTable dtControl)
private void MoveFile(DataTable dtControl)
{
try
{
foreach (DataRow dr in dtControl.Rows)
{
String sourcePath = dr[“DataProcessingDirectory”].ToString();
String destinationPath = dr[“FilePath”].ToString();
if (Directory.Exists(destinationPath) == false)
{
Directory.CreateDirectory(destinationPath);
}
DirectoryInfo di = new DirectoryInfo(sourcePath);
foreach (FileInfo fileName in di.GetFiles())
{
if (File.Exists(destinationPath + fileName))
{
File.Delete(destinationPath + fileName);
}
fileName.MoveTo(destinationPath + fileName);
//log.Information(“Adhoc-information”, string.Format(“Move the file from {0} to {1}”, sourcePath + fileName, destinationPath + fileName));
}
}
}
catch (Exception e)
{
throw e;
}
}
#endregion
}
}
ScriptMainExcel.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Linq;
using System.Windows.Forms;
using System.Collections.Generic;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
using X14 = DocumentFormat.OpenXml.Office2010.Excel;
using X15 = DocumentFormat.OpenXml.Office2013.Excel;
namespace ST_50e9d5ec3f17426585feb2e6ab8d41bb
{
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
//MessageBox.Show(fileExtension);
#region private void SQLToExcel(SqlConnection sqlConnection, DataTable dtControl)
private void SQLToExcel(SqlConnection sqlConnection, DataTable dtControl)
{
try
{
foreach (DataRow dr in dtControl.Rows)
{
String filePath = dr[“DataProcessingDirectory”].ToString();
String workbookNamePostfix = dr[“WorkbookNamePostfix”].ToString();
String fileExtension = dr[“FileExtension”].ToString().ToLower();
String worksheetName = dr[“WorksheetName”].ToString();
String sqlStatement = dr[“SqlStatement”].ToString();
String fileName = filePath
+ workbookNamePostfix
+ DateTime.Now.ToString(“yyyyMMdd”)
+ fileExtension
;
SqlDataAdapter da = new SqlDataAdapter();
DataSet dsResult = new DataSet();
DataTable dtResult = new DataTable();
SqlCommand sqlCommand = new SqlCommand(sqlStatement, sqlConnection);
da.SelectCommand = sqlCommand;
da.Fill(dsResult, “Adhoc_Result”);
dtResult = dsResult.Tables[“Adhoc_Result”];
if (fileExtension == “.xlsx”)
{
if (File.Exists(fileName))//if file already exist
{
OpenExcelFile_OpenXml(dtResult, fileName, worksheetName);
}
else//if file not exist
{
CreateExcelFile_OpenXml(dtResult, fileName, worksheetName);
}
/*The auto-fit logic is something which is implemented by Microsoft Excel, and is not a part of the OpenXML spreadsheet format.
* Auto-fit involves measuring the width (or height) of the value in each cell and finding the maximum value.
* In order to implement auto-fit in your own code, you will have to manually measure the text.*/
ExcelStyle_InteropExcel(fileName, worksheetName);
}
}
}
catch (Exception e)
{
throw e;
}
}
#endregion
#region private void CreateExcelFile_OpenXml(DataTable dtResult, string fileName, string worksheetName)
private void CreateExcelFile_OpenXml(DataTable dtResult, string fileName, string worksheetName)
{
try
{
SpreadsheetDocument spreadsheetDocument;
WorkbookPart workbookPart;
WorksheetPart worksheetPart;
WorkbookStylesPart wbsp;
Sheets sheets;
Sheet sheet;
int rowNumber = dtResult.Rows.Count;
int columNumber = dtResult.Columns.Count;
// Create a spreadsheet document by supplying the filepath.
// By default, AutoSave = true, Editable = true, and Type = xlsx.
spreadsheetDocument = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
workbookPart = spreadsheetDocument.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
// add styles to sheet
wbsp = workbookPart.AddNewPart<WorkbookStylesPart>();
wbsp.Stylesheet = GenerateWorkbookStylesPart1Content();
// generate rows
SheetData sheetData = CreateSheetData_OpenXml(dtResult);
// Add a WorksheetPart to the WorkbookPart.
worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
Worksheet worksheet = new Worksheet();
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;
wbsp.Stylesheet.Save();
/*set a auto filter*/
AutoFilter autoFilter = new AutoFilter() { Reference = (“A1:” + ExcelColumnNumberToName(columNumber.ToString()) + “1”) };
worksheet.Append(autoFilter);
// Add Sheets to the Workbook.
sheets = workbookPart.Workbook.AppendChild(new Sheets());
// Append a new worksheet and associate it with the workbook.
sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = worksheetName };
sheets.Append(sheet);
workbookPart.Workbook.Save();
//string cellReference = GetCellReference(@”c:\temp\test.xlsx”, “Sheet1”, 1, “Quantity”);
//Console.WriteLine(GetColumnName(cellReference)); //prints D for your example
// Close the document.
//spreadsheetDocument.WorkbookPart.Workbook.Save();
spreadsheetDocument.Close();
}// try
catch (Exception e)
{
throw e;
}
}
#endregion
#region private void OpenExcelFile_OpenXml(DataTable dtResult, string fileName, string worksheetName)
private void OpenExcelFile_OpenXml(DataTable dtResult, string fileName, string worksheetName)
{
try
{
SpreadsheetDocument spreadsheetDocument;
WorksheetPart worksheetPart;
Sheets sheets;
Sheet sheet;
int rowNumber = dtResult.Rows.Count;
int columNumber = dtResult.Columns.Count;
// open a spreadsheet document to modify
spreadsheetDocument = SpreadsheetDocument.Open(fileName, true);
// generate rows
SheetData sheetData = CreateSheetData_OpenXml(dtResult);
// Add a WorksheetPart to the WorkbookPart.
worksheetPart = spreadsheetDocument.WorkbookPart.AddNewPart<WorksheetPart>();
Worksheet worksheet = new Worksheet();
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;
/*set a auto filter*/
AutoFilter autoFilter = new AutoFilter() { Reference = (“A1:” + ExcelColumnNumberToName(columNumber.ToString()) + “1”) };
worksheet.Append(autoFilter);
// Add Sheets to the Workbook.
sheets = spreadsheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>();
string relationshipId = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart);
// Get a unique ID for the new worksheet.
uint sheetId = 1;
if (sheets.Elements<Sheet>().Count() > 0)
{
sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
// Append the new worksheet and associate it with the workbook.
sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = worksheetName };
sheets.Append(sheet);
//worksheetPart.Worksheet.Save();
// Close the document.
//spreadsheetDocument.WorkbookPart.Workbook.Save();
spreadsheetDocument.Close();
}// try
catch (Exception e)
{
throw e;
}
}
#endregion
#region private SheetData CreateSheetData_OpenXml(DataTable dtResult)
private SheetData CreateSheetData_OpenXml(DataTable dtResult)
{
SheetData sheetData = new SheetData();
//worksheetPart.Worksheet = new Worksheet(sheetData);
Row headerRow = new Row();
List<String> columns = new List<string>();
foreach (System.Data.DataColumn column in dtResult.Columns)
{
columns.Add(column.ColumnName);
Cell cell = new Cell() { StyleIndex = (UInt32Value)2U };
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(column.ColumnName);
//MessageBox.Show(“”);
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (DataRow dsrow in dtResult.Rows)
{
Row newRow = new Row();
foreach (String col in columns)
{
//Cell cell = new Cell();
Cell cell = new Cell() { StyleIndex = (UInt32Value)1U };
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(dsrow[col].ToString());
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
return sheetData;
}
#endregion
#region private Stylesheet GenerateWorkbookStylesPart1Content()
// Generates content of workbookStylesPart1.
private Stylesheet GenerateWorkbookStylesPart1Content()
{
Stylesheet stylesheet1 = new Stylesheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = “x14ac” } };
stylesheet1.AddNamespaceDeclaration(“mc”, “http://schemas.openxmlformats.org/markup-compatibility/2006”);
stylesheet1.AddNamespaceDeclaration(“x14ac”, “http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac”);
Fonts fonts1 = new Fonts() { Count = (UInt32Value)2U, KnownFonts = true };
Font font1 = new Font();
FontSize fontSize1 = new FontSize() { Val = 11D };
Color color1 = new Color() { Theme = (UInt32Value)1U };
FontName fontName1 = new FontName() { Val = “Calibri” };
FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering() { Val = 2 };
FontScheme fontScheme1 = new FontScheme() { Val = FontSchemeValues.Minor };
font1.Append(fontSize1);
font1.Append(color1);
font1.Append(fontName1);
font1.Append(fontFamilyNumbering1);
font1.Append(fontScheme1);
Font font2 = new Font();
Bold bold1 = new Bold();
FontSize fontSize2 = new FontSize() { Val = 11D };
Color color2 = new Color() { Theme = (UInt32Value)1U };
FontName fontName2 = new FontName() { Val = “Calibri” };
FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering() { Val = 2 };
FontScheme fontScheme2 = new FontScheme() { Val = FontSchemeValues.Minor };
font2.Append(bold1);
font2.Append(fontSize2);
font2.Append(color2);
font2.Append(fontName2);
font2.Append(fontFamilyNumbering2);
font2.Append(fontScheme2);
fonts1.Append(font1);
fonts1.Append(font2);
Fills fills1 = new Fills() { Count = (UInt32Value)4U };
Fill fill1 = new Fill();
PatternFill patternFill1 = new PatternFill() { PatternType = PatternValues.None };
fill1.Append(patternFill1);
Fill fill2 = new Fill();
PatternFill patternFill2 = new PatternFill() { PatternType = PatternValues.Gray125 };
fill2.Append(patternFill2);
Fill fill3 = new Fill();
PatternFill patternFill3 = new PatternFill() { PatternType = PatternValues.Solid };
ForegroundColor foregroundColor1 = new ForegroundColor() { Theme = (UInt32Value)0U, Tint = -0.499984740745262D };
BackgroundColor backgroundColor1 = new BackgroundColor() { Indexed = (UInt32Value)64U };
patternFill3.Append(foregroundColor1);
patternFill3.Append(backgroundColor1);
fill3.Append(patternFill3);
Fill fill4 = new Fill();
PatternFill patternFill4 = new PatternFill() { PatternType = PatternValues.Solid };
ForegroundColor foregroundColor2 = new ForegroundColor() { Rgb = “FFFF0000” };
BackgroundColor backgroundColor2 = new BackgroundColor() { Indexed = (UInt32Value)64U };
patternFill4.Append(foregroundColor2);
patternFill4.Append(backgroundColor2);
fill4.Append(patternFill4);
fills1.Append(fill1);
fills1.Append(fill2);
fills1.Append(fill3);
fills1.Append(fill4);
Borders borders1 = new Borders() { Count = (UInt32Value)2U };
Border border1 = new Border();
LeftBorder leftBorder1 = new LeftBorder();
RightBorder rightBorder1 = new RightBorder();
TopBorder topBorder1 = new TopBorder();
BottomBorder bottomBorder1 = new BottomBorder();
DiagonalBorder diagonalBorder1 = new DiagonalBorder();
border1.Append(leftBorder1);
border1.Append(rightBorder1);
border1.Append(topBorder1);
border1.Append(bottomBorder1);
border1.Append(diagonalBorder1);
Border border2 = new Border();
LeftBorder leftBorder2 = new LeftBorder() { Style = BorderStyleValues.Thin };
Color color3 = new Color() { Indexed = (UInt32Value)64U };
leftBorder2.Append(color3);
RightBorder rightBorder2 = new RightBorder() { Style = BorderStyleValues.Thin };
Color color4 = new Color() { Indexed = (UInt32Value)64U };
rightBorder2.Append(color4);
TopBorder topBorder2 = new TopBorder() { Style = BorderStyleValues.Thin };
Color color5 = new Color() { Indexed = (UInt32Value)64U };
topBorder2.Append(color5);
BottomBorder bottomBorder2 = new BottomBorder() { Style = BorderStyleValues.Thin };
Color color6 = new Color() { Indexed = (UInt32Value)64U };
bottomBorder2.Append(color6);
DiagonalBorder diagonalBorder2 = new DiagonalBorder();
border2.Append(leftBorder2);
border2.Append(rightBorder2);
border2.Append(topBorder2);
border2.Append(bottomBorder2);
border2.Append(diagonalBorder2);
borders1.Append(border1);
borders1.Append(border2);
CellStyleFormats cellStyleFormats1 = new CellStyleFormats() { Count = (UInt32Value)1U };
CellFormat cellFormat1 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U };
cellStyleFormats1.Append(cellFormat1);
CellFormats cellFormats1 = new CellFormats() { Count = (UInt32Value)4U };
CellFormat cellFormat2 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };
CellFormat cellFormat3 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyBorder = true };
CellFormat cellFormat4 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat5 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyFill = true, ApplyBorder = true };
cellFormats1.Append(cellFormat2);
cellFormats1.Append(cellFormat3);
cellFormats1.Append(cellFormat4);
cellFormats1.Append(cellFormat5);
CellStyles cellStyles1 = new CellStyles() { Count = (UInt32Value)1U };
CellStyle cellStyle1 = new CellStyle() { Name = “Normal”, FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U };
cellStyles1.Append(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats() { Count = (UInt32Value)0U };
TableStyles tableStyles1 = new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = “TableStyleMedium2”, DefaultPivotStyle = “PivotStyleMedium9” };
StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension1 = new StylesheetExtension() { Uri = “{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}” };
stylesheetExtension1.AddNamespaceDeclaration(“x14”, “http://schemas.microsoft.com/office/spreadsheetml/2009/9/main”);
X14.SlicerStyles slicerStyles1 = new X14.SlicerStyles() { DefaultSlicerStyle = “SlicerStyleLight1” };
stylesheetExtension1.Append(slicerStyles1);
StylesheetExtension stylesheetExtension2 = new StylesheetExtension() { Uri = “{9260A510-F301-46a8-8635-F512D64BE5F5}” };
stylesheetExtension2.AddNamespaceDeclaration(“x15”, “http://schemas.microsoft.com/office/spreadsheetml/2010/11/main”);
X15.TimelineStyles timelineStyles1 = new X15.TimelineStyles() { DefaultTimelineStyle = “TimeSlicerStyleLight1” };
stylesheetExtension2.Append(timelineStyles1);
stylesheetExtensionList1.Append(stylesheetExtension1);
stylesheetExtensionList1.Append(stylesheetExtension2);
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
stylesheet1.Append(stylesheetExtensionList1);
return stylesheet1;
}
#endregion
#region private void ExcelStyle_InteropExcel(String fileName, String worksheetName)
private void ExcelStyle_InteropExcel(String fileName, String worksheetName)
{
Excel.Application xlApp = null;
Excel.Workbook xlWorkBook = null;
Excel.Sheets xlSheets = null;
Excel.Worksheet xlWorksheet = null;
try
{
FileInfo file = new FileInfo(fileName);
// initialize interop excel objects
xlApp = new Excel.Application();
xlApp.DisplayAlerts = false;
xlApp.Visible = true;
xlWorkBook = xlApp.Workbooks.Open(fileName, ReadOnly: false, Editable: true);
xlSheets = xlWorkBook.Sheets;
xlWorksheet = xlWorkBook.Sheets[worksheetName];
//#################################
//create table
//xlWorksheet.Columns.ClearFormats();
//xlWorksheet.Rows.ClearFormats();
/*get the number of columns and rows in a excel file*/
//int colNumber = xlWorksheet.UsedRange.Columns.Count;
//int rowNumber = xlWorksheet.UsedRange.Rows.Count;
////Add some formatting
//Excel.Range rng1 = xlWorksheet.get_Range((Excel.Range)xlWorksheet.Cells[2, 1], (Excel.Range)xlWorksheet.Cells[1, colNumber]);
//rng1.Font.Bold = true;
//rng1.Font.ColorIndex = 3;
//rng1.Borders.ColorIndex = 1;
xlWorksheet.Columns.AutoFit();
//#################################
xlWorkBook.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, false, Excel.XlSaveAsAccessMode.xlNoChange, false, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
xlWorkBook.Close();
xlApp.Quit();
}
catch (Exception e)
{
throw e;
}
finally
{
/*Release the resources. If you do not release the resources properly.
* File will be still opened by the process and it stops the user from modifying and even from opening*/
Marshal.ReleaseComObject(xlWorksheet);
Marshal.ReleaseComObject(xlSheets);
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(xlApp);
xlWorksheet = null;
xlSheets = null;
xlWorkBook = null;
xlApp = null;
GC.WaitForPendingFinalizers();
GC.Collect();
}
}
#endregion
#region public static int ExcelColumnNameToNumber(string input)
public static int ExcelColumnNameToNumber(string input)
{
input = input.ToUpperInvariant();
int sum = 0;
int charExcelNumber;
for (int i = 0; i < input.Length; i++)
{
/*
In excel A=1 and Z=26*. In ASCII value for character A iS 65 and Z is 122. ‘A’ and 65 can be used interchangebly.
If the input is ABC then input[0]=A, input[1]=B, input[3]=C.
Charracter respective Excel column number A=1, B=2, C=3.
The formula to convert: ABC=1*26^2 + 2*26^1 + 3*26^0.
*/
charExcelNumber = ((int)input[i] – ‘A’ + 1);
sum = sum + charExcelNumber * (int)Math.Pow(26, (input.Length – 1 – i));
}
return sum;
}
#endregion
#region private static String ExcelColumnNumberToName(String columnNumber)
private static String ExcelColumnNumberToName(String columnNumber)
{
int colNum;
Int32.TryParse(columnNumber, out colNum);
int dividend = colNum;
string columnName = String.Empty;
int modulo;
while (dividend > 0)
{
/*
In excel A=1 and Z=26*. In ASCII value for character A iS 65 and Z is 122. ‘A’ and 65 can be used interchangebly.
If the input is ABC then input[0]=A, input[1]=B, input[3]=C.
Charracter respective Excel column number A=1, B=2, C=3.
The formula to convert: ABC=1*26^2 + 2*26^1 + 3*26^0.
*/
modulo = (dividend) % 26;
columnName = Convert.ToChar(64 + modulo).ToString() + columnName;
dividend = (int)((dividend – modulo) / 26);
Console.WriteLine(modulo.ToString());
Console.WriteLine(dividend.ToString());
}
return columnName;
}
#endregion
}//class
}//namespace
Masud Ahmed 