How to export datatable to excel using excel template and OLE DB without any third library in C# web API

See more:

C#

MVC

web

API

Hello,

I want to need exporting excel using c# web api, my code exporting one time only when i created manually one excel template file in a folder, but my for loop not exporting second time.

How can we resolve this issue. I want to use existing excel file like only template.

We can't use any third party library.

I want to create zip all extract files.

Please help me.

Thanks in Advance.

Ankit Agarwal
Software Engineer

What I have tried:

This is Export to Excel code:- (I tried memory stream but I don't know how to handle with memory stream).

public void ExportToExcel(DataTable table, string tempfileName)
        {
            HttpContext context = HttpContext.Current;
            context.Response.Clear();
            
            string query;
            OleDbCommand cmd;
            
            try
            {
                string cnStr = GetConnectionString(tempfileName, Types.Excel_2007_XML_xlsx, true, true);

                using (OleDbConnection cnn = new OleDbConnection(cnStr))
                { 
                    cnn.Open();

                //Drop the existing sheet(first Sheet)
                query = "DROP TABLE [Sheet1$]";
                cmd = new OleDbCommand(query, cnn);
                cmd.ExecuteNonQuery();

                //Create new sheet with our requirements
                query = "CREATE TABLE [Sheet1$] (";
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    query += table.Columns[i].ColumnName;
                    if (i + 1 == table.Columns.Count)
                        if (table.Columns[i].DataType == System.Type.GetType("System.Int32"))
                            query += " INT)";
                        else
                            query += " VARCHAR(255))";
                    else
                        if (table.Columns[i].DataType == System.Type.GetType("System.Int32"))
                        query += " INT,";
                    else
                        query += " VARCHAR(255),";
                }
                cmd = new OleDbCommand(query, cnn);
                cmd.ExecuteNonQuery();

                    //Insert Data
                    foreach (DataRow row in table.Rows)
                    {
                        string values = "(";
                        for (int i = 0; i < table.Columns.Count; i++)
                        {
                            if (i + 1 == table.Columns.Count)
                            {
                                if (table.Columns[i].DataType == System.Type.GetType("System.Int32"))
                                    values += String.IsNullOrEmpty(row[i].ToString()) ? "0)" : row[i] + ")";
                                else
                                    values += "'" + System.Security.SecurityElement.Escape(row[i].ToString()) + "')";
                            }
                            else
                            {
                                if (table.Columns[i].DataType == System.Type.GetType("System.Int32"))
                                    values += String.IsNullOrEmpty(row[i].ToString()) ? "0," : row[i] + ",";
                                else
                                    values += "'" + System.Security.SecurityElement.Escape(row[i].ToString()) + "',";
                            }
                        }
                        query = String.Format("Insert into [Sheet1$] VALUES {0}", values);
                        cmd = new OleDbCommand(query, cnn);
                       // cnn.Open();
                        cmd.ExecuteNonQuery();
                        //cnn.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                context.Response.Write(ex.Message);
                return;
            }
            finally
            {
                //cmd = null;
                //if (cnn != null)
                //cnn.Close();
            }

            context.Response.ContentType = "application/ms-excel";
            context.Response.AppendHeader("Content-Disposition", "attachment;filename="+tempfileName);
            context.Response.WriteFile(tempfileName);
            //MemoryStream ms = new MemoryStream();
            //ms.Flush();
            //context.Response.BinaryWrite(ms.ToArray());

            //ms.WriteTo(context.Response.OutputStream);
        }

Connection string Method:-

private static string GetConnectionString(string fileName, string Type, bool isHeaderExists, bool TreatIntermixedAsText)
        {
            string cnnStr;
            string provider;

            if (Type == "Excel 5.0" || Type == "Excel 8.0")
                provider = "Microsoft.Jet.OLEDB.4.0";
            else
                provider = "Microsoft.ACE.OLEDB.12.0";

            cnnStr = "Provider=" + provider +
                        ";Data Source=" + fileName +
                        ";Extended Properties=\"" + Type +
                                               ";HDR=" + (isHeaderExists ? "Yes;\"" : "No;\"");

            return cnnStr;
        }

Main Function:- (ABC and XYZ are my table names and i have created One Stored procedure for run both tables)

[HttpGet]
        public IHttpActionResult GetExtracts(DateTime StartDate, DateTime EndDate)
        {
            //DateTime StartDate = new DateTime();
            //DateTime EndDate = new DateTime();
            
            string ExtractNameList = "ABC" + "," + "XYZ";
            List<string> ExtractNames = ExtractNameList.Split(',').ToList<string>();
            foreach (var ExtractName in ExtractNames)
            {
                string strQuery = "Exec SpExtracts @StartDate,@EndDate,@ExtractName";
                SqlCommand cmd = new SqlCommand(strQuery);
                cmd.Parameters.AddWithValue("@StartDate", StartDate);
                cmd.Parameters.AddWithValue("@EndDate", EndDate);
                cmd.Parameters.AddWithValue("@ExtractName", string.IsNullOrEmpty(ExtractName) ? (object)System.DBNull.Value : (object)ExtractName);
                DataTable dt = GetData(cmd);
                newfileName= "S15_" + ExtractName + "_" + StartDate + "_" + EndDate + ".xlsx";
                newfileName = newfileName.Replace('/', '-');
                newfileName = newfileName.Replace(" ", "");
                newfileName = newfileName.Replace(":", "-");
                newfile = @"C:\tempFolder\Extracts\" + newfileName;
               
                string fileName = @"C:\tempFolder\Extracts\ExportTemplate.xlsx";
                ExportToExcel(dt, fileName);
                File.Copy(fileName, newfile,true);
                path = @"C:\tempFolder\Extracts\";

            }

            //String[] allfiles = System.IO.Directory.GetFiles(path, "*.xlsx*", System.IO.SearchOption.AllDirectories);
            //var result = new List<string>();
            //string[] extensions = { ".xlsx", ".xls" };
            //foreach (var files in Directory.EnumerateFiles(path, "*.xlsx*", SearchOption.AllDirectories).Where(s => extensions.Any(ext => ext == Path.GetExtension(s))))
            //    result.Add(files);
            //ZipHelper.ZipFiles(path, result, CompressionOption.Normal);
            return Ok(newfileName);

        }