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();
}
}