c# 创建excel表头及内容

主要通过ajax调用函数Getinfo

1.定义表dh

DataTable dh = new DataTable();

2.创建表头

public void CreateCol(string ColumnName)
{
DataColumn dc = new DataColumn(ColumnName, Type.GetType("System.String"));
dh.Columns.Add(dc);
}

public void CreateRow(string ColumnName, string RowValue)
{
datarow[ColumnName] = RowValue;
}

3.定义表头

public void CreateCols()
{
CreateCol("文件名");
CreateCol("文件路径");
}

4.创建表的内容

public string GetInfo(string year,string szs,string szx)
{
try
{
CreateCols();//创建表头
dataSource.Open();
DataTable dt = GetFile(year,szs,szx);//获取数据库数据
if (dt != null && dt.Rows.Count > 0)
{
for(int i=0;i<dt.Rows.Count;i++)
{
DataRow dr = dt.Rows[i];
string NODE_NAME = dr["NODE_NAME"].ToString();
string LJ = dr["LJ"].ToString();
CreateRow("文件名",NODE_NAME);//对应表头名字
CreateRow("文件路径",LJ);
dh.Rows.Add(datarow);
dt_print = dh.Copy();//复制表到一个新的表中
clearDt();//清理表dh函数
}
}
string name = String.Empty;
if (this.Radio1.Checked == true) { name = this.Label2.Text; }
if (this.Radio2.Checked == true) { name = this.Label3.Text; }
if (this.Radio3.Checked == true) { name = this.Label6.Text; }
if (this.Radio4.Checked == true) { name = this.Label7.Text; }
System.Guid guid = Guid.NewGuid();
string filepath = System.Web.HttpContext.Current.Server.MapPath("~/") + "Upload\" + guid.ToString() + "文件统计.xlsx";// path + guid.ToString() + "Excel导入规则错误提醒.xls";
ExportExcel("文件统计--"+name, dt_print, filepath);
return "ok";
}
catch (Exception ex)
{
this.method.WriteDataLog("获取项目的信息出错", ex.ToString());
return ex.Message.Trim();
}
finally
{
dataSource.Close();
}
}

5.excel导出函数

public bool ExportExcel(string reportName, DataTable dt, string saveFileName)
{
bool fileSaved = false;
Excel.Range range = null;
Excel.Workbooks workbooks = null;
Excel.Application xlApp = null;
Excel.Workbook workbook = null;
Excel.Worksheet worksheet = null;
try
{
//判断数据集中需要导出的表格数否为空
if (dt == null)
{
return false;
}
//判断是否安装Excel
//xlApp = new Excel.ApplicationClass();
xlApp = new Excel.Application();
if (xlApp == null)
{
return false;
}
workbooks = xlApp.Workbooks;
workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
worksheet.Cells.Font.Size = 10;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;//记录进度
int rowBack = 0;
if (reportName != string.Empty)
{
worksheet.Cells[1, 1] = reportName;
((Excel.Range)worksheet.Cells[1, 1]).Font.Size = 12;//字号
((Excel.Range)worksheet.Cells[1, 1]).Font.Bold = true;//加粗
}
else
{
rowBack = -1;
}

//写入字段
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[2 + rowBack, i + 1] = dt.Columns[i].ColumnName;
range = (Excel.Range)worksheet.Cells[2 + rowBack, i + 1];
range.Interior.ColorIndex = 15;
range.Font.Bold = true;
}
//写入数值
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 3 + rowBack, i + 1] = "'" + dt.Rows[r][i].ToString();
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;//进度百分比
}
//range = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[dt.Rows.Count + 2, dt.Columns.Count]);
range = worksheet.Range[worksheet.Cells[2, 1], worksheet.Cells[dt.Rows.Count + 2, dt.Columns.Count]];
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
if (dt.Rows.Count > 0)
{
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
}
if (dt.Columns.Count > 1)
{
range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
}
//保存文件
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
fileSaved = true;
}
catch
{
fileSaved = false;
}
}
else
{
fileSaved = false;
}

}
catch (Exception ex)
{
method.WriteDataLog("生成excel出错", ex.ToString());
}
finally
{
//释放Excel对应的对象
if (range != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
range = null;
}
if (worksheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
worksheet = null;
}
if (workbook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
}
if (workbooks != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
workbooks = null;
}
xlApp.Application.Workbooks.Close();
xlApp.Quit();
if (xlApp != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
}
GC.Collect();
}
return fileSaved;
}

6.清理表函数

public void clearDt()
{
datarow = null;
datarow = dh.NewRow();
}

7.获取数据库内容函数

public DataTable GetFile(string year, string szs, string szx)
{
DataTable dt = new DataTable();
string sql = string.Empty;
string jq = "substr(node_name,-3)";
string lx = String.Empty;
string lx1 = string.Empty;
string lx2 = string.Empty;
string lx3 = string.Empty;
try
{
sql = "select * from(select cg_guid,node_name,'C:\Zjzs\paltfam\gxcg'||'\'||substr(xs_dm,0,4)||'\'||xs_dm||'\'||node_name as lj,node_type,year,xs_dm from tdzz.T_GDZLNDCG union all select cg_guid,node_name,'C:\Zjzs\paltfam\ndgxcg'||'\'||substr(xs_dm,0,4)||'\'||xs_dm||'\'||node_name,node_type,year,xs_dm from tdzz.T_GDZLNDGXCG union all select cg_guid,node_name,'C:\Zjzs\paltfam\ndgxcg'||'\'||substr(xs_dm,0,4)||'\'||xs_dm||'\'||node_name,node_type,year,xs_dm from tdzz.T_GDZLJCCG) where node_type='1' ";
lx = jq + "='dbf' or " + jq + "='prj' or " + jq + "='sbn' or " + jq + "='sbx' or " + jq + "='shp' or " + jq + "='shx' or " + jq + "='mdb' or " + jq + "='mxd'";
lx1 = jq + "='txt' or " + jq + "='doc' or substr(node_name,-4)='docx' or " + jq + "='pdf'";
lx2 = jq + "='tif' or " + jq + "='jpg' or " + jq + "='jpeg' or " + jq + "='png' or " + jq + "='pdf' ";
lx3 = jq + "='xls' or substr(node_name,-4)='xlsx'";
if (!string.IsNullOrEmpty(year)) { sql += "and year='" + year + "'"; }

if (this.Radio1.Checked == true) { sql += " and ("+ lx+")"; }
if (this.Radio2.Checked == true) { sql += " and ("+lx2+")"; }
if (this.Radio3.Checked == true) { sql += " and ("+lx1+")"; }
if (this.Radio4.Checked == true) { sql += " and ("+lx3+")"; }
if (!string.IsNullOrEmpty(szx)) { sql += " and xs_dm='" + szx + "'"; }
if (!string.IsNullOrEmpty(szs)) { sql += " and substr(xs_dm,0,4)='" + szs + "'"; }
method.WriteDataLog("文件统计测试", sql);
dt = tdzz.GetTableData(sql);
hidcount.Value = dt.Rows.Count.ToString();
}
catch (Exception ex)
{
method.WriteDataLog("获取文件出错", ex.ToString() + "<br/>sql:" + sql);
}
return dt;
}