Comma separated values in SQL Server

For comma seperated value



;WITH ctm
AS
(
SELECT id,id AS idlist ,VName [Name],0 AS [Status] FROM testTableData
WHERE fk_id = 0
UNION ALL
SELECT ct.id,tt.id AS idlist,tt.Vname [Name],1 AS [Status] FROM testTableData tt
INNER JOIN ctm AS ct ON ct.id = tt.FK_id
AND ct.[Status] = 0
)

SELECT * FROM ctm ORDER BY id,idlist,[Name]

Automate mail merge in C#

private void MailMerge()
{
# region local variables

Object oMissing = System.Reflection.Missing.Value;

Object oTrue = true;
Object oFalse = false;

Word.Application oWord = new Word.Application();
Word.Document oWordDoc = new Word.Document();


oWord.Visible = true;

// Word Mail Merge Template file
Object oTemplatePath = System.Windows.Forms.Application.StartupPath+ "\\Report.dot";

oWordDoc = oWord.Documents.Add(ref oTemplatePath, ref oMissing, ref oMissing, ref oMissing);

#endregion

foreach (Word.Field myMergeField in oWordDoc.Fields)
{

iTotalFields++;
Word.Range rngFieldCode = myMergeField.Code;
String fieldText = rngFieldCode.Text;

if (fieldText.StartsWith(" MERGEFIELD"))
{
// example: MERGEFIELD Name \* MERGEFORMAT
Int32 endMerge = fieldText.IndexOf("\\");
Int32 fieldNameLength = fieldText.Length - endMerge;
String fieldName = fieldText.Substring(11, endMerge - 11); // [Name]

fieldName = fieldName.Trim();

if (fieldName == "Name")
{
myMergeField.Select();
oWord.Selection.TypeText(txt_name.Text);
}

if (fieldName == "Address")
{
myMergeField.Select();
oWord.Selection.TypeText(txt_address.Text);
}

if (fieldName == "Age")
{
myMergeField.Select();
oWord.Selection.TypeText(num_age.Text);
}

if (fieldName == "EAddress")
{
myMergeField.Select();
oWord.Selection.TypeText(txt_email.Text);
}

if (fieldName == "Company")
{
myMergeField.Select();
oWord.Selection.TypeText(txt_company.Text);
}

if (fieldName == "TelNo")
{
myMergeField.Select();
oWord.Selection.TypeText(txt_tel.Text);
}

if (fieldName == "ODetails")
{
myMergeField.Select();
oWord.Selection.TypeText(txt_odetails.Text);
}
}
}

// Open File

}

Structure of the template document

Name: «Name»
Address: «Address»
Age: «Age»
E-mail Address: «EAddress»
Company: «Company»
Telephone Number: «TelNo»
Other Details: «ODetails»

Recursive Search for Folders and Files

public ClsFolderNFileCollection GetCollectionOfRecursiveDirAndFiles(string Src, List Elements)
{
ClsFolderNFileCollection objDirAndFileCollection = new ClsFolderNFileCollection();
string[] Files;

try
{

Files = Directory.GetFileSystemEntries(Src);
foreach (string Element in Files)
{
// Sub directories

if (Directory.Exists(Element))
{
objDirAndFileCollection.FolderCollection.Add(Element);
GetCollectionOfRecursiveDirAndFiles(Element, Elements);
}

else
{
objDirAndFileCollection.FileCollection.Add(Element);
}
}


}
catch (Exception ex)
{
throw new Exception(ex.Message);
}

return objDirAndFileCollection;
}


}

// Return Class

public class ClsFolderNFileCollection
{
public List FileCollection { get; set; }
public List FolderCollection { get; set; }

}

DateTime Stamp Functions

public static string DateTimeIn24Hours()
{
return DateTime.Now.ToString("yyyyMMddHHmm");
}

public static string DateTimeWithUniqueID()
{
string guid = Guid.NewGuid().ToString();

return DateTime.Now.ToString("yyyymmdd") + guid;
}

General Class for FTP Functions

