如何使用excel模板和OLE DB将数据表导出到Excel，而不使用C＃web API中的任何第三个库 [英] How to export datatable to excel using excel template and OLE DB without any third library in C# web API
本文介绍了如何使用excel模板和OLE DB将数据表导出到Excel，而不使用C＃web API中的任何第三个库的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
您好，
我想要使用c＃web api导出excel，我的代码仅在我手动创建文件夹中的一个Excel模板文件时导出一次，但我的for循环没有第二次出口。
我们如何解决这个问题。我想使用现有的excel文件，比如模板。
我们不能使用任何第三方库。
我想创建zip所有提取文件。
请帮帮我。
谢谢提前。
Ankit Agarwal
软件工程师
什么我试过了：
这是导出到Excel代码： - （我试过内存流但我不知道如何处理内存流） 。
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;
}
主要功能： - （ABC和XYZ是我的表名，我创建了一个存储过程来运行两个表）
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);
}
推荐答案
;
cmd = new OleDbCommand（query，cnn）;
cmd.ExecuteNonQuery（）;
//使用我们的要求创建新工作表
query =CREATE 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））
查询+ =INT，;
其他
query + =VARCHAR（255），;
}
cmd = new OleDbCommand（query，cnn）;
cmd.ExecuteNonQuery（）;
//插入数据
foreach（table.Rows中的DataRow行）
{
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（插入[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}，值）;
cmd = new OleDbCommand（query，cnn）;
// cnn.Open（）;
cmd.ExecuteNonQuery（）;
//cnn.Close（）;
}
}
}
catch（Exception ex）
{
context.Response.Write（ex.Message）;
返回;
}
最后
{
// 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）;
}
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;
}
主要功能： - （ABC和XYZ是我的表名，我创建了一个存储过程来运行两个表）
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);
}
这篇关于如何使用excel模板和OLE DB将数据表导出到Excel，而不使用C＃web API中的任何第三个库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
