using System;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using Microsoft.Office.Interop.Excel;
using System.Data;
using System.Web;
namespace NuctechProject.Layouts.Settings
{
public partial class ToExcel : LayoutsPageBase
{
private static readonly string _rootUrl = HttpContext .Current.Request.Url.Scheme + "://" +
HttpContext.Current.Request.Url.Host;
private static readonly string weburl = _rootUrl + "/" ;
private readonly string StrUrl = weburl + "newTemplate/"; //公告站点地址
protected void Page_Load(object sender, EventArgs e)
{
SPSecurity.RunWithElevatedPrivileges(delegate
{
using (var site = new SPSite(StrUrl))
{
using (SPWeb web = site.OpenWeb())
{
//构建DataTable,规则为第一个为任务名,最后一个为层级
var dt = new System.Data.DataTable();
dt.Columns.Add( "里程碑" , typeof( string));
dt.Columns.Add( "责任人" , typeof( string));
dt.Columns.Add( "任务状态" , typeof( string));
dt.Columns.Add( "ceng", typeof (string)); //不用更改,不显示,需放在最后
SPList spList = web.Lists["TastTemp" ];
dt = GetDataTable(0, spList, dt, 1);
DataTabletoExcel(dt, @"C:\Users\Administrator\Desktop\strTemplateFileName.xlsx" );
}
}
;
});
}
public static void DataTabletoExcel(System.Data. DataTable tmpDataTable, string strFileName)
{
if (tmpDataTable == null )
return;
int rowNum = tmpDataTable.Rows.Count;
int columnNum = tmpDataTable.Columns.Count;
int rowIndex = 1;
int columnIndex = 0;
int cengMax = 0;
cengMax = Convert.ToInt32(tmpDataTable.Compute("max(ceng)" , ""));
int cengValue = cengMax - 1;
Microsoft.Office.Interop.Excel. Application xlApp = new Microsoft.Office.Interop.Excel.Application();
xlApp.DefaultFilePath = "";
xlApp.DisplayAlerts = true;
xlApp.SheetsInNewWorkbook = 1;
Workbook xlBook = xlApp.Workbooks.Add(true );
//导入列名
for (int i = 0; i < (columnNum + cengValue); i++)
{
if (i == 1)
{
xlApp.Cells[1, i] = tmpDataTable.Columns[i - 1].ColumnName;
}
if (i <= cengMax && i > 1)
{
xlApp.Cells[1, i] = GetCapital(i - 1) + "级任务" ;
}
if (i > cengMax)
{
xlApp.Cells[1, i] = tmpDataTable.Columns[(i - cengMax)].ColumnName;
}
}
//将DataTable中的数据导入Excel中
for (int i = 0; i < rowNum; i++)
{
rowIndex++;
columnIndex = 0;
for (int j = 0; j < (columnNum - 1); j++)
{
if (j < 1)
{
int ceng = Convert .ToInt16(tmpDataTable.Rows[i][columnNum - 1].ToString());
xlApp.Cells[i + 2, ceng] = tmpDataTable.Rows[i][j].ToString();
}
if (j >= 1)
{
xlApp.Cells[i + 2, j + cengMax] = tmpDataTable.Rows[i][j].ToString();
}
columnIndex++;
}
}
xlApp.get_Range((Microsoft.Office.Interop.Excel. Range)xlApp.Cells[1, 1], (Microsoft.Office.Interop.Excel.Range )xlApp.Cells[1, Convert.ToInt16(columnNum + cengValue - 1)]).Interior.ColorIndex = 16;
xlApp.get_Range((Microsoft.Office.Interop.Excel. Range)xlApp.Cells[1, 1], (Microsoft.Office.Interop.Excel.Range )xlApp.Cells[10000, 100]).EntireColumn.AutoFit();//列宽自适应
xlBook.SaveCopyAs(strFileName);
}
/// <summary>
/// 获取一个任务的子任务
/// </summary>
/// <param name="item"></param>
/// <param name="parentKey"></param>
/// <param name="list"></param>
/// <returns></returns>
public static System.Data.DataTable GetDataTable( int parentId, SPList list, System.Data.DataTable dt, int ceng)
{
SPQuery query = new SPQuery();
query.Query = @"<Where>
<Eq>
<FieldRef Name='PID' />
<Value Type='Number'>" + parentId.ToString() + @"</Value>
</Eq>
</Where>" ;
SPListItemCollection items = list.GetItems(query);
foreach (SPListItem item in items)
{
string taskName = item["TaskName" ].ToString();
string assignedTo = item["AssignedTo" ].ToString().Substring(item["AssignedTo"].ToString().LastIndexOf( "#") + 1);
string status = item["Status" ].ToString();
dt.Rows.Add(taskName, assignedTo, status, ceng.ToString());
dt = GetDataTable(item.ID, list, dt, ceng + 1);
}
return dt;
}
public static string GetCapital( int number)
{
switch (number)
{
case 1:
return "一" ;
case 2:
return "二" ;
case 3:
return "三" ;
case 4:
return "四" ;
case 5:
return "五" ;
}
return "更多" ;
}
}
}