class FTPFunctions
{
///

/// To Make Directory on the FTP-Server
///

/// public static void MakeDir(string dirName)
{
FtpWebRequest reqFTP = null;
Stream ftpStream = null;
try
{

reqFTP = (FtpWebRequest)FtpWebRequest.Create(new Uri("ftp://" + dirName));

reqFTP.Method = WebRequestMethods.Ftp.MakeDirectory;
reqFTP.UseBinary = true;
reqFTP.Credentials = new NetworkCredential(FTPSettings.UserID, FTPSettings.Password);
FtpWebResponse response = (FtpWebResponse)reqFTP.GetResponse();
ftpStream = response.GetResponseStream();
ftpStream.Close();
response.Close();
}
catch (Exception ex)
{
if (ftpStream != null)
{
ftpStream.Close();
ftpStream.Dispose();
}
throw new Exception(ex.Message.ToString());
}
}
///

/// To Check Whether Folder exists or not on the FTP-Server
///

/// /// /// /// ///
public static bool FtpDirectoryExists(string OurDir, string FTPDir, string FTPUser, string FTPPassword)
{
bool IsExists = true;
try
{
FtpWebRequest request;

// Create FtpWebRequest object from the Uri provided
if (!FTPDir.Contains("ftp://"))
{
request = (FtpWebRequest)FtpWebRequest.Create
(new Uri("ftp://" + FTPDir));
}
else
{
request = (FtpWebRequest)FtpWebRequest.Create
(new Uri(FTPDir));
}

// request = (FtpWebRequest)WebRequest.Create(FTPDir);
request.Credentials = new NetworkCredential(FTPUser, FTPPassword);
request.Method = WebRequestMethods.Ftp.PrintWorkingDirectory;

FtpWebResponse response = (FtpWebResponse)request.GetResponse();
}
catch (WebException ex)
{
IsExists = false;
}
return IsExists;
}

///

/// Upload file on the FTP-Server
///

/// /// /// /// ///
public static bool ftpfile(string inputfilepath, string ftpfilepath, string strFTPUser, string strFTPPwd)
{

try
{

FileInfo fileInf = new FileInfo(inputfilepath);

FtpWebRequest ftp;

// Create FtpWebRequest object from the Uri provided
if (!ftpfilepath.Contains("ftp://"))
{
ftp = (FtpWebRequest)FtpWebRequest.Create
(new Uri("ftp://" + ftpfilepath + "/" + fileInf.Name));
}
else
{
ftp = (FtpWebRequest)FtpWebRequest.Create
(new Uri(ftpfilepath + "/" + fileInf.Name));
}

ftp.Credentials = new NetworkCredential(strFTPUser, strFTPPwd);

ftp.KeepAlive = true;
ftp.UseBinary = true;
ftp.Method = WebRequestMethods.Ftp.UploadFile;
FileStream fs = File.OpenRead(inputfilepath);
byte[] buffer = new byte[fs.Length];
fs.Read(buffer, 0, buffer.Length);
fs.Close();
Stream ftpstream = ftp.GetRequestStream();
ftpstream.Write(buffer, 0, buffer.Length);
ftpstream.Close();

return true;

}
catch (Exception ex)
{
// WriteLog("Unable to upload zip file: '"+strFilename+"' - Excep: " + ex.Message);
return false;
}
}

///

/// Get MetaData of the FTP-File
///

/// /// /// /// /// ///
public static FTPFileMetaData GetMetaDataFTPFile(string ftpServerIP, string ftpUserID, string ftpPassword, string strFolderName, string strFileName)
{
FTPFileMetaData metaData = new FTPFileMetaData();
FtpWebRequest reqFTP;


long fileSize = 0;

if (!ftpServerIP.Contains("ftp://"))
{
reqFTP = (FtpWebRequest)FtpWebRequest.Create(new Uri("ftp://" +
ftpServerIP + "/" + strFolderName + "/" + strFileName));
}
else
{
reqFTP = (FtpWebRequest)FtpWebRequest.Create(new Uri(ftpServerIP +
"/" + strFolderName + "/" + strFileName));
}
reqFTP.Method = WebRequestMethods.Ftp.GetDateTimestamp;
reqFTP.UseBinary = true;
reqFTP.KeepAlive = false;
reqFTP.Credentials = new NetworkCredential(ftpUserID,
ftpPassword);
FtpWebResponse response;
try
{
response = (FtpWebResponse)reqFTP.GetResponse();

DateTime lastModified = response.LastModified;

metaData.FileName = strFileName;
metaData.ModifiedTime = lastModified;
Stream ftpStream = response.GetResponseStream();
fileSize = response.ContentLength;
metaData.FileLength = fileSize;
metaData.FileLength = GetFTPFileSize(ftpServerIP, ftpUserID, ftpPassword, strFolderName, strFileName);

return metaData;
}
catch (Exception ex)
{
// BLayer.WriteLog("Error in FTPModule for MetaData " + ex.ToString() + "-" + DateTime.Now);
return metaData;
}
}

///

/// Delete file from the FTP-Server
///

/// /// /// /// /// ///
public static bool DeleteFileFromFTPServer(string ftpServerIP, string ftpUserID, string ftpPassword, string strFolderName, string strFileName)
{



FtpWebRequest request;

try
{

if (!ftpServerIP.Contains("ftp://"))
{
request = (FtpWebRequest)FtpWebRequest.Create(new Uri("ftp://" +
ftpServerIP + "/" + strFolderName + "/" + strFileName));
}
else
{
request = (FtpWebRequest)FtpWebRequest.Create(new Uri(ftpServerIP +
"/" + strFolderName + "/" + strFileName));
}

request.Credentials = new NetworkCredential(ftpUserID,
ftpPassword);
request.Method = WebRequestMethods.Ftp.DeleteFile;

FtpWebResponse response = (FtpWebResponse)request.GetResponse();

response.Close();
return true;
}
catch (Exception ex)
{
return false;
}

}

///

/// Get File-Size
///

/// /// /// /// /// ///
public static long GetFTPFileSize(string ftpServerIP, string ftpUserID, string ftpPassword, string strFolderName, string strFileName)
{
FtpWebRequest reqFTP;
long fileSize = 0;
try
{
reqFTP = (FtpWebRequest)FtpWebRequest.Create(new Uri("ftp://" + ftpServerIP + "/" + strFolderName + "/" + strFileName));
reqFTP.Method = WebRequestMethods.Ftp.GetFileSize;
reqFTP.UseBinary = true;
reqFTP.KeepAlive = false;
reqFTP.Credentials = new NetworkCredential(ftpUserID, ftpPassword);
FtpWebResponse response = (FtpWebResponse)reqFTP.GetResponse();
Stream ftpStream = response.GetResponseStream();
fileSize = response.ContentLength;

ftpStream.Close();
response.Close();
}
catch (Exception ex)
{
// BLayer.WriteLog("Error in FTPModule for FileSize " + ex.ToString() + "-" + DateTime.Now);
}
return fileSize;
}

///

/// Download file from the FTP-Server
///

/// /// /// /// /// /// ///

public static bool DownloadFileFromFTPServer(string ftpServerIP, string ftpUserID, string ftpPassword, string strFolderName, string strFileName, string DestFolder)
{
FtpWebRequest reqFTP;

if (!ftpServerIP.Contains("ftp://"))
{
reqFTP = (FtpWebRequest)FtpWebRequest.Create(new Uri("ftp://" +
ftpServerIP + "/" + strFolderName + "/" + strFileName));


}
else
{
reqFTP = (FtpWebRequest)FtpWebRequest.Create(new Uri(ftpServerIP +
"/" + strFolderName + "/" + strFileName));



}

reqFTP.Method = WebRequestMethods.Ftp.DownloadFile;
reqFTP.UseBinary = true;
reqFTP.KeepAlive = false;
reqFTP.Credentials = new NetworkCredential(ftpUserID,
ftpPassword);
FtpWebResponse response = (FtpWebResponse)reqFTP.GetResponse();
Stream ftpStream = response.GetResponseStream();
long cl = response.ContentLength;
int bufferSize = 2048;
int readCount;
byte[] buffer = new byte[bufferSize];

readCount = ftpStream.Read(buffer, 0, bufferSize);
Directory.CreateDirectory(DestFolder);

FileStream outputStream = new FileStream(DestFolder +
Path.DirectorySeparatorChar + strFileName, FileMode.Create);

try
{

while (readCount > 0)
{
outputStream.Write(buffer, 0, readCount);
readCount = ftpStream.Read(buffer, 0, bufferSize);
}

ftpStream.Close();
outputStream.Close();
response.Close();

return true;
}
catch (Exception ex)
{
string errMsg = "There is problem in downloading file from: " + DestFolder + "\\" + strFileName;
errMsg = errMsg + ": " + ex.Message.ToString();

return false;
}
}

///

/// Get File-listing from the FTP-Server
///

/// /// /// /// ///
public static List GetFTPFileList(string ftpServerIP, string ftpUserID, string ftpPassword, string strFolderName)
{
string[] downloadFiles;
StringBuilder result = new StringBuilder();
FtpWebRequest reqFTP;
List FTPFiles = new List();
try
{
if (!ftpServerIP.Contains("ftp://"))
{
reqFTP = (FtpWebRequest)FtpWebRequest.Create(new Uri("ftp://" +
ftpServerIP + "/" + strFolderName));
}
else
{
reqFTP = (FtpWebRequest)FtpWebRequest.Create(new Uri(ftpServerIP +
"/" + strFolderName));
}
reqFTP.UseBinary = true;
reqFTP.KeepAlive = false;
reqFTP.Credentials = new NetworkCredential(ftpUserID, ftpPassword);
reqFTP.Method = WebRequestMethods.Ftp.ListDirectory;
WebResponse response = reqFTP.GetResponse();
StreamReader reader = new StreamReader(response.GetResponseStream());
//MessageBox.Show(reader.ReadToEnd());
string line = reader.ReadLine();
while (line != null)
{
result.Append(line);
result.Append("\n");
line = reader.ReadLine();
}
result.Remove(result.ToString().LastIndexOf('\n'), 1);
reader.Close();
response.Close();
//MessageBox.Show(response.StatusDescription);
downloadFiles = result.ToString().Split('\n');


foreach (string s in downloadFiles)
{
FTPFileMetaData data = GetMetaDataFTPFile(ftpServerIP, ftpUserID, ftpPassword, strFolderName, s);

// Here we requires only file name

FTPFiles.Add(data.FileName);


}

return FTPFiles;
}
catch (Exception ex)
{

return FTPFiles;
}
}

///

/// Get Folder List from the FTP-Server
///

/// /// /// /// ///
public static List GetDirectoryListingFromFTPServer(string ftpServerIP, string ftpUserID, string ftpPassword, string strFolderName)
{

FtpWebRequest request;

StringBuilder result = new StringBuilder();
List directories = new List();

try
{

if (!ftpServerIP.Contains("ftp://"))
{
request = (FtpWebRequest)FtpWebRequest.Create(new Uri("ftp://" +
ftpServerIP + "/" + strFolderName));
}
else
{
request = (FtpWebRequest)FtpWebRequest.Create(new Uri(ftpServerIP +
"/" + strFolderName));
}


request.Method = WebRequestMethods.Ftp.ListDirectoryDetails;
request.Credentials = new NetworkCredential(ftpUserID,
ftpPassword);


FtpWebResponse response = (FtpWebResponse)
request.GetResponse();

Stream responseStream = response.GetResponseStream();
StreamReader reader = new StreamReader(responseStream);
string line = reader.ReadLine();
while (line != null)
{
// Filtering folder names from the ListingString
if (line.StartsWith("drw-") && !(line.EndsWith(".")))
{
if (line.Contains(":"))
{
result.Append(line.Substring(line.LastIndexOf(":") + 4));
result.Append("\n");
}
}
line = reader.ReadLine();
}


directories.AddRange(result.ToString().Split(new string[] { "\n", "\r\n" }, StringSplitOptions.RemoveEmptyEntries));


reader.Close();
response.Close();

return directories;
}
catch (WebException e)
{
return directories;
}
}

///

/// Check Existence of the File on FTP-Server
///

/// /// /// ///
public bool IsFileExistsFTPServerURI(string fullFtpFilepath, string userName,string passWord)
{

bool exists = true;

var request =

(FtpWebRequest)WebRequest.Create(fullFtpFilepath);

request.Credentials =

new NetworkCredential(userName, passWord);

request.Method =

WebRequestMethods.Ftp.GetDateTimestamp;

try
{

FtpWebResponse response =

(FtpWebResponse)request.GetResponse();

}

catch (WebException ex)
{

FtpWebResponse response = (FtpWebResponse)ex.Response;

if (response.StatusCode ==

FtpStatusCode.ActionNotTakenFileUnavailable)
{

exists = false; //File does not exist

}

}

return exists;

}


}

