/// <summary> /// Creates column header and sets the style /// </summary> /// <param name="worksheetModel">WorksheetModel object</param> /// <param name="worksheet">ExcelWorksheet object</param> protected static void CreateColumnHeader(WorksheetModel worksheetModel, ExcelWorksheet worksheet) { int columnCount = 1; int rowNumberForColumnHeader = worksheetModel.DataColumnHeaderModel.StartRow; //string cellLetter = new String(worksheetModel.DataColumnHeaderModel.ColumnStartCell.Where(Char.IsLetter).ToArray()); int columnIndexValue = Utils.GetColumnNumber(worksheetModel.DataColumnHeaderModel.ColumnStartCell); string lastColumnCell = string.Empty; foreach (var column in worksheetModel.DataColumnHeaderModel.Columns) { string columnLetter = string.Empty; columnLetter = Utils.GetExcelColumnName(columnIndexValue); worksheet.Cells[columnLetter + rowNumberForColumnHeader.ToString()].Value = column.ColumnName; //Set column width worksheet.Column(columnCount).Width = column.ColumnWidth; lastColumnCell = columnLetter; columnCount++; columnIndexValue++; } string worksheetHeaderCellRange = string.Format("{0}{1}:{2}{3}", worksheetModel.DataColumnHeaderModel.ColumnStartCell, worksheetModel.DataColumnHeaderModel.StartRow, lastColumnCell, rowNumberForColumnHeader); worksheet.Cells[worksheetHeaderCellRange].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[worksheetHeaderCellRange].Style.Fill.BackgroundColor.SetColor(worksheetModel.DataColumnHeaderModel.BackgroundColor); worksheet.Cells[worksheetHeaderCellRange].Style.Font.Bold = worksheetModel.DataColumnHeaderModel.Bold; worksheet.Cells[worksheetHeaderCellRange].Style.Font.Color.SetColor(worksheetModel.DataColumnHeaderModel.FontColor); }
public void StartServerSync() { var pipename = $"worksheet-{Guid.NewGuid()}"; this.Client = new WorksheetModel(pipename, document.FilePath, ThreadModel); this.Client.CellChanged += OnCellChanged; this.Client.Start(); this.Server = new WorksheetServer(pipename, document.FilePath); this.Server.Start(); }
/// <summary> /// Primary functionality to set data to cells. Calls SetBorder to set all the borders /// </summary> /// <param name="worksheetModel">Model for worsheet</param> /// <param name="worksheet">ExcelWorksheet object</param> /// <param name="dataTable">Table containing data to export</param> /// <returns></returns> protected static int AddDataToExcel(WorksheetModel worksheetModel, ExcelWorksheet worksheet, DataTable dataTable) { int row = worksheetModel.WorksheetDataStartRow; SetBorder(worksheet, row, dataTable.Rows.Count, dataTable.Columns.Count); //Assign data to cells foreach (DataRow dr in worksheetModel.WorksheetData.Rows) { int columIndex = 1; foreach (var item in worksheetModel.WorksheetData.Columns) { worksheet.Cells[row, columIndex].Value = dr[item.ToString()]; columIndex++; } row++; } return(row); }
// Sample code for inserting a work sheet. protected override void OnCellDoubleClick(GridCellClickEventArgs e) { WorkbookForm form = FindForm() as WorkbookForm; if (form != null) { WorkbookModel workbook = form.WorkbookView.Workbook; GridModel sheet3 = new SampleGridModel(); SampleGrid.SetupGridModel(sheet3); WorksheetModel workSheetModel = new WorksheetModel(workbook, "Sheet 3", sheet3); workbook.Worksheets.Add(workSheetModel); form.WorkbookView.ActivePage = form.WorkbookView.TabBarPages[workbook.Worksheets.Count - 1]; } base.OnCellDoubleClick(e); }
private void btnOk_Click(object sender, EventArgs e) { var n = treeView1.SelectedNode; if (n != null) { SelectedWorksheet = n.Tag as WorksheetModel; } if (SelectedWorksheet == null) { MessageBox.Show("Please select a worksheet"); return; } else { SelectedSpreadsheet = n.Parent.Tag as SpreadsheetModel; } DialogResult = DialogResult.OK; }
private void OnLoadedCommand(ReoGridControl reoGridControl) { _WorksheetModel = new WorksheetModel(reoGridControl, typeof(Book), _Books); _WorksheetModel.OnBeforeChangeRecord += WorksheetModel_OnBeforeChangeRecord; }
static void Main(string[] args) { List <Customer> customers = new List <Customer>(); ExcelExport.Export.Utils.OutputDir = new DirectoryInfo($"{AppDomain.CurrentDomain.BaseDirectory}SampleApp"); using (SqlConnection sqlConn = new SqlConnection(@"Data Source=JAYANTGURU-PC\SQLSERVER2017;Initial Catalog=Practice;Persist Security Info=True;User ID=sa;Password=guru@000")) { sqlConn.Open(); using (SqlCommand sqlCmd = new SqlCommand(@"SELECT [CustomerID] ,[CompanyName] ,[ContactName] ,[ContactTitle] ,[Address] ,[City] ,[Region] ,[PostalCode] ,[Country] ,[Phone] ,[Fax] FROM [Practice].[dbo].[Customers]", sqlConn)) { using (SqlDataReader sqlReader = sqlCmd.ExecuteReader()) { // get the data and fill rows 5 onwards while (sqlReader.Read()) { Customer customer = new Customer(); customer.CustomerID = sqlReader["CustomerID"] != DBNull.Value ? Convert.ToInt32(sqlReader["CustomerID"]) : 0; customer.CompanyName = sqlReader["CompanyName"] != DBNull.Value ? sqlReader["CompanyName"].ToString() : string.Empty; customer.ContactName = sqlReader["ContactName"] != DBNull.Value ? sqlReader["ContactName"].ToString() : string.Empty; customer.ContactTitle = sqlReader["ContactTitle"] != DBNull.Value ? sqlReader["ContactTitle"].ToString() : string.Empty; customer.Address = sqlReader["Address"] != DBNull.Value ? sqlReader["Address"].ToString() : string.Empty; customer.City = sqlReader["City"] != DBNull.Value ? sqlReader["City"].ToString() : string.Empty; customer.Region = sqlReader["Region"] != DBNull.Value ? sqlReader["Region"].ToString() : string.Empty; customer.PostalCode = sqlReader["PostalCode"] != DBNull.Value ? sqlReader["PostalCode"].ToString() : string.Empty; customer.Country = sqlReader["Country"] != DBNull.Value ? sqlReader["Country"].ToString() : string.Empty; customer.Phone = sqlReader["Phone"] != DBNull.Value ? sqlReader["Phone"].ToString() : string.Empty; customer.Fax = sqlReader["Fax"] != DBNull.Value ? sqlReader["Fax"].ToString() : string.Empty; customers.Add(customer); } sqlReader.Close(); } } sqlConn.Close(); } DataTable dataTable = Utils.ToDataTable <Customer>(customers); WorkbookModel workbook = new WorkbookModel(); workbook.FilePath = ConfigurationManager.AppSettings["ExcelFilePath"]; workbook.Disclaimer = ConfigurationManager.AppSettings["Disclaimer"]; workbook.ImageModel.ImagePath = ConfigurationManager.AppSettings["LogoPath"]; WorksheetModel worksheetModel = new WorksheetModel(); worksheetModel.WorksheetData = dataTable; foreach (var item in dataTable.Columns) { ColumnModel columnModel = new ColumnModel(); columnModel.ColumnName = item.ToString(); worksheetModel.DataColumnHeaderModel.Columns.Add(columnModel); } WorksheetHeaderModel worksheetHeaderModel = new WorksheetHeaderModel(); worksheetHeaderModel.HeaderFontSize = 15; worksheetHeaderModel.HeaderText = "Transaction Detailed Report"; worksheetHeaderModel.MergeHeader = true; worksheetModel.WorksheetHeaderModels.Add(worksheetHeaderModel); WorksheetHeaderModel worksheetHeaderModel2 = new WorksheetHeaderModel(); worksheetHeaderModel2.HeaderFontSize = 15; worksheetHeaderModel2.HeaderText = "Report Dated 01-May-2018 to 31-July-2018"; worksheetHeaderModel2.MergeHeader = true; worksheetModel.WorksheetHeaderModels.Add(worksheetHeaderModel2); WorksheetHeaderModel worksheetHeaderModel3 = new WorksheetHeaderModel(); worksheetHeaderModel3.HeaderFontSize = 15; worksheetHeaderModel3.HeaderText = "AMC: Aditya Birla Mutual Funds"; worksheetHeaderModel3.MergeHeader = true; worksheetModel.WorksheetHeaderModels.Add(worksheetHeaderModel3); WorksheetHeaderModel worksheetHeaderModel4 = new WorksheetHeaderModel(); worksheetHeaderModel4.HeaderFontSize = 15; worksheetHeaderModel4.HeaderText = "Asset Type: Equity"; worksheetHeaderModel4.MergeHeader = true; worksheetHeaderModel4.HorizontalAlignment = HorizontalAlignment.Left; worksheetModel.WorksheetHeaderModels.Add(worksheetHeaderModel4); WorksheetHeaderModel worksheetHeaderModel5 = new WorksheetHeaderModel(); worksheetHeaderModel5.HeaderFontSize = 10; worksheetHeaderModel5.HeaderText = "Sub Type: Mid Cap"; worksheetHeaderModel5.MergeHeader = true; worksheetHeaderModel5.HorizontalAlignment = HorizontalAlignment.Left; worksheetModel.WorksheetHeaderModels.Add(worksheetHeaderModel5); workbook.WorksheetModels.Add(worksheetModel); Excel.CreateExcel(workbook); }