// Its excel write try this also add ur solution
using System;
using System.Collections.Generic;
using System.Diagnostics.CodeAnalysis;
using System.IO;
using System.Text;
/// <summary>
/// Produces Excel file without using Excel
/// </summary>
[SuppressMessage("Microsoft.StyleCop.CSharp.ReadabilityRules", "SA1101:PrefixLocalCallsWithThis",
Justification = "this is not required")]
[SuppressMessage("Microsoft.StyleCop.CSharp.DocumentationRules", "SA1600:ElementsMustBeDocumented",
Justification = "Code is not documented")]
public sealed class ExcelWriter : IDisposable
{
// private Stream stream;
private readonly BinaryWriter writer;
private readonly ushort[] begin = { 0x0809, 8, 0, 0x10, 0, 0 };
private readonly ushort[] end = { 0x0A, 00 };
/// <summary>
/// Initializes a new instance of the <see cref="ExcelWriter"/> class.
/// </summary>
/// <param name="stream">The stream.</param>
public ExcelWriter(Stream stream)
{
// this.stream = stream;
writer = new BinaryWriter(stream);
}
/// <summary>
/// Writes the text cell value.
/// </summary>
/// <param name="row">The row. no</param>
/// <param name="col">The col. no</param>
/// <param name="value">The string value.</param>
public void WriteCell(int row, int col, string value)
{
ushort[] data = { 0x0204, 0, 0, 0, 0, 0 };
var len = value.Length;
var plainText = Encoding.ASCII.GetBytes(value);
data[1] = (ushort)(8 + len);
data[2] = (ushort)row;
data[3] = (ushort)col;
data[5] = (ushort)len;
WriteUshortArray(data);
writer.Write(plainText);
}
/// <summary>
/// Writes the integer cell value.
/// </summary>
/// <param name="row">The row number.</param>
/// <param name="col">The column number.</param>
/// <param name="value">The value.</param>
public void WriteCell(int row, int col, int value)
{
ushort[] data = { 0x027E, 10, 0, 0, 0 };
data[2] = (ushort)row;
data[3] = (ushort)col;
WriteUshortArray(data);
var buffer = (value << 2) | 2;
writer.Write(buffer);
}
/// <summary>
/// Writes the double cell value.
/// </summary>
/// <param name="row">The row number.</param>
/// <param name="col">The column number.</param>
/// <param name="value">The value.</param>
public void WriteCell(int row, int col, double value)
{
ushort[] data = { 0x0203, 14, 0, 0, 0 };
data[2] = (ushort)row;
data[3] = (ushort)col;
WriteUshortArray(data);
writer.Write(value);
}
/*
/// <summary>
/// Writes the empty cell.
/// </summary>
/// <param name="row">The row number.</param>
/// <param name="col">The column number.</param>
public void WriteCell(int row, int col)
{
ushort[] clData = { 0x0201, 6, 0, 0, 0x17 };
clData[2] = (ushort)row;
clData[3] = (ushort)col;
WriteUshortArray(clData);
}
*/
/// <summary>
/// Must be called once for creating XLS file header
/// </summary>
public void BeginWrite()
{
WriteUshortArray(begin);
}
/// <summary>
/// Ends the writing operation, but do not close the stream
/// </summary>
public void EndWrite()
{
WriteUshortArray(end);
writer.Flush();
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
private void Dispose(bool disposing)
{
if (disposing)
{
// dispose managed resources
writer.Close();
}
}
private void WriteUshortArray(IList<ushort> value)
{
for (var i = 0; i < value.Count; i++)
{
writer.Write(value[i]);
}
}
}
}
private static void WriteExcel(DataTable dataTableToBeConverted, string excelFileName)
{
var stream = new FileStream(excelFileName, FileMode.OpenOrCreate);
var writer = new ExcelWriter(stream);
writer.BeginWrite();
// Write Header
for (var i = 0; i < dataTableToBeConverted.Columns.Count; i++)
{
writer.WriteCell(0, i, dataTableToBeConverted.Columns[i].ColumnName);
}
// Write Lines
for (var rows = 0; rows < dataTableToBeConverted.Rows.Count; rows++)
{
for (var columns = 0; columns < dataTableToBeConverted.Columns.Count; columns++)
{
DataRow dr = dataTableToBeConverted.Rows[rows];
if (dr.IsNull(columns) == true)
{
writer.WriteCell(rows + 1, columns, "");
}
else
{
switch (dataTableToBeConverted.Columns[columns].DataType.Name)
{
case "String":
writer.WriteCell(rows + 1, columns, Convert.ToString(dr[columns], CultureInfo.InvariantCulture));
break;
case "Decimal":
case "Double":
writer.WriteCell(rows + 1, columns, Convert.ToDouble(dr[columns], CultureInfo.InvariantCulture));
break;
case "Int32":
writer.WriteCell(rows + 1, columns, Convert.ToInt32(dr[columns], CultureInfo.InvariantCulture));
break;
case "DateTime":
writer.WriteCell(rows + 1, columns, Convert.ToString(dr[columns], CultureInfo.InvariantCulture));
break;
}
}
}
}
writer.EndWrite();
stream.Close();
}
----
Try This i also implement This .