Data Table Export to Excel with XSLT

Util Class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Xml;
using System.Threading;
using System.Data;
using System.Xml.Xsl;
using System.Web;
using System.Xml.XPath;

namespace Auditor_Apps
{
class DataSetToExcelUtil
{


private string appType;
private HttpResponse response;

private void CreateStylesheet(XmlTextWriter writer, string[] sHeaders, string[] sFileds, ExportFormat FormatType)
{
try
{
string ns = "http://www.w3.org/1999/XSL/Transform";
writer.Formatting = Formatting.Indented;
writer.WriteStartDocument();
writer.WriteStartElement("xsl", "stylesheet", ns);
writer.WriteAttributeString("version", "1.0");
writer.WriteStartElement("xsl:output");
writer.WriteAttributeString("method", "text");
writer.WriteAttributeString("version", "4.0");
writer.WriteEndElement();
writer.WriteStartElement("xsl:template");
writer.WriteAttributeString("match", "/");
for (int i = 0; i < sHeaders.Length; i++) { writer.WriteString("\""); writer.WriteStartElement("xsl:value-of"); writer.WriteAttributeString("select", "'" + sHeaders[i] + "'"); writer.WriteEndElement(); writer.WriteString("\""); if (i != (sFileds.Length - 1)) { writer.WriteString((FormatType == ExportFormat.CSV) ? "," : "\t"); } } writer.WriteStartElement("xsl:for-each"); writer.WriteAttributeString("select", "Export/Values"); writer.WriteString("\r\n"); for (int j = 0; j < sFileds.Length; j++) { writer.WriteString("\""); writer.WriteStartElement("xsl:value-of"); writer.WriteAttributeString("select", sFileds[j]); writer.WriteEndElement(); writer.WriteString("\""); if (j != (sFileds.Length - 1)) { writer.WriteString((FormatType == ExportFormat.CSV) ? "," : "\t"); } } writer.WriteEndElement(); writer.WriteEndElement(); writer.WriteEndElement(); writer.WriteEndDocument(); } catch (Exception exception) { throw exception; } } public enum ExportFormat { CSV = 1, Excel = 2 } public enum AppType { WEB = 1, Winform = 2 } public void ExportDetails(DataTable DetailsTable, ExportFormat FormatType, AppType appType, string FileName) { try { if (DetailsTable.Rows.Count == 0) { throw new Exception("There are no details to export."); } DataSet dsExport = new DataSet("Export"); DataTable table = DetailsTable.Copy(); table.TableName = "Values"; dsExport.Tables.Add(table); string[] sHeaders = new string[table.Columns.Count]; string[] sFileds = new string[table.Columns.Count]; for (int i = 0; i < table.Columns.Count; i++) { sHeaders[i] = table.Columns[i].ColumnName; sFileds[i] = this.ReplaceSpclChars(table.Columns[i].ColumnName); } if (appType == AppType.WEB) { this.Export_with_XSLT_Web(dsExport, sHeaders, sFileds, FormatType, FileName); } else if (appType == AppType.Winform) { this.Export_with_XSLT_Windows(dsExport, sHeaders, sFileds, FormatType, FileName); } } catch (Exception exception) { throw exception; } } private void Export_with_XSLT_Web(DataSet dsExport, string[] sHeaders, string[] sFileds, ExportFormat FormatType, string FileName) { try { this.response.Clear(); this.response.Buffer = true; if (FormatType == ExportFormat.CSV) { this.response.ContentType = "text/csv"; this.response.AppendHeader("content-disposition", "attachment; filename=" + FileName); } else { this.response.ContentType = "application/vnd.ms-excel"; this.response.AppendHeader("content-disposition", "attachment; filename=" + FileName); } MemoryStream w = new MemoryStream(); XmlTextWriter writer = new XmlTextWriter(w, Encoding.UTF8); this.CreateStylesheet(writer, sHeaders, sFileds, FormatType); writer.Flush(); w.Seek(0L, SeekOrigin.Begin); XmlDataDocument document = new XmlDataDocument(dsExport); XslTransform transform = new XslTransform(); transform.Load(new XmlTextReader(w), null, null); StringWriter writer2 = new StringWriter(); transform.Transform((IXPathNavigable)document, null, (TextWriter)writer2, null); this.response.Write(writer2.ToString()); writer2.Close(); writer.Close(); w.Close(); this.response.End(); } catch (ThreadAbortException exception) { string message = exception.Message; } catch (Exception exception2) { throw exception2; } } public void ExportDetails(DataTable DetailsTable, int[] ColumnList, ExportFormat FormatType, AppType appType, string FileName) { try { if (DetailsTable.Rows.Count == 0) { throw new Exception("There are no details to export"); } DataSet dsExport = new DataSet("Export"); DataTable table = DetailsTable.Copy(); table.TableName = "Values"; dsExport.Tables.Add(table); if (ColumnList.Length > table.Columns.Count)
{
throw new Exception("ExportColumn List should not exceed Total Columns");
}
string[] sHeaders = new string[ColumnList.Length];
string[] sFileds = new string[ColumnList.Length];
for (int i = 0; i < ColumnList.Length; i++) { if ((ColumnList[i] < 0) || (ColumnList[i] >= table.Columns.Count))
{
throw new Exception("ExportColumn Number should not exceed Total Columns Range");
}
sHeaders[i] = table.Columns[ColumnList[i]].ColumnName;
sFileds[i] = this.ReplaceSpclChars(table.Columns[ColumnList[i]].ColumnName);
}
if (appType == AppType.WEB)
{
this.Export_with_XSLT_Web(dsExport, sHeaders, sFileds, FormatType, FileName);
}
else if (appType == AppType.Winform)
{
this.Export_with_XSLT_Windows(dsExport, sHeaders, sFileds, FormatType, FileName);
}
}
catch (Exception exception)
{
throw exception;
}
}
private void Export_with_XSLT_Windows(DataSet dsExport, string[] sHeaders, string[] sFileds, ExportFormat FormatType, string FileName)
{
try
{
MemoryStream w = new MemoryStream();
XmlTextWriter writer = new XmlTextWriter(w, Encoding.UTF8);
this.CreateStylesheet(writer, sHeaders, sFileds, FormatType);
writer.Flush();
w.Seek(0L, SeekOrigin.Begin);
XmlDataDocument document = new XmlDataDocument(dsExport);
XslTransform transform = new XslTransform();
transform.Load(new XmlTextReader(w), null, null);
StringWriter writer2 = new StringWriter();
transform.Transform((IXPathNavigable)document, null, (TextWriter)writer2, null);
StreamWriter writer3 = new StreamWriter(FileName);
writer3.WriteLine(writer2.ToString());
writer3.Close();
writer2.Close();
writer.Close();
w.Close();
}
catch (Exception exception)
{
throw exception;
}
}



private string ReplaceSpclChars(string fieldName)
{
fieldName = fieldName.Replace(" ", "_x0020_");
fieldName = fieldName.Replace("%", "_x0025_");
fieldName = fieldName.Replace("#", "_x0023_");
fieldName = fieldName.Replace("&", "_x0026_");
fieldName = fieldName.Replace("/", "_x002F_");
return fieldName;
}



}
}



