/// <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);
}
