private void InsertDataTableRows(Worksheet worksheet, ExcelDataTable excelDataTable) { int rowCount = excelDataTable.RowCount; int colCount = excelDataTable.ColumnCount; int startRowIndex = excelDataTable.StartRowIndex; int startColIndex = excelDataTable.StartColumnIndex; int colIndex; for (int rowIndex = 1; rowIndex <= rowCount; rowIndex++) { for (colIndex = 0; colIndex < colCount; colIndex++) { string result = excelDataTable.GetCellValueAt(rowIndex - 1, colIndex); double number; if (Double.TryParse(result, out number)) { Range numberRange = worksheet.Cells[startRowIndex + rowIndex, startColIndex + colIndex]; numberRange.NumberFormat = "0.0000"; } worksheet.Cells[startRowIndex + rowIndex, startColIndex + colIndex] = result != "" ? result : "=NA()"; } } }
private void InsertProtocolCommentsDataTable(ExcelTemplate excelTemplate, ExcelWorksheet worksheet, ProtocolTemplate title) { DataTable dataTable = QProtocolComments.SelectItemsToDataTable(title); ExcelDataTable excelDataTable = CreateNewExcelDataTable("CommentsTable" + title.TemplateID, dataTable, 5, 4); InsertExcelDataTable(excelTemplate, worksheet, excelDataTable); FormatExcelDataTable(worksheet, excelDataTable); }
private void InsertDataTableColumns(Worksheet worksheet, ExcelDataTable excelDataTable) { int rowIndex = excelDataTable.StartRowIndex; int colCount = excelDataTable.ColumnCount; for (int i = 0; i < colCount; i++) { worksheet.Cells[rowIndex, i + excelDataTable.StartColumnIndex] = excelDataTable.GetColumnName(i); } }
private void InsertProtocolEventsDataTable(ExcelTemplate excelTemplate, ExcelWorksheet worksheet, ProtocolTemplate title) { DataTable dataTable = QProtocolActivities.SelectItemsToDataTable(this.protocolRequest.ID, title.TemplateID); ExcelDataTable eventsDataTable = CreateNewExcelDataTable("EventsTable" + title.TemplateID, dataTable, 1, 4); InsertExcelDataTable(excelTemplate, worksheet, eventsDataTable); FormatExcelDataTable(worksheet, eventsDataTable); }
private ExcelDataTable CreateNewExcelDataTable(string tableName, System.Data.DataTable dataTable, int startColumnIndex, int startRowIndex) { ExcelDataTable excelDataTable = new ExcelDataTable(); excelDataTable.SetDataTable(dataTable); excelDataTable.StartColumnIndex = startColumnIndex; excelDataTable.StartRowIndex = startRowIndex; excelDataTable.SetTopLeft(); excelDataTable.SetBottomRight(); excelDataTable.TableName = tableName; excelDataTable.TableStyleName = "TableStyleLight11"; return(excelDataTable); }
private void SetDataTableRange(Worksheet worksheet, ExcelDataTable excelDataTable) { Range dataTableRange = worksheet.get_Range(excelDataTable.TopLeft + ":" + excelDataTable.BottomRight); worksheet.ListObjects.AddEx(XlListObjectSourceType.xlSrcRange, dataTableRange, Type.Missing, XlYesNoGuess.xlNo, Type.Missing).Name = excelDataTable.TableName; ListObject listObj = worksheet.ListObjects.get_Item(excelDataTable.TableName); worksheet.ListObjects.get_Item(excelDataTable.TableName).TableStyle = excelDataTable.TableStyleName; var type = listObj.GetType(); if (type.GetMethod("ShowAutoFilterDropDown") != null) { worksheet.ListObjects.get_Item(excelDataTable.TableName).ShowAutoFilterDropDown = false; } }
public void InsertDataTable(Worksheet worksheet, ExcelDataTable excelDataTable) { SetDataTableRange(worksheet, excelDataTable); InsertDataTableColumns(worksheet, excelDataTable); InsertDataTableRows(worksheet, excelDataTable); }
public void SetDataTableColumnWidth(Worksheet worksheet, ExcelDataTable excelDataTable, int width) { Range dataTableRange = worksheet.get_Range(excelDataTable.TopLeft + ":" + excelDataTable.BottomRight); dataTableRange.ColumnWidth = width; }
public static void SetDataTableColumnAutoFit(Worksheet worksheet, ExcelDataTable excelDataTable) { Range dataTableRange = worksheet.get_Range(excelDataTable.TopLeft + ":" + excelDataTable.BottomRight); dataTableRange.EntireColumn.AutoFit(); }
private void FormatExcelDataTable(ExcelWorksheet worksheet, ExcelDataTable excelDataTable) { //ExcelTemplate.SetWrapText(worksheet, excelDataTable.TopLeft, excelDataTable.BottomRight); ExcelTemplate.SetTopAlignLeft(worksheet, excelDataTable.TopLeft, excelDataTable.BottomRight); ExcelTemplate.SetDataTableColumnAutoFit(worksheet, excelDataTable); }
private void InsertExcelDataTable(ExcelTemplate excelTemplate, ExcelWorksheet worksheet, ExcelDataTable excelDataTable) { excelTemplate.InsertDataTable(worksheet, excelDataTable); excelTemplate.SetDataTableColumnWidth(worksheet, excelDataTable, 13); }