Implementation code:

DataSetToExcelUtil ObjUtil = new DataSetToExcelUtil();

ObjUtil.ExportDetails(dt, DataSetToExcelUtil.ExportFormat.Excel, DataSetToExcelUtil.AppType.Winform, ExcelPath);

Get ASCII in C#

public static string GetASCIIValue(string strValue)
{
return Encoding.ASCII.GetString(
Encoding.Convert(
Encoding.UTF8,
Encoding.GetEncoding(
Encoding.ASCII.EncodingName,
new EncoderReplacementFallback(string.Empty),
new DecoderExceptionFallback()
),
Encoding.UTF8.GetBytes(strValue)
)
);

}



string s1 = FinancialMIS_Apps.StringManipulation.GetASCIIValue("Expression");
string s2 = FinancialMIS_Apps.StringManipulation.GetASCIIValue("Räksmörgås");

How to write custom formatted excel in C# from dataTable

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Export_Excel = RKLib.ExportData;
using System.IO;
using MyExcel = Microsoft.Office.Interop.Excel;

public class FormattedMISReports
{
public static string ExportDataTableToExcel(DataTable dtSource, string ExcelPath, bool blnFirstSheet, string sheetName, int sheetIndex)
{
MyExcel.Application oXL;
MyExcel.Workbook oWB;
MyExcel.Worksheet oSheet;
MyExcel.Range oRange;

List CustomIndex = new List();
CustomIndex.Add(14);
CustomIndex.Add(40);
CustomIndex.Add(41);
CustomIndex.Add(88);
CustomIndex.Add(89);
CustomIndex.Add(90);
CustomIndex.Add(91);
CustomIndex.Add(96);

List NonHypen = new List();
NonHypen.Add(92);
NonHypen.Add(93);
NonHypen.Add(94);
NonHypen.Add(95);

try
{

// Start Excel and get Application object.
oXL = new MyExcel.Application();

// Set some properties
oXL.DisplayAlerts = false;
oXL.Visible = true;

if (blnFirstSheet)
{
// Get a new workbook.
oWB = oXL.Workbooks.Add(Type.Missing);

// Adding extra sheets in the workbook
oWB.Sheets.Add(Type.Missing, oWB.Worksheets[oWB.Worksheets.Count], 7, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);

// Activating first sheet in the workbook
oSheet = (MyExcel.Worksheet)oWB.Worksheets[sheetIndex];

oSheet.Activate();

oSheet.Name = sheetName;
}
else
{
// opening existing excel file
oWB = oXL.Workbooks.Open(ExcelPath, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", false, false, 0, true, 0, 0);

// Activating first sheet in the workbook
oSheet = (MyExcel.Worksheet)oWB.Worksheets[sheetIndex];

oSheet.Activate();

oSheet.Name = sheetName;
}



// Header Text
oSheet.Cells[1, 1] = "IDS Infotech Ltd.";
oSheet.Cells[2, 1] = "Department: " +sheetName + "";
oSheet.get_Range("A1", "B2").Font.Bold = true;
oSheet.get_Range("A1", "B2").Font.Color = System.Drawing.ColorTranslator.ToWin32(System.Drawing.Color.DarkBlue);



// Process the DataTable into the Excel
DataTable dt = dtSource;

int startIndex = 5;
int rowCount = 5;
foreach (DataRow dr in dt.Rows)
{
rowCount += 1;
for (int i = 1; i < dt.Columns.Count + 1; i++)
{
// Add the header the first time through
if (rowCount == (startIndex+1))
{
oSheet.Cells[startIndex, i] = dt.Columns[i - 1].ColumnName;
}

// coversion for the zero values
if ((Convert.ToString(dr[i - 1]) == "0.00" || Convert.ToString(dr[i - 1]) == string.Empty) && !NonHypen.Contains(rowCount))
oSheet.Cells[rowCount, i] = "-";
else
oSheet.Cells[rowCount, i] = dr[i - 1].ToString();


// Custom Formatting

// Resize the columns
oRange = oSheet.get_Range(oSheet.Cells[1, 1],
oSheet.Cells[rowCount, dt.Columns.Count + 1]); // Additional column for the aggregate-sum
oRange.EntireColumn.AutoFit();

oRange.Borders.LineStyle = MyExcel.XlLineStyle.xlContinuous;
oRange.Borders.Color = System.Drawing.ColorTranslator.ToWin32(System.Drawing.Color.LightSteelBlue);



oSheet.get_Range(oSheet.Cells[startIndex, 1], oSheet.Cells[1, dt.Columns.Count]).Font.Bold = true;
oSheet.get_Range(oSheet.Cells[1, 1], oSheet.Cells[1, dt.Columns.Count]).Font.Italic = true;
oSheet.get_Range(oSheet.Cells[1, 1], oSheet.Cells[1, dt.Columns.Count]).Font.Size = 11;

oSheet.get_Range(oSheet.Cells[rowCount, dt.Columns.Count], oSheet.Cells[rowCount, dt.Columns.Count]).HorizontalAlignment = MyExcel.XlHAlign.xlHAlignCenter;

if (CustomIndex.Contains(rowCount))
{
oSheet.get_Range(oSheet.Cells[rowCount, 1], oSheet.Cells[rowCount, dt.Columns.Count]).Font.Bold = true;
oSheet.get_Range(oSheet.Cells[rowCount, 1], oSheet.Cells[rowCount, dt.Columns.Count]).Font.Italic = true;
oSheet.get_Range(oSheet.Cells[rowCount, 1], oSheet.Cells[rowCount, dt.Columns.Count]).Font.Size = 11;
oSheet.get_Range(oSheet.Cells[rowCount, 1], oSheet.Cells[rowCount, dt.Columns.Count]).Font.Color = System.Drawing.ColorTranslator.ToWin32(System.Drawing.Color.DarkBlue);
oSheet.get_Range(oSheet.Cells[rowCount, dt.Columns.Count], oSheet.Cells[rowCount, dt.Columns.Count]).HorizontalAlignment = MyExcel.XlHAlign.xlHAlignCenter;
oSheet.get_Range(oSheet.Cells[rowCount, 1], oSheet.Cells[rowCount, i]).Borders.LineStyle = MyExcel.XlLineStyle.xlDouble;
}

// End of Custom Formatting

}
}



// array for the excel column heads
string[] array = { "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", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK", "BL", "BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT", "BU", "BV", "BW", "BX", "BY", "BZ" };
int iArrayIndex = 0;

// Applying formulae
for (int i = 2; i < dt.Columns.Count + 2; i++)
{

// Total_Revenue_A
oSheet.get_Range(oSheet.Cells[14, i], oSheet.Cells[14, i]).Formula = "=sum(" + array[iArrayIndex] + "7:" + array[iArrayIndex] + "13)";

// Total_Employ_Cost_B
oSheet.get_Range(oSheet.Cells[40, i], oSheet.Cells[40, i]).Formula = "=sum(" + array[iArrayIndex] + "15:" + array[iArrayIndex] + "39)";

// PerCentage_Employ_Cost_Per_Revenue
oSheet.get_Range(oSheet.Cells[41, i], oSheet.Cells[41, i]).Formula = "=(" + array[iArrayIndex] + "40/" + array[iArrayIndex] + "14)";
oSheet.get_Range(oSheet.Cells[41, i], oSheet.Cells[41, i]).NumberFormat = "##.##%";

// Total_Establis_OtherCost_C
oSheet.get_Range(oSheet.Cells[88, i], oSheet.Cells[88, i]).Formula = "=sum(" + array[iArrayIndex] + "42:" + array[iArrayIndex] + "87)";

// Total_Cost_D_SumOf_B_Plus_C

oSheet.get_Range(oSheet.Cells[89, i], oSheet.Cells[89, i]).Formula = "=(" + array[iArrayIndex] + "88+" + array[iArrayIndex] + "40)";

// Gross_Profit_EBIT
oSheet.get_Range(oSheet.Cells[90, i], oSheet.Cells[90, i]).Formula = "=(" + array[iArrayIndex] + "14-" + array[iArrayIndex] + "89)";

// Gross_Profit_EBIT without CSG cost
oSheet.get_Range(oSheet.Cells[91, i], oSheet.Cells[91, i]).Formula = "=(" + array[iArrayIndex] + "40+" + array[iArrayIndex] + "90+" + array[iArrayIndex] + "88)";

// Netprofit
oSheet.get_Range(oSheet.Cells[96, i], oSheet.Cells[96, i]).Formula = "=(" + array[iArrayIndex] + "14-" + array[iArrayIndex] + "89-" + array[iArrayIndex] + "92-" + array[iArrayIndex] + "93-" + array[iArrayIndex] + "94-" + array[iArrayIndex] + "95)";

if(iArrayIndex < dt.Columns.Count)
iArrayIndex++;
}

// Aggregate Column
for (int k = 7; k <= 97; k++)
{
oSheet.get_Range(oSheet.Cells[k, (dt.Columns.Count + 1)], oSheet.Cells[k, (dt.Columns.Count + 1)]).Formula = "=sum(B" + k + ":"+array[(dt.Columns.Count-2)]+ k + ")";

}

// Set-Header/Column Settings

oSheet.get_Range(oSheet.Cells[5, (dt.Columns.Count + 1)], oSheet.Cells[5, (dt.Columns.Count + 1)]).Value2 = "Total";
oSheet.get_Range(oSheet.Cells[5, (dt.Columns.Count + 1)], oSheet.Cells[97, (dt.Columns.Count + 1)]).Interior.Color = System.Drawing.ColorTranslator.ToWin32(System.Drawing.Color.LightSteelBlue);
oSheet.get_Range(oSheet.Cells[5, (dt.Columns.Count + 1)], oSheet.Cells[97, (dt.Columns.Count + 1)]).Borders.LineStyle = MyExcel.XlLineStyle.xlDouble;
oSheet.get_Range(oSheet.Cells[5, (dt.Columns.Count + 1)], oSheet.Cells[97, (dt.Columns.Count + 1)]).Borders.Color = System.Drawing.ColorTranslator.ToWin32(System.Drawing.Color.LightSteelBlue);
oSheet.get_Range(oSheet.Cells[5, (dt.Columns.Count + 1)], oSheet.Cells[97, (dt.Columns.Count + 1)]).Font.Bold = true;

// Header-Line

oSheet.get_Range(oSheet.Cells[5, 1], oSheet.Cells[5, (dt.Columns.Count + 1)]).Borders.LineStyle = MyExcel.XlLineStyle.xlDouble;


// Report Footer

MyExcel.Range FooterColumn = oSheet.get_Range(oSheet.Cells[100, 1], oSheet.Cells[100, 1]);
FooterColumn.Font.Bold = true;
FooterColumn.Font.Italic = true;
FooterColumn.Font.Color = System.Drawing.ColorTranslator.ToWin32(System.Drawing.Color.DarkBlue);
FooterColumn.Value2 = "Report Generated by MIS_Finance - " + DateTime.Now.ToShortDateString();


// Text-Alignment

oSheet.get_Range(oSheet.Cells[5, 2], oSheet.Cells[96, (dt.Columns.Count + 1)]).HorizontalAlignment = MyExcel.XlHAlign.xlHAlignCenter;


// Save the sheet and close
oSheet = null;
oRange = null;


if (blnFirstSheet)
{
oWB.SaveAs(ExcelPath, MyExcel.XlFileFormat.xlWorkbookNormal,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
MyExcel.XlSaveAsAccessMode.xlExclusive,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
}

else
{
oWB.Save();
}

oWB.Close(Type.Missing, Type.Missing, Type.Missing);
oWB = null;
oXL.Quit();

// No-Errors
return string.Empty;
}
catch (Exception ex)
{
MessageBox.Show("Invalid Operation : \n" + ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
DataLayer.ExecuteExcepLogProcedure(ex.ToString());
return "Error " + ex.Message;
}
finally
{

// Clean up
// NOTE: When in release mode, this does the trick
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();

}
}
}

DES encryption in C#

const string DESKey = "AQWSEDRF";
const string DESIV = "HGFEDCBA";

public static string DESDecrypt(string stringToDecrypt)
{
byte[] key;
byte[] IV;
byte[] inputByteArray;
key = Convert2ByteArray(DESKey);
IV = Convert2ByteArray(DESIV);
int len = stringToDecrypt.Length;
inputByteArray = Convert.FromBase64String(stringToDecrypt);
DESCryptoServiceProvider des = new DESCryptoServiceProvider();
MemoryStream ms = new MemoryStream();
CryptoStream cs = new CryptoStream(ms, des.CreateDecryptor(key, IV), CryptoStreamMode.Write);
try
{
cs.Write(inputByteArray, 0, inputByteArray.Length);
cs.FlushFinalBlock();
Encoding encoding = Encoding.UTF8;
return encoding.GetString(ms.ToArray());
}
catch (System.Exception ex)
{
throw ex;
}
finally
{
ms.Close();
cs.Close();
}
}

public static string DESEncrypt(string stringToEncrypt)
{
byte[] key;
byte[] IV;
byte[] inputByteArray;
key = Convert2ByteArray(DESKey);
IV = Convert2ByteArray(DESIV);
inputByteArray = Encoding.UTF8.GetBytes(stringToEncrypt);
DESCryptoServiceProvider des = new DESCryptoServiceProvider();
MemoryStream ms = new MemoryStream();
CryptoStream cs = new CryptoStream(ms, des.CreateEncryptor(key, IV), CryptoStreamMode.Write);
try
{
cs.Write(inputByteArray, 0, inputByteArray.Length);
cs.FlushFinalBlock();
return Convert.ToBase64String(ms.ToArray());
}
catch (System.Exception ex)
{
throw ex;
}
finally
{
ms.Close();
cs.Close();
}
}

static byte[] Convert2ByteArray(string strInput)
{
int intCounter; char[] arrChar;
arrChar = strInput.ToCharArray();
byte[] arrByte = new byte[arrChar.Length];
for (intCounter = 0; intCounter < arrByte.Length; intCounter++)
arrByte[intCounter] = Convert.ToByte(arrChar[intCounter]);
return arrByte;
}

Zip File or Folder in C#

using ICSharpCode.SharpZipLib.Zip;
using System.IO;

ZipOutputStream zos = null;
protected void Button1_Click(object sender, EventArgs e)
{
string[] pathCollection = new string[2];
PathCellction[0] = "c:\\folder1";
PathCellction[1] = "c:\\folder2";
StartZip(pathCollection, "filename");
}

protected void StartZip(string[] pathCollection, string strFileName)
{
MemoryStream ms;
Response.ContentType = "application/octet-stream";
strFileName = HttpUtility.UrlEncode(strFileName).Replace('+', ' ');
Response.AddHeader("Content-Disposition", "attachment; filename=" + strFileName + ".zip");
ms = new MemoryStream();
zos = new ZipOutputStream(ms);
addZipEntry(pathCollection);
ms.Close();
zos.Finish();
zos.Close();
Response.Clear();
Response.BinaryWrite(ms.ToArray());
Response.End();
}

protected void addZipEntry(string[] pathCollection)
{
for (int i = 0; i < pathCollection.Length; i++)
{
string strPath = pathCollection[i];
addZipEntry(strPath, strPath.LastIndexOf("\\") + 1);
}
}

protected void addZipEntry(string strPath, int baseIndex)
{
DirectoryInfo di = new DirectoryInfo(strPath);
foreach (DirectoryInfo item in di.GetDirectories())
{
addZipEntry(item.FullName, baseIndex);
}
foreach (FileInfo item in di.GetFiles())
{
FileStream fs = File.OpenRead(item.FullName);
byte[] buffer = new byte[fs.Length];
fs.Read(buffer, 0, buffer.Length);
string strEntryName = item.FullName.Remove(0, baseIndex);
ZipEntry entry = new ZipEntry(strEntryName);
zos.PutNextEntry(entry);
zos.Write(buffer, 0, buffer.Length);
fs.Close();
}
}