I want to write a excel file (xls/xlsx) in C#.
Constraints:
I don't want to use following things while writing:
1. Third Party dll( My client is not allowing me to use third party dll )
2. SDK(Same as above reason as am not allowed to download)
3. Not allowed to use interop as in target server there is no office installed.
4. OpenXml
Info:
1. Using VS 2005.
2. Using ASP.Net (C# 2.0)
Write a csv file which can be opened directly by Excel.
I think this will help you
[DllImport("user32.dll")]
private static extern uint GetWindowThreadProcessId(IntPtr hWnd, out uint lpdwProcessId);
public DataTable readexcelV1(string path, bool isHeader, params string[] sheetname)
{
bool isFirstTime = true;
string filePath = path;
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
ExcelApp.Visible = false;
Microsoft.Office.Interop.Excel.Workbook wb = ExcelApp.Workbooks.Open(filePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
string ActiveSheetName = "";
if (sheetname.Count() == 0)
ActiveSheetName = ((dynamic)wb).ActiveSheet.Name;
else
ActiveSheetName = Convert.ToString(sheetname[0]);
Microsoft.Office.Interop.Excel.Worksheet sh = (Microsoft.Office.Interop.Excel.Worksheet)wb.Sheets[ActiveSheetName];
//////////////////////////
excellt.Range excelRange = sh.UsedRange;
Int32 Userows = excelRange.Rows.Count;
Int32 Usecolumns = excelRange.Columns.Count;
int Pointrow = 1;
int expVar = 75000;
int Pointcolumn = expVar;
int co = 0;
int r = 0;
expVar = Userows > expVar ? expVar : Userows;
decimal lcount = Convert.ToDecimal(Userows) / Convert.ToDecimal(expVar);
Int32 lcountint = Convert.ToInt32(Math.Ceiling(lcount));
DataTable dt = new DataTable();
if (isHeader == true)
{
int duplicateColum = 1;
for (int j = 1; j <= excelRange.Columns.Count; j++) // Header Names
{
if (excelRange.Cells[1, j].Value2 != null)
{
if (!dt.Columns.Contains(Convert.ToString(excelRange.Cells[1, j].Value2).Trim()))
{
dt.Columns.Add(Convert.ToString(excelRange.Cells[1, j].Value2).Trim());
}
else
{
dt.Columns.Add(Convert.ToString(excelRange.Cells[1, j].Value2).Trim() + duplicateColum.ToString());
duplicateColum++;
}
}
else
{
dt.Columns.Add("Column" + j.ToString());
duplicateColum++;
}
}
}
else
{
for (int j = 1; j <= excelRange.Columns.Count; j++) // Header Names
dt.Columns.Add("Column" + j.ToString());
}
string cell = string.Empty;
string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
int colCharsetLen = colCharset.Length;
if (dt.Columns.Count > colCharsetLen)
{
cell = colCharset.Substring(
(dt.Columns.Count - 1) / colCharsetLen - 1, 1);
}
cell += colCharset.Substring(
(dt.Columns.Count - 1) % colCharsetLen, 1);
for (int i = 0; i < lcountint; i++)
{
string cell1 = "A" + Convert.ToString(Pointrow);
string cell2 = cell + Convert.ToString(Pointcolumn);
excellt.Range rng = sh.get_Range(cell1, cell2);
object[,] x = (object[,])rng.get_Value(excellt.XlRangeValueDataType.xlRangeValueDefault);
int NumRow = isHeader == true && isFirstTime == true ? 2 : 1;
int loopUpto = Userows > expVar ? (Pointcolumn - Pointrow) : expVar;
loopUpto += 2;
isFirstTime = false;
while (NumRow < loopUpto)
{
dt.Rows.Add();
co = 0;
for (int c = 1; c <= Usecolumns; c++)
{
dt.Rows[r][co] = Convert.ToString(x[NumRow, c]);
co++;
}
NumRow++;
r++;
}
Pointrow += expVar;
Pointcolumn += expVar;
Pointcolumn = (Pointcolumn >= Userows) ? Userows : Pointcolumn;
progressBar1.Value += 10;
}
GC.Collect();
GC.WaitForPendingFinalizers();
if (ExcelApp != null)
{
ExcelApp.Quit();
int hWnd = ExcelApp.Application.Hwnd;
uint processID; GetWindowThreadProcessId((IntPtr)hWnd, out processID);
Process[] procs = Process.GetProcessesByName("EXCEL");
foreach (Process p in procs)
{
if (p.Id == processID)
p.Kill();
}
Marshal.FinalReleaseComObject(ExcelApp);
}
return dt;
}
I hope client is paying by the hour because these are stupid solution constraints (unless there are extenuating circumstances)
However, the XML format for an Excel spreadsheet is actually not that bad to write code for, The newer version of EXcel will open an .xml file and treat it as a spreadsheet. You can embed formatting, headers, etc. in the .xml format and since XML is structured, it is far more maintainable than writing lots of bytes of data via code.
Use excel, create an document in the right format, use that as a template, and tweek till it works as needed. Make sure you have a decent XML editor and lots of patience.
I think a Text-Tab Delimited will be much simpler and faster to write than attempting a CSV generator. CSV's can sometimes have weird quirks if you aren't careful during your design phase and carefully examine all expected variations of your data. CSV's will require text qualifiers etc. Unless you're expecting your data to have TAB's all over the place, a TAB-Delimited file is pretty easy to do.
