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