Herkese Selam,
Bir süre önce excell dosyası oluşturan ve bu dosyanın içerisine belirtilen değerleri yazan bir class yazmıştım. Bu class’ı sizinle paylaşmak istedim. Belki bir gün bir yerde ihtiyacınız olabilir.
public class ExcelService
{
private Microsoft.Office.Interop.Excel.Application app;
private Microsoft.Office.Interop.Excel.Workbook workbook;
private Microsoft.Office.Interop.Excel.Worksheet worksheet;
private Microsoft.Office.Interop.Excel.Range range;
public void CreateExcelDocument()
{
CultureInfo oldCulture = Thread.CurrentThread.CurrentCulture;
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
app = new Microsoft.Office.Interop.Excel.Application();
app.Visible = false;
Thread.CurrentThread.CurrentCulture = oldCulture;
}
public void Save(string path)
{
CultureInfo oldCulture = Thread.CurrentThread.CurrentCulture;
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
workbook.SaveAs(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Thread.CurrentThread.CurrentCulture = oldCulture;
}
public void CreateWorkSheet(string workSheetName)
{
CultureInfo oldCulture = Thread.CurrentThread.CurrentCulture;
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
Microsoft.Office.Interop.Excel.Worksheet retVal;
if (workbook == null)
{
workbook = app.Workbooks.Add(true);
retVal = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
retVal.Name = workSheetName;
Microsoft.Office.Interop.Excel.Worksheet currentWorkSheet;
for (int i = 1; i <= workbook.Worksheets.Count; i++)
{
currentWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[i];
if (currentWorkSheet.Name.Equals("Sheet1"))
{
currentWorkSheet.Delete();
}
}
}
else
{
retVal = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing);
retVal.Name = workSheetName;
}
Thread.CurrentThread.CurrentCulture = oldCulture;
}
public void WriteData(int row, int col, string data, string stringFormat, string workSheetName)
{
CultureInfo oldCulture = Thread.CurrentThread.CurrentCulture;
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
for (int i = 1; i <= workbook.Worksheets.Count; i++)
{
Microsoft.Office.Interop.Excel.Worksheet retVal =
(Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[i];
if (retVal.Name.Equals(workSheetName))
{
if (!(string.IsNullOrEmpty(stringFormat)))
{
range = retVal.get_Range(retVal.Cells[row, col], retVal.Cells[row, col]);
range.Value2 =data.Replace(",", ".");
range.NumberFormat = stringFormat;
}
else
{
range = retVal.get_Range(retVal.Cells[row, col], retVal.Cells[row, col]);
range.Value2 = data;
}
}
}
Thread.CurrentThread.CurrentCulture = oldCulture;
}
public void MergeColumns(int row, int col, int row2, int col2, int mergeColumns, string workSheetName)
{
CultureInfo oldCulture = Thread.CurrentThread.CurrentCulture;
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
for (int i = 1; i <= workbook.Worksheets.Count; i++)
{
Microsoft.Office.Interop.Excel.Worksheet retVal =
(Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[i];
if (retVal.Name.Equals(workSheetName))
{
range = retVal.get_Range(retVal.Cells[row, col], retVal.Cells[row2, col2]);
range.Merge(mergeColumns);
}
}
Thread.CurrentThread.CurrentCulture = oldCulture;
}
public void EditCell(int row, int col, string color, bool isBold, int size, string workSheetName)
{
CultureInfo oldCulture = Thread.CurrentThread.CurrentCulture;
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
for (int i = 1; i <= workbook.Worksheets.Count; i++)
{
Microsoft.Office.Interop.Excel.Worksheet retVal =
(Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[i];
if (retVal.Name.Equals(workSheetName))
{
range = retVal.get_Range(retVal.Cells[row, col], retVal.Cells[row, col]);
switch (color)
{
case "ORANGE":
range.Interior.Color =
System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Orange);
break;
case "KHAKI":
range.Interior.Color =
System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Khaki);
break;
case "BURLYWOOD":
range.Interior.Color =
System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.BurlyWood);
break;
case "SKYBLUE":
range.Interior.Color =
System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.SkyBlue);
break;
case "GAINSBORO":
range.Interior.Color =
System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gainsboro);
break;
case "WHITESMOKE":
range.Interior.Color =
System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.WhiteSmoke);
break;
default:
// workSheet_range.Interior.Color = System.Drawing.Color..ToArgb();
break;
}
range.Font.Bold = isBold;
range.ColumnWidth = size;
}
}
Thread.CurrentThread.CurrentCulture = oldCulture;
}
internal void Dispose()
{
if (app != null)
{
app.Quit();
}
app = null;
workbook = null;
worksheet = null;
range = null;
}
}
Kullanmadan önce projemize interop excell referansını vermeniz gerekir.
Bu işlemi yaptıktan sonra gerekli using ifadelerini vermemiz gerekir. Bunlar;
using System.Globalization;
using System.Threading;
Bu işlemlerden sonra sınıfımız kullanmaya hazırdır. Kullanımı için basit bir örnek verecek olursam.
excellApp.CreateExcelDocument();
excellApp.CreateWorkSheet("Deneme Work Sheet");
excellApp.WriteData(1, 1, "Emrah", "", "Deneme Work Sheet");
excellApp.MergeColumns(1, 1, 1, 2, 2, "Deneme Work Sheet");
excellApp.EditCell(1, 1, "ORANGE", true, 7, "Deneme Work Sheet");
excellApp.Save(@"C:\deneme");
Bu kod yardımıyla C: \ dizini altında deneme excell dosyası oluşur. Sonucunda deneme dosyasının içerisinde şunu görürsünüz.
Bu kadar basit umarım işinize yarar.
Kolay gelsin.
