public JsonResult ReadExcelFile(HttpPostedFileBase file)
{
var fileName = Path.GetFileName(file.FileName);
var path = Path.Combine(Server.MapPath("~/tempfile"), fileName);
file.SaveAs(path);
EPPlus epplus = new EPPlus();
List<ExampleData> sampleDataList = new List<ExampleData>();
sampleDataList = epplus.Read<ExampleData>(path);
System.IO.File.Delete(path);
return Json(sampleDataList,JsonRequestBehavior.AllowGet);
}
public void InitSPList(SPWeb web)
{
string path = SPUtility.GetCurrentGenericSetupPath(@"TEMPLATE\LAYOUTS\SP.Framework.SiteStructure\SPList_Init.xlsx");
Dictionary <string, DataTable> dicTable = EPPlus.ExcelToDataTable(path);
string typeListName = "SYS_LOOKUP_TYPES";
DataTable typeTable = dicTable[typeListName];
SPList typeList = web.Lists.TryGetList(typeListName);
foreach (DataRow dr in typeTable.Rows)
{
SPListItem item = typeList.Items.Add();
item["APP_CODE"] = dr["APP_CODE"];
item["SYSCODE_FLAG"] = dr["SYSCODE_FLAG"];
item["LOOKUP_CODE"] = dr["LOOKUP_CODE"];
item["LOOKUP_NAME"] = dr["LOOKUP_NAME"];
item["REMARK"] = dr["REMARK"];
item["ENABLE"] = dr["ENABLE"];
item.Update();
}
string valueListName = "SYS_LOOKUP_VALUES";
DataTable valueTable = dicTable[valueListName];
SPList valueList = web.Lists.TryGetList(valueListName);
foreach (DataRow dr in valueTable.Rows)
{
SPListItem item = valueList.Items.Add();
string caml = Camlex.Query().Where(x => (string)x["LOOKUP_CODE"] == dr["LOOKUP_CODE"].ToString()).ToString();
SPQuery query = new SPQuery();
query.Query = caml;
var code = SPHelper.GetListItems(web, typeListName, query);
SPFieldLookupValue value = new SPFieldLookupValue(code[0]["ID"].ToString());
item["LOOKUP_CODE_LINE"] = value;
item["LOOKUP_VALUE"] = dr["LOOKUP_VALUE"];
item["LOOKUP_VALUE_NAME"] = dr["LOOKUP_VALUE_NAME"];
item["REMARK"] = dr["REMARK"];
item["ENABLE"] = dr["ENABLE"];
item.Update();
}
}
public string ExportFile(TemplateModel model, DataTable dt, string PageTitle)
{
if (dt == null || dt.Rows.Count == 0)
{
return("");
}
string path = SPUtility.GetCurrentGenericSetupPath(@"TEMPLATE\LAYOUTS\CMICT.CSP.Web\BusinessSearch\");
// 判定该路径是否存在
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
string newFileName = Guid.NewGuid().ToString();
string newFilePath = path + newFileName + ".xlsx";
var columns = ("bs_row_num," + model.SQLBuilder.ColumnNames).Split(',').ToList();
var columnsDisplay = ("序号," + model.SQLBuilder.DisplayNames).Split(',').ToList();
var mergeColumns = ("," + model.SQLBuilder.MergeColumnNames).Split(',').ToList();
for (int i = 0; i < columns.Count; i++)
{
//dt.Columns[columns[i]].ColumnName = columnsDisplay[i];
dt.Columns[columns[i]].SetOrdinal(i);
}
int j = dt.Columns.Count;
while (j > columns.Count)
{
dt.Columns.RemoveAt(columns.Count);
j = dt.Columns.Count;
}
EPPlus.CreateFile(newFilePath, dt, columnsDisplay, mergeColumns, PageTitle, model.TemplateName);
return(newFilePath);
}
protected void lbtnExport_OnClick(object sender, EventArgs e)
{
string entityName = this.hdEntityName.Value;
if (!string.IsNullOrEmpty(entityName))
{
string[] ss = this.hdEntityName.Value.Split('|');
SPSecurity.RunWithElevatedPrivileges(delegate()
{
try
{
string sql = "select * from " + ss[0] + " order by " + ss[1];
SqlHelper sqlHelper = new SqlHelper(ConnString);
DataTable dt = sqlHelper.ExecuteDataTable(sql);
string fileName = ss[0] + DateTime.Now.ToString("yyyyMMddHHss") + ".xlsx";
string configPath = "C:\\";
if (!string.IsNullOrEmpty(ConfigurationManager.AppSettings["ExportFilePath"]))
{
configPath = ConfigurationManager.AppSettings["ExportFilePath"];
}
// 判定该路径是否存在
if (!Directory.Exists(configPath))
{
Directory.CreateDirectory(configPath);
}
string filePath = configPath + fileName;
EPPlus.CreateFile(filePath, dt, new List <string>(), new List <string>(), "", ss[0]);
System.IO.FileStream files = new FileStream(filePath, FileMode.Open, FileAccess.Read,
FileShare.Read);
long filesize = files.Length;
byte[] byteFile = null;
if (files.Length == 0)
{
byteFile = new byte[1];
}
else
{
byteFile = new byte[files.Length];
}
files.Read(byteFile, 0, (int)byteFile.Length);
files.Close();
if (File.Exists(filePath))
{
//删除生成文件
File.Delete(filePath);
}
ScriptManager.RegisterStartupScript(this.Page, this.Page.GetType(), "loadfiletip",
"layer.closeAll();", true);
Page.Response.ContentType = "application/octet-stream";
Page.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
Page.Response.AddHeader("Content-Length", filesize.ToString());
Page.Response.BinaryWrite(byteFile);
Page.Response.Flush();
Page.Response.Close();
//Page.Response.End();
}
catch (Exception ex)
{
BaseComponent.Error(ex.Message);
if (ex.InnerException != null)
{
BaseComponent.Error(ex.InnerException.Message);
}
throw;
}
});
}
ScriptManager.RegisterStartupScript(this.Page, this.Page.GetType(), "loadfiletip",
"layer.closeAll();", true);
}
