private void btnExport_Click(object sender, EventArgs e)
{
////Export to Excel as of now
if (listOfGatheredTweets.Count > 0)
{
var dlg = new SaveFileDialog();
dlg.Filter = "Excel Files|*.xlsx;";
dlg.ShowDialog();
if (!string.IsNullOrEmpty(dlg.FileName))
{
DataSet dsTweetContainer = new DataSet();
tweetsTextBox.AppendText("\r\n Exporting your data to excel. Please wait...");
dsTweetContainer.Tables.Add(Utility.ConvertToDataTable(listOfGatheredTweets, "TweetyzardExport"));
using (dsTweetContainer)
{
OpenXMLHelper.ExportDataSetToExcel(dsTweetContainer, dlg.FileName, true);
tweetsTextBox.AppendText("\r\n Your data has been exported to: " + dlg.FileName);
tweetsTextBox.SelectionStart = tweetsTextBox.TextLength;
tweetsTextBox.ScrollToCaret();
listOfGatheredTweets = new List <TweetStore>();
}
}
}
else
{
MessageBox.Show("No Tweet Gathered", "No Tweets", MessageBoxButtons.OK);
return;
}
}
private Row CreateHeaderRowForExcel(List <ColumnMappingModel> columnMapping)
{
Row workRow = new Row();
foreach (var t in columnMapping)
{
workRow.Append(OpenXMLHelper.CreateCell(t.Name, 2U));
}
return(workRow);
}
private Row CreateHeaderRowForExcel()
{
Row workRow = new Row();
workRow.Append(OpenXMLHelper.CreateCell("Name", 2U));
workRow.Append(OpenXMLHelper.CreateCell("Deployment Status", 2U));
workRow.Append(OpenXMLHelper.CreateCell("Firmware", 2U));
workRow.Append(OpenXMLHelper.CreateCell("Start", 2U));
workRow.Append(OpenXMLHelper.CreateCell("End", 2U));
return(workRow);
}
private Row GenerateRowForChildPartDetail(DeviceRegistryApiModel model, List <ColumnMappingModel> columnMapping)
{
Row tRow = new Row();
JObject modelObject = (JObject)JToken.FromObject(model);
foreach (var map in columnMapping)
{
tRow.Append(OpenXMLHelper.CreateCell((string)modelObject.SelectToken(map.Mapping)));
}
return(tRow);
}
private Row GenerateRowForChildPartDetail(DeviceDeploymentStatus testmodel)
{
Row tRow = new Row();
tRow.Append(OpenXMLHelper.CreateCell(testmodel.Name));
tRow.Append(OpenXMLHelper.CreateCell(testmodel.DeploymentStatus));
tRow.Append(OpenXMLHelper.CreateCell(testmodel.Firmware));
tRow.Append(OpenXMLHelper.CreateCell(testmodel.Start));
tRow.Append(OpenXMLHelper.CreateCell(testmodel.End));
return(tRow);
}
private void CreatePartsForExcel(SpreadsheetDocument document, List <DeviceDeploymentStatus> data)
{
SheetData partSheetData = this.GenerateSheetdataForDetails(data);
WorkbookPart workbookPart = document.AddWorkbookPart();
this.GenerateWorkbookPartContent(workbookPart);
WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart <WorkbookStylesPart>("rId3");
OpenXMLHelper.GenerateWorkbookStylesPartContent(workbookStylesPart);
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>("rId1");
OpenXMLHelper.GenerateWorksheetPartContent(worksheetPart, partSheetData);
}
private void CreatePartsForExcel(SpreadsheetDocument document, List <DeviceRegistryApiModel> data, List <ColumnMappingModel> columnMapping)
{
SheetData partSheetData = this.GenerateSheetdataForDetails(data, columnMapping);
WorkbookPart workbookPart = document.AddWorkbookPart();
this.GenerateWorkbookPartContent(workbookPart);
WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart <WorkbookStylesPart>("rId3");
OpenXMLHelper.GenerateWorkbookStylesPartContent(workbookStylesPart);
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>("rId1");
OpenXMLHelper.GenerateWorksheetPartContent(worksheetPart, partSheetData);
}
private DataTable ReadAsDataTable(string fileName)
{
DataTable dta = OpenXMLHelper.ExcelWorksheetToDataTable(fileName, fuImportfile.FileName);
dta.Rows.RemoveAt(0);
var dt = new DataTable();
//Add columns to DataTable.
dt.Columns.AddRange(
new DataColumn[7]
{
new DataColumn("Id", Type.GetType("System.Int32")),
new DataColumn("Ar"), new DataColumn("Ord"),
new DataColumn("Arende"), new DataColumn("Betankande"),
new DataColumn("Skrivelse"), new DataColumn("Protokoll")
});
//Set AutoIncrement True for the First Column.
dt.Columns["Id"].AutoIncrement = true;
//Set the Starting or Seed value.
dt.Columns["Id"].AutoIncrementSeed = 1;
//Set the Increment value.
dt.Columns["Id"].AutoIncrementStep = 1;
foreach (DataRow row in dta.Rows)
{
dt.Rows.Add(null, row[0], row[1], row[2], row[3], row[4], row[5]);
}
// Set the sort column and sort order.
dt.DefaultView.Sort = ImportTableDataSortColumn + " " + ImportTableDataSortDirection;
ViewState["ImportFileContent"] = dt;
return(dt);
}
public DocumentHelper()
{
_openXMLHelper = new OpenXMLHelper();
}
public JsonResult AddFromExcel(string file)
{
BaseJsonData json = new BaseJsonData();
if (!User.Identity.IsAuthenticated)
{
json.msg_text = "没有登陆或登陆失效，请重新登陆后操作。";
json.msg_code = "notLogin";
return(Json(json, JsonRequestBehavior.AllowGet));
}
int user = PageValidate.FilterParam(User.Identity.Name);
if (!RoleCheck.CheckHasAuthority(user, db, "添加经费", "经费管理"))
{
json.msg_text = "没有权限。";
json.msg_code = "noPower";
return(Json(json, JsonRequestBehavior.AllowGet));
}
string attachmentTempPath = MyConfiguration.GetAttachmentTempPath();//上传附件的暂存地址
string filePath = attachmentTempPath + file;
if (!io.File.Exists(filePath))
{
json.msg_text = "没有找到上传的文件。";
json.msg_code = "noFile";
return(Json(json, JsonRequestBehavior.AllowGet));
}
OpenXMLHelper excel = new OpenXMLHelper();
DataTable dtExcel = null;
try
{
dtExcel = excel.ExcelToDataTable("Sheet1", filePath);
}catch (Exception ee)
{
json.msg_text = string.Format("excel[{0}]读取出错。", file);
json.msg_code = "noThis";
return(Json(json, JsonRequestBehavior.AllowGet));
}
if (dtExcel == null || dtExcel.Rows.Count == 0)
{
json.msg_text = "表格里没有行。";
json.msg_code = "noThis";
return(Json(json, JsonRequestBehavior.AllowGet));
}
string code, name, manager;
decimal amount, balance;
int i = 1, mid;
StringBuilder sbMsg = new StringBuilder();
List <SelectOption> managers = DropDownList.UserSelect(1);
foreach (DataRow dr in dtExcel.Rows)
{
code = dr["项目编号"].ToString().Trim();
if (string.IsNullOrEmpty(code))
{
sbMsg.Append("行").Append(i).Append("没有项目编号。\r\n");
continue;
}
if (db.Funds.Where(x => x.f_code == code).Count() > 0)
{
sbMsg.Append("行").Append(i).Append("经费").Append(code).Append("经费已存在，勿重复录入。\r\n");
continue;
}
name = dr["项目名称"].ToString().Trim();
manager = dr["负责人"].ToString().Trim();
if (string.IsNullOrEmpty(manager))
{
sbMsg.Append("行").Append(i).Append("经费").Append(code).Append("没有项目负责人。\r\n");
continue;
}
try
{
mid = int.Parse(
(from m in managers where m.text == manager select m.id).FirstOrDefault());
}
catch
{
sbMsg.Append("行").Append(i).Append("经费").Append(code).Append("项目负责人未录入。\r\n");
continue;
}
try
{
balance = decimal.Parse(dr["本年余额"].ToString().Trim().Replace(",", ""));
}
catch
{
sbMsg.Append("行").Append(i).Append("经费").Append(code).Append("经费余额读取失败。\r\n");
continue;
}
try
{
amount = decimal.Parse(dr["年初数"].ToString().Trim().Replace(",", ""));
}
catch { amount = balance; }
Funds funds = new Funds
{
f_add_Time = DateTime.Now,
f_amount = amount,
f_balance = balance,
f_code = code,
f_info = name,
f_manager = mid,
f_name = name,
f_source = name,
f_state = 1
};
db.Funds.Add(funds);
}
try
{
db.SaveChanges();
SysLog.WriteLog(user, "经费批量录入[{0}]", IpHelper.GetIP(), "", 6, "", db);
}
catch (Exception)
{
json.msg_text = "添加失败，请检查excel后重新操作。";
json.msg_code = "delErr";
return(Json(json, JsonRequestBehavior.AllowGet));
}
DataCache.RemoveCacheBySearch("cache_user");
DataCache.RemoveCacheBySearch("cache_response_user");
DataCache.RemoveCacheBySearch("cache_funds");
json.msg_text = sbMsg.ToString();
json.msg_code = "success";
json.state = 1;
return(Json(json, JsonRequestBehavior.AllowGet));
}
