1.asp.net DataTable 导出 Excel
public void ExportExcel()
{
String str = WebConfigurationManager.ConnectionStrings["DB_Zqdn"].ToString();
SqlConnection conn = new SqlConnection();
string sql = @"select a.ID 用户ID ,a.Mobile 用户手机号,a.Name 姓名,a.Province 省份,
a.City 城市,a.Area 区域
from users as a
left join
(select b.CreateUserID,sum(case when status=1 then 1 else 0 end) SH ,COUNT(*) as total
from CaseTpls as b
group by CreateUserID) as b
on a.ID = b.CreateUserID";
conn.ConnectionString = str;
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Open();
}
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable table = new DataTable();
adapter.Fill(table);
CreateExcel(table, "application/ms-excel","用户数据");
}
public void CreateExcel(DataTable dt, string FileType, string FileName)
{
Response.Clear();
Response.Charset = "UTF-8";
Response.Buffer = true;
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
Response.AppendHeader("Content-Disposition", "attachment;filename=\"" + System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + ".xls\"");
Response.ContentType = FileType;
string colHeaders = string.Empty;
string ls_item = string.Empty;
DataRow[] myRow = dt.Select();
int i = 0;
int cl = dt.Columns.Count;
for (int key = 0; key < cl; key++)
{
Response.Output.Write(dt.Columns[key]+"\t");
}
Response.Output.Write("\n");
foreach (DataRow row in myRow)
{
for (i = 0; i < cl; i++)
{
if (i == (cl - 1))
{
ls_item += row[i].ToString().Replace("\t", "").Replace("\n", "") + "\n";
}
else
{
ls_item += row[i].ToString().Replace("\t", "").Replace("\n", "") + "\t";
}
}
Response.Output.Write(ls_item);
ls_item = string.Empty;
}
Response.Output.Flush();
Response.End();
}
2.VScode 匹配以某几个字符开始和逗号结束的中间字符串
"KP"[^,]*,
