Tuesday, November 13, 2012

Excel Import problem IMP-Sample app


        protected void btnExport_Click(object sender, EventArgs e)
        {
            DataTable dataToExport = GetData();
            ExportToExcelFile(dataToExport);
        }

 private DataTable GetData()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("ID", typeof(int));
            dt.Columns.Add("FirstName", typeof(string));
            dt.Columns.Add("LastName", typeof(string));
            //Add Row1
            DataRow dr = dt.NewRow();
            dr["ID"] = 100;
            dr["FirstName"] = "Phaneendra";
            dr["LastName"] = "Bitra";
            dt.Rows.Add(dr);
            //Add Row2
            dr = dt.NewRow();
            dr["ID"] = 101;
            dr["FirstName"] = "Kranthi";
            dr["LastName"] = "Gullapalli";
            dt.Rows.Add(dr);
            //Add Row3
            dr = dt.NewRow();
            dr["ID"] = 102;
            dr["FirstName"] = "Kiran";
            dr["LastName"] = "Gunda";
            dt.Rows.Add(dr);
            //Add Row4
            dr = dt.NewRow();
            dr["ID"] = 103;
            dr["FirstName"] = "Siva";
            dr["LastName"] = "Kuchi";
            dt.Rows.Add(dr);
            return dt;
        }


private void ExportToExcelFile(System.Data.DataTable dt)
        {
            Excel.Application xlsApp = new Excel.ApplicationClass();
            Excel.Workbook xlsWorkbook;
            Excel.Worksheet xlsWorksheet;
            string strhdr;
            int row;
            string strFile = "file1.xls";
            string filename = Server.MapPath(strFile);
            if (dt.Rows.Count > 0)
            {
                //Create new workbook
                xlsWorkbook = xlsApp.Workbooks.Add(true);
                //Get the first worksheet
                xlsWorksheet = (Excel.Worksheet)(xlsWorkbook.Worksheets[1]);
                //Activate current worksheet
                xlsWorksheet.Activate();
                //Set header row to row 1
                row = 1;
                //Add table headers to worksheet
                xlsWorksheet.Cells[row, 1] = "ID";
                xlsWorksheet.Cells[row, 2] = "FirstName";
                xlsWorksheet.Cells[row, 3] = "LastName";
                //Format header row (bold, extra row height, autofit width)
                xlsWorksheet.get_Range("A" + row.ToString(), "C" + row.ToString()).Font.Bold = true;
                xlsWorksheet.get_Range("A" + row.ToString(), "C" + row.ToString()).Rows.RowHeight = 1.5 * xlsWorksheet.StandardHeight;
                xlsWorksheet.get_Range("A" + row.ToString(), "C" + row.ToString()).EntireRow.AutoFit();
                //Freeze the columm headers
                xlsWorksheet.get_Range("A" + (row + 1).ToString(), "C" + (row + 1).ToString()).Select();
                xlsApp.ActiveWindow.FreezePanes = true;
                //Write data to Excel worksheet
                foreach (DataRow dr in dt.Rows)
                {
                    row += 1;
                    if (dr["ID"] != null)
                        xlsWorksheet.Cells[row, 1] = dr["ID"];
                    if (dr["FirstName"] != null)
                        xlsWorksheet.Cells[row, 2] = dr["FirstName"];
                    if (dr["LastName"] != null)
                        xlsWorksheet.Cells[row, 3] = dr["LastName"];
                }
                //Format data rows (align to center and left, autofit width and height)
                xlsWorksheet.get_Range("A2", "C" + row.ToString()).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
                xlsWorksheet.get_Range("A2", "C" + row.ToString()).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
                xlsWorksheet.get_Range("A2", "c" + row.ToString()).EntireColumn.AutoFit();
                xlsWorksheet.get_Range("A2", "c" + row.ToString()).EntireRow.AutoFit();
                xlsWorksheet.get_Range("A:A", Missing.Value).EntireColumn.Hidden = true;
                //Make excel workbook visible to user after all data has been added to worksheet.
                xlsApp.DisplayAlerts = false;
                xlsWorkbook.Close(true, filename, null);
                //Export data to client machine
                strhdr = "attachment;filename=" + strFile;
                Response.Clear();
                Response.ContentType = "application/vnd.ms-excel";
                Response.ContentEncoding = System.Text.Encoding.Default;
                Response.AppendHeader("Content-Disposition", strhdr);
                Response.WriteFile(filename);
                Response.Flush();
                Response.Clear();
                Response.Close();
            }
            xlsApp.Quit();
        }

No comments: