using System;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using Microsoft.Office.Interop.Excel;
using System.IO;
using System.Reflection;
using System.Web;
using System.Collections;
using System.Web.UI.WebControls;
using System.Collections.Generic;
using Microsoft.SharePoint.Administration;
using System.Data.OleDb;
using System.Data;
using System.Linq;
using System.Drawing;
using System.Data.SqlClient;
namespace MyDemo.Layouts.MyDemo
{
public partial class Excel : LayoutsPageBase
{
string rootUrl = Common.rootUrl;//连接地址
string strExcelConn;//Excel使用的Connction
protected void Page_Load(object sender, EventArgs e)
{
}
protected void ButImport_Click(object sender, EventArgs e)
{
if (FileUploadImport.HasFile)
{
string strLoginName = HttpContext.Current.User.Identity.Name.ToString(); //获取用户名
string folderTemp = strLoginName.Substring(strLoginName.LastIndexOf('\\') + 1);
SPSecurity.RunWithElevatedPrivileges(delegate()
{
try
{
string FileException = System.IO.Path.GetExtension(FileUploadImport.FileName).ToLower();//选中文件的类型
//通过判断类型是否为xlsx和xls来判断是否为Excel文件
if (FileException == ".xlsx" || FileException == ".xls")
{
string FileFolder = Server.MapPath("~/_layouts/15/images/" + folderTemp + "Upfile/");//获取文件地址
if (!Directory.Exists(FileFolder)) //根目录
{
Directory.CreateDirectory(FileFolder);//判断上传目录是否存在 自动创建
}
string StrFilepath = FileFolder + FileUploadImport.FileName;//FileUpload文件路径
FileUploadImport.SaveAs(StrFilepath);//将导入文件保存在Web服务器上此路径
using (SPSite site = new SPSite(rootUrl))
{
//获取站点
SPWeb web = site.RootWeb;
SPList list = web.GetList(rootUrl + "/Lists/HomePage/AllItems.aspx");
//允许站点在GET请求上进行更新
web.AllowUnsafeUpdates = true;
//添加列表项
string strExcel = ExcelSheetName(StrFilepath)[0].ToString();//获取Excel表文件名
int ExcelRow = ExcelDataSource(StrFilepath, strExcel).Tables[0].Rows.Count;//获取Excel表中的行数
int ExcelColumn = ExcelDataSource(StrFilepath, strExcel).Tables[0].Columns.Count;//获取Excel表中的列数
try
{
for (int i = 0; i < ExcelRow; i++)
{
SPListItem newItem = list.AddItem();
for (int j = 0; j < ExcelColumn; j++)
{
newItem[ChangeRowName(list, ExcelDataSource(StrFilepath, strExcel).Tables[0].Columns[j].ColumnName)] = ExcelDataSource(StrFilepath, strExcel).Tables[0].Rows[i][j].ToString();
}
newItem.Update();
}
}
catch (Exception ex)
{
Page.ClientScript.RegisterStartupScript(Page.ClientScript.GetType(), "myscript", "<script type='text/javascript'>alert('Excel表列名与系统不符合，请检查Excel表列名！');</script>");
}
}
}
else
{
Page.ClientScript.RegisterStartupScript(Page.ClientScript.GetType(), "myscript", "<script type='text/javascript'>alert('您选择的文件不是Excel格式！');</script>");
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
string strFileFolder = Server.MapPath("~/_layouts/15/images/" + folderTemp + "Upfile/");
if (Directory.Exists(strFileFolder)) //根目录
{
//Directory.CreateDirectory(strFileFolder);//判断上传目录是否存在 自动创建
Directory.Delete(strFileFolder, true);//删除临时文件
}
}
});
}
else
{
Page.ClientScript.RegisterStartupScript(Page.ClientScript.GetType(), "myscript", "<script type='text/javascript'>alert('上传失败！');</script>");
}
}
protected void ButExport_Click(object sender, EventArgs e)
{
string strLoginName = HttpContext.Current.User.Identity.Name.ToString(); //获取用户名
string folderTemp = strLoginName.Substring(strLoginName.LastIndexOf('\\') + 1);
SPSecurity.RunWithElevatedPrivileges(delegate()
{
try
{
string FileName = DateTime.Now.ToString("yyyy.MM.dd HH.mm.ss") + ".xlsx";//以日期定义要导出的文件名
string FileFolder = Server.MapPath("~/_layouts/15/images/" + folderTemp + "Upfile/");
if (!Directory.Exists(FileFolder)) //根目录
{
Directory.CreateDirectory(FileFolder);//判断上传目录是否存在 自动创建
}
string strFilePath = FileFolder + FileName;
System.Data.DataTable dt = new System.Data.DataTable();
using (SPWeb spweb = new SPSite(rootUrl).OpenWeb())
{
SPList spList = spweb.GetList(rootUrl + "/Lists/ProjectList/AllItems.aspx");
dt.Columns.Add("", typeof(string));
dt.Columns.Add("标题", typeof(string));
dt.Columns.Add("项目标题", typeof(string));
dt.Columns.Add("用户名", typeof(string));
SPListItemCollection itemColl = spList.Items;
foreach (SPListItem item in itemColl)
{
string strTitle = item["标题"].ToString();
string strManager = item["项目标题"].ToString();
string strSchedule = item["用户名"].ToString();
dt.Rows.Add("", strTitle, strManager, strSchedule);
}
}
//创建一个Excel应用程序对象，如果未创建成功则推出。
Application excel1 = new Application();
if (excel1 == null)
{
System.Web.HttpContext.Current.Response.Write("无法创建Excel对象，可能你的电脑未装Excel");
}
Workbooks workBooks1 = excel1.Workbooks;
Workbook workBook1 = workBooks1.Add(XlWBATemplate.xlWBATWorksheet);
Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel1.Worksheets[1]; //取得sheet1
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName.ToString();
}
//把DataTable的数据导入到Excel中（这里用的是两个for循环，当表中行，列比较少时 还 可以，但是比较多时，速度就会很慢，我电脑两万五千条用了尽量分钟）
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString();
}
}
try
{
//保存Excel
workBook1.Saved = true;
workBook1.SaveCopyAs(FileFolder+FileName);
}
catch (Exception ex)
{
System.Web.HttpContext.Current.Response.Write("导出文件时出错，文件可能正被打开!\n" + ex.ToString());
}
workBook1.Close();
excel1.Visible = true;
if (excel1 != null)
{
excel1.Workbooks.Close();
excel1.Quit();
int generation = System.GC.GetGeneration(excel1);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel1);
excel1 = null;
System.GC.Collect(generation);
}
}
catch (Exception ex)
{
throw ex;
}
});
}
//连接到Excel
public DataSet ExcelDataSource( string filepath , string sheetname )
{
strExcelConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties='Excel 12.0;HDR=YES'";
OleDbConnection conn = new OleDbConnection(strExcelConn);
OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "]", strExcelConn);
DataSet ds = new DataSet();
oada.Fill(ds);
return ds;
}
//获得Excel中的所有sheetname。
public ArrayList ExcelSheetName( string filepath )
{
strExcelConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties='Excel 12.0;HDR=YES'";
ArrayList al = new ArrayList();
OleDbConnection conn = new OleDbConnection(strExcelConn);
conn.Open();
System.Data.DataTable sheetNames = conn.GetOleDbSchemaTable( System.Data.OleDb.OleDbSchemaGuid.Tables,new object[]{null,null,null,"TABLE"} );
conn.Close();
foreach ( DataRow dr in sheetNames.Rows )
{
al.Add(dr[2]);
}
return al;
}
public string ChangeRowName(SPList splist, string rowName)
{
try
{
return splist.Fields[rowName].InternalName;
}
catch (Exception)
{
return "";
}
}
}
}
