1. 创建DataTable
2. 转为Excel并输出
using System;
using System.Data;
using System.Text;
namespace 进阶学习_DataTable转Excel
{
class Program
{
static void Main(string[] args)
{
//创建一个名为"Table_New"的空表
DataTable dt = new DataTable("Table_New");
创建空列
//DataColumn dc = new DataColumn();
通过列架构添加列
//dt.Columns.Add(dc);
//创建带列名和类型名的列
dt.Columns.Add("编号", typeof(String));
//创建带列名和类型名的列
dt.Columns.Add("号码", typeof(String));
for (int i = 0; i < 10; i++)
{
//通过行框架添加数据并赋值
dt.Rows.Add(String.Format("编号:{0}", i), i*10086);//Add里面参数的数据顺序要和dt中的列的顺序对应
}
//新建行的赋值
//DataRow dr = dt.NewRow();
//dr[0] = "张三";//通过索引赋值
//dr["column1"] = DateTime.Now; //通过名称赋值
// //对表已有行进行赋值
//dt.Rows[0][0] = "张三"; //通过索引赋值 (行, 列)
//dt.Rows[0]["column1"] = DateTime.Now;//通过名称赋值 (行, 列名)
//取值
//string name = dt.Rows[0][0].ToString(); //通过索引赋值 (行, 列)
//string time = dt.Rows[0]["column1"].ToString(); //通过名称赋值 (行, 列名)
DataToExcel(dt, "你好");
}
/// <summary>
/// Datatable生成Excel表格并返回路径
/// </summary>
/// <param name="m_DataTable">Datatable</param>
/// <param name="s_FileName">文件名</param>
/// <returns></returns>
public static string DataToExcel(System.Data.DataTable m_DataTable, string s_FileName)
{
string FileName = @"C:\Users\JK\Desktop\DataTableToExcel\" + s_FileName + ".xlsx"; //文件存放路径
if (System.IO.File.Exists(FileName)) //存在则删除
{
System.IO.File.Delete(FileName);
}
System.IO.FileStream objFileStream;
System.IO.StreamWriter objStreamWriter;
string strLine = "";
objFileStream = new System.IO.FileStream(FileName, System.IO.FileMode.OpenOrCreate, System.IO.FileAccess.Write);
objStreamWriter = new System.IO.StreamWriter(objFileStream, Encoding.Unicode);
for (int i = 0; i < m_DataTable.Columns.Count; i++)
{
strLine = strLine + m_DataTable.Columns[i].Caption.ToString() + Convert.ToChar(9); //写列标题
}
objStreamWriter.WriteLine(strLine);
strLine = "";
for (int i = 0; i < m_DataTable.Rows.Count; i++)
{
for (int j = 0; j < m_DataTable.Columns.Count; j++)
{
if (m_DataTable.Rows[i].ItemArray[j] == null)
strLine = strLine + " " + Convert.ToChar(9); //写内容
else
{
string rowstr = "";
rowstr = m_DataTable.Rows[i].ItemArray[j].ToString();
if (rowstr.IndexOf("\r\n") > 0)
rowstr = rowstr.Replace("\r\n", " ");
if (rowstr.IndexOf("\t") > 0)
rowstr = rowstr.Replace("\t", " ");
strLine = strLine + rowstr + Convert.ToChar(9);
}
}
objStreamWriter.WriteLine(strLine);
strLine = "";
}
objStreamWriter.Close();
objFileStream.Close();
return FileName; //返回生成文件的绝对路径
}
}
}
效果: