/// <summary> /// SheetからEntityTableへの変換 /// </summary> /// <param name="sheet">対象シート</param> /// <returns>変換したEntityTable</returns> /// <remarks> /// Excel格納情報 (列,行)表記 /// ( 2, 2) テーブル物理名(値は文字列) /// ( 2, 3) テーブル論理名(値は文字列) /// 以下フィールド情報。5行目はヘッダー、6行目から空白値まで値 /// ( 1, 6) フィールドNoの開始位置(値は数値) /// ( 2, 6) フィールド物理名の開始位置(値は文字列) /// ( 3, 6) フィールド論理名の開始位置(値は文字列) /// ( 4, 6) フィールドコメントの開始位置(値は文字列) /// ( 5, 6) フィールド型の開始位置(値は文字列) /// ( 6, 6) フィールドサイズの開始位置(値は数値) /// ( 7, 6) フィールドNull制約有無の開始位置(値は〇 or 空白) /// ( 8, 6) フィールドデフォルト値の開始位置(値は文字列) /// ( 9, 6) フィールド主キーの開始位置(値は〇 or 空白) /// /// </remarks> private EntityTable ConvertSheetToEntityTable(ExcelObject sheet) { var newEntityTable = new EntityTable { PhysicsName = sheet["B2"], LogicName = sheet["B3"] }; var fields = new List <EntityField>(); for (int cnt = 0; cnt < 65535; cnt++) { var row = (cnt + 6).ToString(); // フィールドのNo値が空白の場合、処理を抜ける if (string.IsNullOrEmpty(sheet["A" + row])) { break; } var field = new EntityField(); field.No = intParseEx(sheet["A" + row]); field.PhysicsName = sheet["B" + row]; field.LogicName = sheet["C" + row]; //field.Comment = sheet["D" + row]; field.FieldType = SqlServerConfiguration.ConvertFieldTypeToString(sheet["E" + row]); field.FieldSize = sheet["F" + row] == "" ? 0 : intParseEx(sheet["F" + row]); field.Nullable = sheet["G" + row] == "〇" ? true : false; field.Required = sheet["H" + row] == "〇" ? true : false; fields.Add(field); } newEntityTable.Fields = fields; return(newEntityTable); }
public override void Submit_OnClick(object sender, EventArgs e) { try { PhExport.Visible = false; const string fileName = "users.csv"; var filePath = PathUtils.GetTemporaryFilesPath(fileName); ExcelObject.CreateExcelFileForUsers(filePath, ETriStateUtils.GetEnumType(DdlCheckedState.SelectedValue)); var link = new HyperLink { NavigateUrl = ApiRouteActionsDownload.GetUrl(PageUtility.InnerApiUrl, filePath), Text = "下载" }; var successMessage = "成功导出文件! " + ControlUtils.GetControlRenderHtml(link); SuccessMessage(successMessage); BtnSubmit.Visible = false; } catch (Exception ex) { var failedMessage = "文件导出失败!<br/><br/>原因为:" + ex.Message; FailMessage(ex, failedMessage); } }
private void ExportContentExcel(int channelId, List <int> contentIdList, List <string> displayAttributes, bool isPeriods, string dateFrom, string dateTo, ETriState checkedState, out string fileName) { var nodeInfo = ChannelManager.GetChannelInfo(SiteId, channelId); fileName = $"{nodeInfo.ChannelName}.csv"; var filePath = PathUtils.GetTemporaryFilesPath(fileName); ExcelObject.CreateExcelFileForContents(filePath, SiteInfo, nodeInfo, contentIdList, displayAttributes, isPeriods, dateFrom, dateTo, checkedState); }
private string ExportComment(int nodeId, int contentId) { var docFileName = "评论.csv"; var filePath = PathUtils.GetTemporaryFilesPath(docFileName); ExcelObject.CreateExcelFileForComments(filePath, PublishmentSystemInfo, nodeId, contentId); return(docFileName); }
private string ExportTrackingContents(string startDateString, string endDateString, int nodeId, int contentId, int totalNum, bool isDelete) { var docFileName = "内容统计.csv"; var filePath = PathUtils.GetTemporaryFilesPath(docFileName); ExcelObject.CreateExcelFileForTrackingContents(filePath, startDateString, endDateString, PublishmentSystemInfo, nodeId, contentId, totalNum, isDelete); return(docFileName); }
private string ExportTrackingYears() { var docFileName = "年统计.csv"; var filePath = PathUtils.GetTemporaryFilesPath(docFileName); ExcelObject.CreateExcelFileForTrackingYears(filePath, PublishmentSystemId); return(docFileName); }
public RegularMeeting GetRegularMeetingInfo() { var meeting = new RegularMeeting(); using (var excelObject = new ExcelObject(agenda)) { if (Regex.IsMatch(agenda, "old")) { meeting.Date = excelObject.At(3, 2).Unify(checklist); meeting.Room = excelObject.At(4, 2).Unify(checklist); meeting.Theme = excelObject.At(5, 2).Unify(checklist); meeting.Toastmaster = excelObject.At(6, 2).Unify(checklist); meeting.SAA = excelObject.At(7, 2).Unify(checklist); meeting.General_Evaluator = excelObject.At(8, 2).Unify(checklist); meeting.Timer = excelObject.At(9, 2).Unify(checklist); meeting.Grammarian = excelObject.At(10, 2).Unify(checklist); meeting.Ah_Counter = excelObject.At(11, 2).Unify(checklist); meeting.Table_Topic_Master = excelObject.At(12, 2).Unify(checklist); meeting.Table_Topic_Evaluator = excelObject.At(13, 2).Unify(checklist); meeting.Prepared_Speaker_1 = excelObject.At(14, 2).Unify(checklist); meeting.Prepared_Speaker_2 = excelObject.At(18, 2).Unify(checklist); meeting.Speech_Evaluator_1 = excelObject.At(17, 2).Unify(checklist); meeting.Speech_Evaluator_2 = excelObject.At(21, 2).Unify(checklist); meeting.Quiz_Master = excelObject.At(23, 2).Unify(checklist); meeting.Photographer = excelObject.At(24, 2).Unify(checklist); meeting.Video_Taker = excelObject.At(25, 2).Unify(checklist); meeting.Meeting_Minutes_Taker = excelObject.At(26, 2).Unify(checklist); meeting.Times = excelObject.At(28, 2).Unify(checklist); } else { meeting.Date = excelObject.At(3, 2).Unify(checklist); meeting.Room = excelObject.At(4, 2).Unify(checklist); meeting.Theme = excelObject.At(5, 2).Unify(checklist); meeting.Toastmaster = excelObject.At(7, 2).Unify(checklist); meeting.SAA = excelObject.At(8, 2).Unify(checklist); meeting.General_Evaluator = excelObject.At(9, 2).Unify(checklist); meeting.Timer = excelObject.At(10, 2).Unify(checklist); meeting.Grammarian = excelObject.At(11, 2).Unify(checklist); meeting.Ah_Counter = excelObject.At(12, 2).Unify(checklist); meeting.Table_Topic_Master = excelObject.At(13, 2).Unify(checklist); meeting.Table_Topic_Evaluator = excelObject.At(14, 2).Unify(checklist); meeting.Prepared_Speaker_1 = excelObject.At(15, 2).Unify(checklist); meeting.Prepared_Speaker_2 = excelObject.At(16, 2).Unify(checklist); meeting.Speech_Evaluator_1 = excelObject.At(17, 2).Unify(checklist); meeting.Speech_Evaluator_2 = excelObject.At(18, 2).Unify(checklist); meeting.Quiz_Master = excelObject.At(20, 2).Unify(checklist); meeting.Photographer = excelObject.At(21, 2).Unify(checklist); meeting.Video_Taker = excelObject.At(22, 2).Unify(checklist); meeting.Meeting_Minutes_Taker = excelObject.At(23, 2).Unify(checklist); meeting.Times = excelObject.At(32, 2).Unify(checklist); } } return(meeting); }
/// <summary> /// Entityテーブル群の生成 /// </summary> /// <param name="path"></param> /// <returns></returns> public List <EntityTable> Generat(string path) { var tables = new List <EntityTable>(); var excel = new ExcelObject(); excel.Load(path); tables.Add(ConvertSheetToEntityTable(excel)); return(tables); }
private string ExportInputContent(int inputId) { var inputInfo = DataProvider.InputDao.GetInputInfo(inputId); string docFileName = $"{inputInfo.InputName}.csv"; var filePath = PathUtils.GetTemporaryFilesPath(docFileName); ExcelObject.CreateExcelFileForInputContents(filePath, PublishmentSystemInfo, inputInfo); return(docFileName); }
public DataTable GetSpecificColumnsValue(String filename, bool headEncode, String SheetName, List <object> columnsList, String Criteria) { ExcelObject excelObj; if (headEncode) { excelObj = new ExcelObject(filename, HeaderEncoding.Yes); } else { excelObj = new ExcelObject(filename, HeaderEncoding.No); } return(excelObj.GetSpecificColumnsValue(SheetName, columnsList, Criteria)); }
public async Task <ActionResult> Export() { if (!await _authManager.HasAppPermissionsAsync(Types.AppPermissions.SettingsUsers)) { return(Unauthorized()); } const string fileName = "users.csv"; var filePath = _pathManager.GetTemporaryFilesPath(fileName); var excelObject = new ExcelObject(_databaseManager, _pathManager); await excelObject.CreateExcelFileForUsersAsync(filePath, null); return(this.Download(filePath)); }
public void ImportInputContentsByCsvFile(InputInfo inputInfo, string excelFilePath, int importStart, int importCount, bool isChecked) { var contentInfoList = ExcelObject.GetInputContentsByCsvFile(excelFilePath, Fso.PublishmentSystemInfo, inputInfo); contentInfoList.Reverse(); if (importStart > 1 || importCount > 0) { var theList = new List <InputContentInfo>(); if (importStart == 0) { importStart = 1; } if (importCount == 0) { importCount = contentInfoList.Count; } var firstIndex = contentInfoList.Count - importStart - importCount + 1; if (firstIndex <= 0) { firstIndex = 0; } var addCount = 0; for (var i = 0; i < contentInfoList.Count; i++) { if (addCount >= importCount) { break; } if (i >= firstIndex) { theList.Add(contentInfoList[i]); addCount++; } } contentInfoList = theList; } foreach (var contentInfo in contentInfoList) { contentInfo.IsChecked = isChecked; DataProvider.InputContentDao.Insert(contentInfo); } }
/// <summary> /// /// </summary> /// <param name="filename">Excel filename from which you want to get data</param> /// <param name="headEncode">Consider top header as data of the sheet:true ---- Consider top header as a row</param> /// <param name="SheetName">Sheet name of the given file to get data</param> /// <returns>Returns all column and their definitions present in the given sheet as dataTable object.</returns> public DataTable ExcelColumnDef(String filename, bool headEncode, String SheetName) { DataTable Returndt = new DataTable(); ExcelObject excelObj; if (headEncode) { excelObj = new ExcelObject(filename, HeaderEncoding.Yes); } else { excelObj = new ExcelObject(filename, HeaderEncoding.No); } Returndt = excelObj.GetColumnDefinition(SheetName); return(Returndt); }
public List <int> ImportContentsByCsvFile(ChannelInfo channelInfo, string csvFilePath, bool isOverride, bool isChecked, int checkedLevel, int adminId, int userId, int sourceId) { var contentInfoList = ExcelObject.GetContentsByCsvFile(csvFilePath, _siteInfo, channelInfo); contentInfoList.Reverse(); var tableName = ChannelManager.GetTableName(_siteInfo, channelInfo); foreach (var contentInfo in contentInfoList) { contentInfo.IsChecked = isChecked; contentInfo.CheckedLevel = checkedLevel; if (!contentInfo.AddDate.HasValue) { contentInfo.AddDate = DateTime.Now; } contentInfo.LastEditDate = DateTime.Now; contentInfo.AdminId = adminId; contentInfo.UserId = userId; contentInfo.SourceId = sourceId; if (isOverride) { var existsIds = DataProvider.ContentDao.GetIdListBySameTitle(tableName, contentInfo.ChannelId, contentInfo.Title); if (existsIds.Count > 0) { foreach (var id in existsIds) { contentInfo.Id = id; DataProvider.ContentDao.Update(_siteInfo, channelInfo, contentInfo); } } else { contentInfo.Id = DataProvider.ContentDao.Insert(tableName, _siteInfo, channelInfo, contentInfo); } } else { contentInfo.Id = DataProvider.ContentDao.Insert(tableName, _siteInfo, channelInfo, contentInfo); } } return(contentInfoList.Select(x => x.Id).ToList()); }
private void CreateExcelObject(bool displayAlerts, bool isVisible) { Cursor = Cursors.WaitCursor; ExcelObject objExcel = new ExcelObject(isVisible, displayAlerts) { CloseExcelAfterCompleteFlag = CloseAfterCompleteCheckBox.Checked, ChartStyleCode = int.Parse(ChartStyleCombo.SelectedValue.ToString()), CollorPatternCode = int.Parse(CollorPatternCombo.SelectedValue.ToString()), TechnologyGroupId = int.Parse(GroupNameList.SelectedValue.ToString()) }; objExcel.GenerateCustomChart(ExcelObject.CustomChartFormat.TechnologyQuadrant); DisplayChartPicture(); Cursor = Cursors.Default; }
public Entity ObjectConvertoEntity(ExcelObject excelobject) { Entity entity = new Entity("account"); //entity["accountname"] = excelobject.AccountName; entity["emailaddress1"] = excelobject.EmailAddress1; entity["emailaddress2"] = excelobject.EmailAddress2; entity["telephone1"] = excelobject.Telephone1; entity["telephone2"] = excelobject.Telephone2; //Varlık alanları aşağıdadır. //Alanların varlık üzerine maplemesi gereklidir. //Objenin nasıl doldurulacağını araştıraak bulmanızı bekliyoruz. //Aşağıdaki 5 alan string tipindedir. //accountname,emailaddress1,emailaddress2,telephone1,telephone2 return(entity); }
public async Task <List <int> > ImportContentsByCsvFileAsync(Channel channel, string csvFilePath, bool isOverride, bool isChecked, int checkedLevel, int adminId, int userId, int sourceId) { var excelObject = new ExcelObject(_databaseManager, _pathManager); var contentInfoList = await excelObject.GetContentsByCsvFileAsync(csvFilePath, _site, channel); contentInfoList.Reverse(); foreach (var contentInfo in contentInfoList) { contentInfo.Checked = isChecked; contentInfo.CheckedLevel = checkedLevel; if (!contentInfo.AddDate.HasValue) { contentInfo.AddDate = DateTime.Now; } contentInfo.AdminId = adminId; contentInfo.UserId = userId; contentInfo.SourceId = sourceId; if (isOverride) { var existsIds = await _databaseManager.ContentRepository.GetContentIdsBySameTitleAsync(_site, channel, contentInfo.Title); if (existsIds.Count > 0) { foreach (var id in existsIds) { contentInfo.Id = id; await _databaseManager.ContentRepository.UpdateAsync(_site, channel, contentInfo); } } else { contentInfo.Id = await _databaseManager.ContentRepository.InsertAsync(_site, channel, contentInfo); } } else { contentInfo.Id = await _databaseManager.ContentRepository.InsertAsync(_site, channel, contentInfo); } } return(contentInfoList.Select(x => x.Id).ToList()); }
public async Task <ActionResult <StringResult> > Export() { if (!await _authManager.HasAppPermissionsAsync(Types.AppPermissions.SettingsAdministrators)) { return(Unauthorized()); } const string fileName = "administrators.csv"; var filePath = _pathManager.GetTemporaryFilesPath(fileName); var excelObject = new ExcelObject(_databaseManager, _pathManager); await excelObject.CreateExcelFileForAdministratorsAsync(filePath); var downloadUrl = _pathManager.GetRootUrlByPath(filePath); return(new StringResult { Value = downloadUrl }); }
private void cboSheet_SelectedIndexChanged(object sender, EventArgs e) { DataTable dt = tableCollection[cboSheet.SelectedItem.ToString()]; dataGridView1.DataSource = dt; dataGridView1.AllowUserToAddRows = false; ExcelObject newexcelObject = new ExcelObject(); foreach (DataGridViewRow account in dataGridView1.Rows) { newexcelObject.AccountName = account.Cells["AccountName"].Value.ToString(); newexcelObject.Telephone1 = account.Cells["Telephone1"].Value.ToString(); newexcelObject.Telephone2 = account.Cells["Telephone2"].Value.ToString(); newexcelObject.EmailAddress1 = account.Cells["EmailAddress1"].Value.ToString(); newexcelObject.EmailAddress2 = account.Cells["EmailAddress2"].Value.ToString(); var entity = mapHelper.ObjectConvertoEntity(newexcelObject); mSCRMHelper.CreateEntityRecord(entity); } }
/// <summary> /// Get Content of given excel file's particular sheet in table format /// </summary> /// <param name="filename">Excel filename from which you want to get data</param> /// <param name="headEncode">Consider top header as data of the sheet:true ---- Consider top header as a row</param> /// <param name="SheetName">Sheet name of the given file to get data</param> /// <returns>Returns all data present in the given sheet as dataTable object.</returns> public DataTable ExcelContent(String filename, bool headEncode, String SheetName) { DataTable Returndt = new DataTable(); ExcelObject excelObj; if (headEncode) { excelObj = new ExcelObject(filename, HeaderEncoding.Yes); } else { excelObj = new ExcelObject(filename, HeaderEncoding.No); } DataTable dtSchema = excelObj.GetSheetList(); var retGivenSheetTableName = (from dr in dtSchema.AsEnumerable() where dr["TABLE_NAME"].ToString().Contains(SheetName) select dr["TABLE_NAME"]).ToList(); if (retGivenSheetTableName != null) { Returndt = excelObj.ReadSheet(retGivenSheetTableName[0].ToString(), ""); } return(Returndt); }
public async Task ImportContentsByCsvFileAsync(int channelId, string csvFilePath, bool isOverride, int importStart, int importCount, bool isChecked, int checkedLevel) { var channelInfo = await _databaseManager.ChannelRepository.GetAsync(channelId); var excelObject = new ExcelObject(_databaseManager, _pathManager); var contentInfoList = await excelObject.GetContentsByCsvFileAsync(csvFilePath, _site, channelInfo); contentInfoList.Reverse(); if (importStart > 1 || importCount > 0) { var theList = new List <Content>(); if (importStart == 0) { importStart = 1; } if (importCount == 0) { importCount = contentInfoList.Count; } var firstIndex = contentInfoList.Count - importStart - importCount + 1; if (firstIndex <= 0) { firstIndex = 0; } var addCount = 0; for (var i = 0; i < contentInfoList.Count; i++) { if (addCount >= importCount) { break; } if (i >= firstIndex) { theList.Add(contentInfoList[i]); addCount++; } } contentInfoList = theList; } foreach (var contentInfo in contentInfoList) { contentInfo.Checked = isChecked; contentInfo.CheckedLevel = checkedLevel; if (isOverride) { var existsIds = await _databaseManager.ContentRepository.GetContentIdsBySameTitleAsync(_site, channelInfo, contentInfo.Title); if (existsIds.Count > 0) { foreach (var id in existsIds) { contentInfo.Id = id; await _databaseManager.ContentRepository.UpdateAsync(_site, channelInfo, contentInfo); } } else { contentInfo.Id = await _databaseManager.ContentRepository.InsertAsync(_site, channelInfo, contentInfo); } } else { contentInfo.Id = await _databaseManager.ContentRepository.InsertAsync(_site, channelInfo, contentInfo); } //this.FSO.AddContentToWaitingCreate(contentInfo.ChannelId, contentID); } }
public IHttpActionResult Submit() { try { var request = new RequestImpl(); var downloadUrl = string.Empty; var siteId = request.GetPostInt("siteId"); var channelId = request.GetPostInt("channelId"); var exportType = request.GetPostString("exportType"); var isAllCheckedLevel = request.GetPostBool("isAllCheckedLevel"); var checkedLevelKeys = request.GetPostObject <List <int> >("checkedLevelKeys"); var isAllDate = request.GetPostBool("isAllDate"); var startDate = request.GetPostDateTime("startDate", DateTime.Now); var endDate = request.GetPostDateTime("endDate", DateTime.Now); var columnNames = request.GetPostObject <List <string> >("columnNames"); if (!request.IsAdminLoggin || !request.AdminPermissions.HasChannelPermissions(siteId, channelId, ConfigManager.ChannelPermissions.ChannelEdit)) { return(Unauthorized()); } var siteInfo = SiteManager.GetSiteInfo(siteId); if (siteInfo == null) { return(BadRequest("无法确定内容对应的站点")); } var channelInfo = ChannelManager.GetChannelInfo(siteId, channelId); if (channelInfo == null) { return(BadRequest("无法确定内容对应的栏目")); } var columns = ContentManager.GetContentColumns(siteInfo, channelInfo, true); var pluginIds = PluginContentManager.GetContentPluginIds(channelInfo); var pluginColumns = PluginContentManager.GetContentColumns(pluginIds); var contentInfoList = new List <ContentInfo>(); var count = ContentManager.GetCount(siteInfo, channelInfo); var pages = Convert.ToInt32(Math.Ceiling((double)count / siteInfo.Additional.PageSize)); if (pages == 0) { pages = 1; } if (count > 0) { for (var page = 1; page <= pages; page++) { var offset = siteInfo.Additional.PageSize * (page - 1); var limit = siteInfo.Additional.PageSize; var pageContentIds = ContentManager.GetContentIdList(siteInfo, channelInfo, offset, limit); var sequence = offset + 1; foreach (var contentId in pageContentIds) { var contentInfo = ContentManager.GetContentInfo(siteInfo, channelInfo, contentId); if (contentInfo == null) { continue; } if (!isAllCheckedLevel) { var checkedLevel = contentInfo.CheckedLevel; if (contentInfo.IsChecked) { checkedLevel = siteInfo.Additional.CheckContentLevel; } if (!checkedLevelKeys.Contains(checkedLevel)) { continue; } } if (!isAllDate) { if (contentInfo.AddDate < startDate || contentInfo.AddDate > endDate) { continue; } } contentInfoList.Add(ContentManager.Calculate(sequence++, contentInfo, columns, pluginColumns)); } } if (contentInfoList.Count > 0) { if (exportType == "zip") { var fileName = $"{channelInfo.ChannelName}.zip"; var filePath = PathUtils.GetTemporaryFilesPath(fileName); var exportObject = new ExportObject(siteId, request.AdminName); contentInfoList.Reverse(); if (exportObject.ExportContents(filePath, contentInfoList)) { downloadUrl = PageUtils.GetTemporaryFilesUrl(fileName); } } else if (exportType == "excel") { var fileName = $"{channelInfo.ChannelName}.csv"; var filePath = PathUtils.GetTemporaryFilesPath(fileName); ExcelObject.CreateExcelFileForContents(filePath, siteInfo, channelInfo, contentInfoList, columnNames); downloadUrl = PageUtils.GetTemporaryFilesUrl(fileName); } } } return(Ok(new { Value = downloadUrl, IsSuccess = !string.IsNullOrEmpty(downloadUrl) })); } catch (Exception ex) { LogUtils.AddErrorLog(ex); return(InternalServerError(ex)); } }
public ExcelTable(ref ExcelColumns columns) { _columns = columns; _table = new ExcelObject("Table"); if (_table != null) { int _column_count = columns.Count(); for (int index = 0; index < _column_count; index++) { ExcelObject _value = columns.GetColumnObject(index); _table.AddObject(-(index + 1), ref _value); }; }; _rows = new SortedList(); }
public bool SetValue(string column_name, string value, bool is_white) { bool result = false; if (_list != null) { if (!_list.Contains(column_name)) { ExcelObject _cell = new ExcelObject("Cell"); if (_cell != null) { ExcelObject _data = new ExcelObject("Data"); if (_data != null) { string column_type = ""; string column_system_type = ""; string style_id = ""; int column_index = 0; ExcelColumns _columns = _table.GetColumns(); if (_columns != null) { ExcelObject _column = _columns.GetColumnObject(column_name); if (_column != null) { column_type = _column.GetPropertyValue("Type"); column_system_type = _column.GetPropertyValue("SystemType"); style_id = _column.GetPropertyValue("StyleId"); column_index = Int32.Parse(_column.GetPropertyValue("Index")); if (column_index < 0) column_index = -column_index; _column.IsNewLine = true; }; }; int style_int = 0; try { style_int = Int32.Parse(style_id); if (!is_white) style_int = style_int + 4; } catch { } _cell.AddProperty("ss", "StyleID", "s" + style_int.ToString()); _row.AddObject(column_index, ref _cell); if (value.Length > 0) { _cell.AddObject(0, ref _data); _data.AddProperty("ss", "Type", column_type); if (column_system_type == "System.DateTime") value = GetExcelShortDateFormat(value); if (column_system_type == "System.Decimal") value = GetExcelDoubleFormat(value); ExcelObject _value = new ExcelObject(value); if (_value != null) { _data.AddObject(0, ref _value); _value.IsXML = false; _list.Add(column_name, _value); result = true; }; }; }; }; } else { if (value.Length > 0) ((ExcelObject)_list[column_name]).Name = value; }; }; return result; }
public bool SetHeaderValue(string column_name, string column_alias) { bool result = false; //string value = column_name; string value = column_alias; if (_list != null) { if (!_list.Contains(column_name)) { ExcelObject _cell = new ExcelObject("Cell"); if (_cell != null) { ExcelObject _data = new ExcelObject("Data"); if (_data != null) { string column_type = ""; string column_system_type = ""; string style_id = ""; int column_index = 0; ExcelColumns _columns = _table.GetColumns(); if (_columns != null) { ExcelObject _column = _columns.GetColumnObject(column_name); if (_column != null) { column_type = _column.GetPropertyValue("Type"); column_system_type = _column.GetPropertyValue("SystemType"); style_id = _column.GetPropertyValue("StyleId"); column_index = Int32.Parse(_column.GetPropertyValue("Index")); if (column_index < 0) column_index = -column_index; _column.IsNewLine = true; }; }; _cell.AddProperty("ss", "StyleID", "s21"); _row.AddObject(column_index, ref _cell); if (value.Length > 0) { _cell.AddObject(0, ref _data); _data.AddProperty("ss", "Type", "String"); ExcelObject _value = new ExcelObject(value); if (_value != null) { _data.AddObject(0, ref _value); _value.IsXML = false; _list.Add(column_name, _value); result = true; }; }; }; }; } else ((ExcelObject)_list[column_name]).Name = value; }; return result; }
public void ImportContentsByCsvFile(int channelId, string csvFilePath, bool isOverride, int importStart, int importCount, bool isChecked, int checkedLevel) { var channelInfo = ChannelManager.GetChannelInfo(_siteInfo.Id, channelId); var contentInfoList = ExcelObject.GetContentsByCsvFile(csvFilePath, _siteInfo, channelInfo); contentInfoList.Reverse(); if (importStart > 1 || importCount > 0) { var theList = new List <ContentInfo>(); if (importStart == 0) { importStart = 1; } if (importCount == 0) { importCount = contentInfoList.Count; } var firstIndex = contentInfoList.Count - importStart - importCount + 1; if (firstIndex <= 0) { firstIndex = 0; } var addCount = 0; for (var i = 0; i < contentInfoList.Count; i++) { if (addCount >= importCount) { break; } if (i >= firstIndex) { theList.Add(contentInfoList[i]); addCount++; } } contentInfoList = theList; } var tableName = ChannelManager.GetTableName(_siteInfo, channelInfo); foreach (var contentInfo in contentInfoList) { contentInfo.IsChecked = isChecked; contentInfo.CheckedLevel = checkedLevel; if (isOverride) { var existsIds = DataProvider.ContentDao.GetIdListBySameTitle(tableName, contentInfo.ChannelId, contentInfo.Title); if (existsIds.Count > 0) { foreach (var id in existsIds) { contentInfo.Id = id; DataProvider.ContentDao.Update(tableName, _siteInfo, contentInfo); } } else { contentInfo.Id = DataProvider.ContentDao.Insert(tableName, _siteInfo, contentInfo); } } else { contentInfo.Id = DataProvider.ContentDao.Insert(tableName, _siteInfo, contentInfo); } //this.FSO.AddContentToWaitingCreate(contentInfo.ChannelId, contentID); } }
public IHttpActionResult Post() { string sPath = ""; string kmlPath = ""; int iUploadedCnt = 0; sPath = HostingEnvironment.MapPath("~/locker/"); // kmlPath = HostingEnvironment.MapPath("~/KML/"); HttpFileCollection hfc = HttpContext.Current.Request.Files; for (int iCnt = 0; iCnt <= hfc.Count - 1; iCnt++) { HttpPostedFile hpf = hfc[iCnt]; if (hpf.ContentLength > 0) { // CHECK IF THE SELECTED FILE(S) ALREADY EXISTS IN FOLDER. (AVOID DUPLICATE) if (!File.Exists(sPath + Path.GetFileName(hpf.FileName))) { //SAVE THE FILES IN THE FOLDER. hpf.SaveAs(sPath + Path.GetFileName(hfc.AllKeys[iCnt].ToString() + ".csv")); iUploadedCnt = iUploadedCnt + 1; } } } if (iUploadedCnt > 0) { for (int i = 0; i < iUploadedCnt; i++) { ExcelObject excel = new ExcelObject(); excel.readExcelFile(sPath + hfc.AllKeys[i].ToString() + ".csv"); if (hfc.AllKeys[i].ToString() == "Location List") { int colcnt = 4; // poll_Location poll = new poll_Location(); while (colcnt < excel.ReadList.Count) { poll_Location poll = new poll_Location(); poll.poll_Id = int.Parse(excel.ReadList[colcnt].ToString()); colcnt++; poll.poll_Name = excel.ReadList[colcnt].ToString(); colcnt++; poll.poll_Address = excel.ReadList[colcnt].ToString(); colcnt++; poll.Zone = int.Parse(excel.ReadList[colcnt].ToString()); colcnt++; poll.Monday_Arrival = 0; poll.Tuesday_Arrival = 0; poll.OpenReady = 0; poll.ClosePollReady = 0; db.Entry(poll).State = EntityState.Modified; if (!ModelState.IsValid) { return(BadRequest(ModelState)); } db.poll_Location.Add(poll); try { db.SaveChanges(); } catch (DbUpdateException) { if (poll_LocationExists(poll.poll_Id)) { return(Conflict()); } else { throw; } } } } else if (hfc.AllKeys[i].ToString() == "Location Details") { int colcnt = 4; while (colcnt < excel.ReadList.Count) { dbo_poll_ContactDetails pollCnt = new dbo_poll_ContactDetails(); pollCnt.poll_Id = int.Parse(excel.ReadList[colcnt].ToString()); colcnt++; pollCnt.contact_FirstName = excel.ReadList[colcnt].ToString(); colcnt++; pollCnt.contact_LastName = excel.ReadList[colcnt].ToString(); colcnt++; pollCnt.contact_Type = excel.ReadList[3].ToString(); // colcnt++; pollCnt.contact_Info = excel.ReadList[colcnt].ToString(); colcnt++; db.Entry(pollCnt).State = EntityState.Modified; if (!ModelState.IsValid) { return(BadRequest(ModelState)); } db.dbo_poll_ContactDetails.Add(pollCnt); try { db.SaveChanges(); } catch (DbUpdateException) { //if (!dbo_poll_ContactDetails(pollCnt.poll_Id)) //{ // return Conflict(); //} //else //{ // throw; //} } } } else if (hfc.AllKeys[i].ToString() == "Precinct List") { int colcnt = 4; while (colcnt < excel.ReadList.Count) { election_Precinct precinct = new election_Precinct(); precinct.poll_Id = int.Parse(excel.ReadList[colcnt].ToString()); colcnt++; precinct.precinct_Id = int.Parse(excel.ReadList[colcnt].ToString()); colcnt++; precinct.ward_Name = excel.ReadList[colcnt].ToString(); colcnt++; precinct.precinct = excel.ReadList[colcnt].ToString(); colcnt++; db.Entry(precinct).State = EntityState.Modified; if (!ModelState.IsValid) { return(BadRequest(ModelState)); } db.election_Precinct.Add(precinct); try { db.SaveChanges(); } catch (DbUpdateException) { if (!election_PrecinctExists(precinct.poll_Id)) { return(Conflict()); } else { throw; } } } } else if (hfc.AllKeys[i].ToString() == "KMLFile") { } else if (hfc.AllKeys[i].ToString() == "Polling Location All") { int colcnt = 8; while (colcnt < excel.ReadList.Count) { poll_Location_All pollAll = new poll_Location_All(); pollAll.poll_id = int.Parse(excel.ReadList[colcnt].ToString()); colcnt++; pollAll.status = excel.ReadList[colcnt].ToString(); colcnt++; pollAll.latitude = double.Parse(excel.ReadList[colcnt].ToString()); colcnt++; pollAll.longitude = double.Parse(excel.ReadList[colcnt].ToString()); colcnt++; pollAll.location_Name = excel.ReadList[colcnt].ToString(); colcnt++; pollAll.location_Address = excel.ReadList[colcnt].ToString(); colcnt++; pollAll.city = excel.ReadList[colcnt].ToString(); colcnt++; pollAll.state = excel.ReadList[colcnt].ToString(); colcnt++; db.Entry(pollAll).State = EntityState.Modified; if (!ModelState.IsValid) { return(BadRequest(ModelState)); } db.poll_Location_All.Add(pollAll); try { db.SaveChanges(); } catch (DbUpdateException) { if (!poll_Location_AllExists(pollAll.poll_id)) { return(Conflict()); } else { throw; } } } } } DirectoryInfo di = new DirectoryInfo(sPath); foreach (FileInfo file in di.GetFiles()) { file.Delete(); } } else { // return "Upload Failed"; } return(StatusCode(HttpStatusCode.NoContent)); }
public ExcelObject GetRootObject(ref ExcelObject existed_object) { ExcelObject result = null; if (existed_object != null) { result = existed_object; ExcelObject current_object = existed_object; while (current_object.Parent != null) { result = current_object; current_object = existed_object.Parent; }; }; return result; }
public bool AddObject(int key, ref ExcelObject new_object) { bool result = false; int new_key = key; if (_objects != null) { if (new_object != null) { if (new_key == 0) new_key = _objects.Count; if (!_objects.Contains(new_key)) { _objects.Add(new_key, new_object); new_object.Parent = this; result = true; }; }; }; return result; }
public ExcelTable CreateWorkSheet(string name, ref ExcelColumns columns) { ExcelTable result = null; if (_root != null) { ExcelObject _worksheet = new ExcelObject("Worksheet"); if (_worksheet != null) { _worksheet.AddProperty("ss", "Name", name); ExcelTable _table = new ExcelTable(ref columns); if (_table != null) { ExcelObject _table_object = _table.GetTableObject(); if (_table_object != null) { _worksheet.AddObject(0, ref _table_object); _table_object.IsNewLine = true; }; result = _table; }; //***_root.AddObject(0, ref _worksheet); }; }; return result; }
public ExcelDocument() { _column_filter = ""; _header = "<?xml version=\"1.0\"?>"; _header += "<?mso-application progid=\"Excel.Sheet\"?>"; //_footer = ""; _root = new ExcelObject("Workbook"); if (_root != null) { _root.AddProperty("", "xmlns", "urn:schemas-microsoft-com:office:spreadsheet"); _root.AddProperty("xmlns", "o", "urn:schemas-microsoft-com:office:office"); _root.AddProperty("xmlns", "x", "urn:schemas-microsoft-com:office:excel"); _root.AddProperty("xmlns", "ss", "urn:schemas-microsoft-com:office:spreadsheet"); _root.AddProperty("xmlns", "html", "http://www.w3.org/TR/REC-html40"); ExcelObject _styles = new ExcelObject("Styles"); if (_styles != null) { ExcelObject _style_default = new ExcelObject("Style"); if (_style_default != null) { _style_default.AddProperty("ss", "ID", "Default"); _style_default.AddProperty("ss", "Name", "Normal"); _styles.AddObject(0, ref _style_default); }; ExcelObject _header_default = new ExcelObject("Style"); if (_header_default != null) { ExcelObject _header_align = new ExcelObject("Alignment"); if (_header_align != null) { _header_align.AddProperty("ss", "Vertical", "Bottom"); _header_align.AddProperty("ss", "WrapText", "0"); _header_default.AddObject(0, ref _header_align); }; ExcelObject _header_font = new ExcelObject("Font"); if (_header_font != null) { _header_font.AddProperty("ss", "Color", "#FFFFFF"); _header_font.AddProperty("ss", "Bold", "1"); _header_default.AddObject(0, ref _header_font); }; ExcelObject _header_interior = new ExcelObject("Interior"); if (_header_interior != null) { _header_interior.AddProperty("ss", "Color", "#333333"); _header_interior.AddProperty("ss", "Pattern", "Solid"); _header_default.AddObject(0, ref _header_interior); }; _header_default.AddProperty("ss", "ID", "s21"); _styles.AddObject(0, ref _header_default); }; ExcelObject _number_default = new ExcelObject("Style"); if (_number_default != null) { ExcelObject _number_format_default = new ExcelObject("NumberFormat"); if (_number_format_default != null) { _number_default.AddObject(0, ref _number_format_default); }; ExcelObject _number_default_interior = new ExcelObject("Interior"); if (_number_default_interior != null) { _number_default_interior.AddProperty("ss", "Color", "#FFFFFF"); _number_default_interior.AddProperty("ss", "Pattern", "Solid"); _number_default.AddObject(0, ref _number_default_interior); }; _number_default.AddProperty("ss", "ID", "s22"); _styles.AddObject(0, ref _number_default); }; ExcelObject _number_zero_precision = new ExcelObject("Style"); if (_number_zero_precision != null) { ExcelObject _number_format_zero_precision = new ExcelObject("NumberFormat"); if (_number_format_zero_precision != null) { _number_format_zero_precision.AddProperty("ss", "Format", "0"); _number_zero_precision.AddObject(0, ref _number_format_zero_precision); }; ExcelObject _number_zero_precision_interior = new ExcelObject("Interior"); if (_number_zero_precision_interior != null) { _number_zero_precision_interior.AddProperty("ss", "Color", "#FFFFFF"); _number_zero_precision_interior.AddProperty("ss", "Pattern", "Solid"); _number_zero_precision.AddObject(0, ref _number_zero_precision_interior); }; _number_zero_precision.AddProperty("ss", "ID", "s23"); _styles.AddObject(0, ref _number_zero_precision); }; ExcelObject _number_four_precision = new ExcelObject("Style"); if (_number_four_precision != null) { ExcelObject _number_format_four_precision = new ExcelObject("NumberFormat"); if (_number_format_four_precision != null) { _number_format_four_precision.AddProperty("ss", "Format", "0.0000"); _number_four_precision.AddObject(0, ref _number_format_four_precision); }; ExcelObject _number_four_precision_interior = new ExcelObject("Interior"); if (_number_four_precision_interior != null) { _number_four_precision_interior.AddProperty("ss", "Color", "#FFFFFF"); _number_four_precision_interior.AddProperty("ss", "Pattern", "Solid"); _number_four_precision.AddObject(0, ref _number_four_precision_interior); }; _number_four_precision.AddProperty("ss", "ID", "s24"); _styles.AddObject(0, ref _number_four_precision); }; ExcelObject _date_style = new ExcelObject("Style"); if (_date_style != null) { ExcelObject _date_format = new ExcelObject("NumberFormat"); if (_date_format != null) { _date_format.AddProperty("ss", "Format", "Short Date"); _date_style.AddObject(0, ref _date_format); }; ExcelObject _date_style_interior = new ExcelObject("Interior"); if (_date_style_interior != null) { _date_style_interior.AddProperty("ss", "Color", "#FFFFFF"); _date_style_interior.AddProperty("ss", "Pattern", "Solid"); _date_style.AddObject(0, ref _date_style_interior); }; _date_style.AddProperty("ss", "ID", "s25"); _styles.AddObject(0, ref _date_style); }; //Other color ExcelObject _number_default_ = new ExcelObject("Style"); if (_number_default_ != null) { ExcelObject _number_format_default_ = new ExcelObject("NumberFormat"); if (_number_format_default_ != null) { _number_default_.AddObject(0, ref _number_format_default_); }; ExcelObject _number_default_interior_ = new ExcelObject("Interior"); if (_number_default_interior_ != null) { _number_default_interior_.AddProperty("ss", "Color", "#C0C0C0"); _number_default_interior_.AddProperty("ss", "Pattern", "Solid"); _number_default_.AddObject(0, ref _number_default_interior_); }; _number_default_.AddProperty("ss", "ID", "s26"); _styles.AddObject(0, ref _number_default_); }; ExcelObject _number_zero_precision_ = new ExcelObject("Style"); if (_number_zero_precision_ != null) { ExcelObject _number_format_zero_precision_ = new ExcelObject("NumberFormat"); if (_number_format_zero_precision_ != null) { _number_format_zero_precision_.AddProperty("ss", "Format", "0"); _number_zero_precision_.AddObject(0, ref _number_format_zero_precision_); }; ExcelObject _number_zero_precision_interior_ = new ExcelObject("Interior"); if (_number_zero_precision_interior_ != null) { _number_zero_precision_interior_.AddProperty("ss", "Color", "#C0C0C0"); _number_zero_precision_interior_.AddProperty("ss", "Pattern", "Solid"); _number_zero_precision_.AddObject(0, ref _number_zero_precision_interior_); }; _number_zero_precision_.AddProperty("ss", "ID", "s27"); _styles.AddObject(0, ref _number_zero_precision_); }; ExcelObject _number_four_precision_ = new ExcelObject("Style"); if (_number_four_precision_ != null) { ExcelObject _number_format_four_precision_ = new ExcelObject("NumberFormat"); if (_number_format_four_precision_ != null) { _number_format_four_precision_.AddProperty("ss", "Format", "0.0000"); _number_four_precision_.AddObject(0, ref _number_format_four_precision_); }; ExcelObject _number_four_precision_interior_ = new ExcelObject("Interior"); if (_number_four_precision_interior_ != null) { _number_four_precision_interior_.AddProperty("ss", "Color", "#C0C0C0"); _number_four_precision_interior_.AddProperty("ss", "Pattern", "Solid"); _number_four_precision_.AddObject(0, ref _number_four_precision_interior_); }; _number_four_precision_.AddProperty("ss", "ID", "s28"); _styles.AddObject(0, ref _number_four_precision_); }; ExcelObject _date_style_ = new ExcelObject("Style"); if (_date_style_ != null) { ExcelObject _date_format_ = new ExcelObject("NumberFormat"); if (_date_format_ != null) { _date_format_.AddProperty("ss", "Format", "Short Date"); _date_style_.AddObject(0, ref _date_format_); }; ExcelObject _date_style_interior_ = new ExcelObject("Interior"); if (_date_style_interior_ != null) { _date_style_interior_.AddProperty("ss", "Color", "#C0C0C0"); _date_style_interior_.AddProperty("ss", "Pattern", "Solid"); _date_style_.AddObject(0, ref _date_style_interior_); }; _date_style_.AddProperty("ss", "ID", "s29"); _styles.AddObject(0, ref _date_style_); }; _root.AddObject(0, ref _styles); }; }; }
public bool AddColumn(int index, string column_name, string column_alias, int width, string type, string system_type, string style_id) { bool result = false; if (_list != null) { if (!_list.Contains(column_name)) { ExcelObject _object = new ExcelObject("Column"); if (_object != null) { _object.AddProperty("ss", "AutoFitWidth", "0"); _object.AddProperty("ss", "Width", width.ToString()); _object.AddProperty("", "Type", type, false); _object.AddProperty("", "Name", column_name, false); _object.AddProperty("", "Alias", column_alias, false); _object.AddProperty("", "SystemType", system_type, false); _object.AddProperty("", "StyleId", style_id, false); _object.AddProperty("", "Index", index.ToString(), false); _list.Add(column_name, _object); result = true; }; }; }; return result; }
public async Task <ActionResult <SubmitResult> > Submit([FromBody] SubmitRequest request) { if (!await _authManager.HasContentPermissionsAsync(request.SiteId, request.ChannelId, Types.ContentPermissions.View)) { return(Unauthorized()); } var summaries = ContentUtility.ParseSummaries(request.ChannelContentIds); var site = await _siteRepository.GetAsync(request.SiteId); if (site == null) { return(NotFound()); } var channel = await _channelRepository.GetAsync(request.ChannelId); if (channel == null) { return(this.Error("无法确定内容对应的栏目")); } var columnsManager = new ColumnsManager(_databaseManager, _pathManager); var columns = await columnsManager.GetContentListColumnsAsync(site, channel, ColumnsManager.PageType.Contents); var contentInfoList = new List <Content>(); var calculatedContentInfoList = new List <Content>(); if (summaries.Count == 0) { var ccIds = await _contentRepository.GetSummariesAsync(site, channel, channel.IsAllContents); var count = ccIds.Count; var pages = Convert.ToInt32(Math.Ceiling((double)count / site.PageSize)); if (pages == 0) { pages = 1; } if (count > 0) { for (var page = 1; page <= pages; page++) { var offset = site.PageSize * (page - 1); var limit = site.PageSize; var pageCcIds = ccIds.Skip(offset).Take(limit).ToList(); var sequence = offset + 1; foreach (var channelContentId in pageCcIds) { var contentInfo = await _contentRepository.GetAsync(site, channelContentId.ChannelId, channelContentId.Id); if (contentInfo == null) { continue; } if (!request.IsAllCheckedLevel) { var checkedLevel = contentInfo.CheckedLevel; if (contentInfo.Checked) { checkedLevel = site.CheckContentLevel; } if (!request.CheckedLevelKeys.Contains(checkedLevel)) { continue; } } if (!request.IsAllDate) { if (contentInfo.AddDate < request.StartDate || contentInfo.AddDate > request.EndDate) { continue; } } contentInfoList.Add(contentInfo); calculatedContentInfoList.Add(await columnsManager.CalculateContentListAsync(sequence++, site, request.ChannelId, contentInfo, columns)); } } } } else { var sequence = 1; foreach (var channelContentId in summaries) { var contentInfo = await _contentRepository.GetAsync(site, channelContentId.ChannelId, channelContentId.Id); if (contentInfo == null) { continue; } if (!request.IsAllCheckedLevel) { var checkedLevel = contentInfo.CheckedLevel; if (contentInfo.Checked) { checkedLevel = site.CheckContentLevel; } if (!request.CheckedLevelKeys.Contains(checkedLevel)) { continue; } } if (!request.IsAllDate) { if (contentInfo.AddDate < request.StartDate || contentInfo.AddDate > request.EndDate) { continue; } } contentInfoList.Add(contentInfo); calculatedContentInfoList.Add(await columnsManager.CalculateContentListAsync(sequence++, site, request.ChannelId, contentInfo, columns)); } } var downloadUrl = string.Empty; if (contentInfoList.Count > 0) { if (request.ExportType == "zip") { var fileName = $"{channel.ChannelName}.zip"; var filePath = _pathManager.GetTemporaryFilesPath(fileName); var caching = new CacheUtils(_cacheManager); var exportObject = new ExportObject(_pathManager, _databaseManager, caching, site); contentInfoList.Reverse(); if (await exportObject.ExportContentsAsync(filePath, contentInfoList)) { downloadUrl = _pathManager.GetTemporaryFilesUrl(fileName); } } else if (request.ExportType == "excel") { var exportColumnNames = request.IsAllColumns ? columns.Select(x => x.AttributeName).ToList() : request.ColumnNames; var fileName = $"{channel.ChannelName}.csv"; var filePath = _pathManager.GetTemporaryFilesPath(fileName); var excelObject = new ExcelObject(_databaseManager, _pathManager); await excelObject.CreateExcelFileForContentsAsync(filePath, site, channel, calculatedContentInfoList, exportColumnNames); downloadUrl = _pathManager.GetTemporaryFilesUrl(fileName); } } return(new SubmitResult { Value = downloadUrl, IsSuccess = !string.IsNullOrEmpty(downloadUrl) }); }
public ExcelRow(int index, ExcelTable table) { _list = new SortedList(); _row = new ExcelObject("Row"); _table = table; if ((table != null) && (_row != null)) { _row.IsNewLine = true; ExcelObject _table_object = _table.GetTableObject(); if (_table_object != null) _table_object.AddObject(index, ref _row); }; }
public ExcelRow(ExcelTable table) { _list = new SortedList(); _row = new ExcelObject("Row"); _table = table; if (_row != null) { _row.IsNewLine = true; }; }