private static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart, DefinedNames definedNamesCol)
{
OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart, Encoding.ASCII);
writer.WriteStartElement(new Worksheet());
// To demonstrate how to set column-widths in Excel, here's how to set the width of all columns to our default of "25":
UInt32 inx = 1;
writer.WriteStartElement(new Columns());
foreach (DataColumn dc in dt.Columns)
{
writer.WriteElement(new Column {
Min = inx, Max = inx, CustomWidth = true, Width = DEFAULT_COLUMN_WIDTH
});
inx++;
}
writer.WriteEndElement();
writer.WriteStartElement(new SheetData());
string cellValue = "";
string cellReference = "";
// Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable.
//
// We'll also create an array, showing which type each column of data is (Text or Numeric), so when we come to write the actual
// cells of data, we'll know if to write Text values or Numeric cell values.
int numberOfColumns = dt.Columns.Count;
bool[] IsIntegerColumn = new bool[numberOfColumns];
bool[] IsFloatColumn = new bool[numberOfColumns];
bool[] IsDateColumn = new bool[numberOfColumns];
string[] excelColumnNames = new string[numberOfColumns];
for (int n = 0; n < numberOfColumns; n++)
{
excelColumnNames[n] = GetExcelColumnName(n);
}
//
// Create the Header row in our Excel Worksheet
// We'll set the row-height to 20px, and (using the "AppendHeaderTextCell" function) apply some formatting to the cells.
//
uint rowIndex = 1;
writer.WriteStartElement(new Row {
RowIndex = rowIndex, Height = 20, CustomHeight = true
});
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
DataColumn col = dt.Columns[colInx];
AppendHeaderTextCell(excelColumnNames[colInx] + "1", col.ColumnName, writer);
IsIntegerColumn[colInx] = (col.DataType.FullName.StartsWith("System.Int"));
IsFloatColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Double") || (col.DataType.FullName == "System.Single");
IsDateColumn[colInx] = (col.DataType.FullName == "System.DateTime");
// Uncomment the following lines, for an example of how to create some Named Ranges in your Excel file
#if FALSE
// For each column of data in this worksheet, let's create a Named Range, showing where there are values in this column
// eg "NamedRange_UserID" = "Drivers!$A2:$A6"
// "NamedRange_Surname" = "Drivers!$B2:$B6"
string columnHeader = col.ColumnName.Replace(" ", "_");
string NamedRange = string.Format("{0}!${1}2:${2}{3}", worksheetName, excelColumnNames[colInx], excelColumnNames[colInx], dt.Rows.Count + 1);
DefinedName definedName = new DefinedName()
{
Name = "NamedRange_" + columnHeader,
Text = NamedRange
};
definedNamesCol.Append(definedName);
#endif
}
writer.WriteEndElement(); // End of header "Row"
//
// Now, step through each row of data in our DataTable...
//
double cellFloatValue = 0;
CultureInfo ci = new CultureInfo("en-US");
foreach (DataRow dr in dt.Rows)
{
// ...create a new row, and append a set of this row's data to it.
++rowIndex;
writer.WriteStartElement(new Row {
RowIndex = rowIndex
});
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
cellValue = dr.ItemArray[colInx].ToString();
cellValue = ReplaceHexadecimalSymbols(cellValue);
cellReference = excelColumnNames[colInx] + rowIndex.ToString();
// Create cell with data
if (IsIntegerColumn[colInx] || IsFloatColumn[colInx])
{
// For numeric cells without any decimal places.
// If this numeric value is NULL, then don't write anything to the Excel file.
cellFloatValue = 0;
bool bIncludeDecimalPlaces = IsFloatColumn[colInx];
if (double.TryParse(cellValue, out cellFloatValue))
{
cellValue = cellFloatValue.ToString(ci);
AppendNumericCell(cellReference, cellValue, bIncludeDecimalPlaces, writer);
}
}
else if (IsDateColumn[colInx])
{
// For date values, we save the value to Excel as a number, but need to set the cell's style to format
// it as either a date or a date-time.
DateTime dateValue;
if (DateTime.TryParse(cellValue, out dateValue))
{
AppendDateCell(cellReference, dateValue, writer);
}
else
{
// This should only happen if we have a DataColumn of type "DateTime", but this particular value is null/blank.
AppendTextCell(cellReference, cellValue, writer);
}
}
else
{
// For text cells, just write the input data straight out to the Excel file.
AppendTextCell(cellReference, cellValue, writer);
}
}
writer.WriteEndElement(); // End of Row
}
writer.WriteEndElement(); // End of SheetData
writer.WriteEndElement(); // End of worksheet
writer.Close();
}
private void GenerateWorksheetAContentSubsectorPollutionSourceFieldSheet(WorksheetPart worksheetPart, Workbook workbook, string SheetName, int SheetOrdinal, bool ActivePollutionSource)
{
BaseEnumService baseEnumService = new BaseEnumService(_TaskRunnerBaseService._BWObj.appTaskModel.Language);
TVItemService tvItemService = new TVItemService(_TaskRunnerBaseService._BWObj.appTaskModel.Language, _TaskRunnerBaseService._User);
PolSourceSiteService polSourceSiteService = new PolSourceSiteService(_TaskRunnerBaseService._BWObj.appTaskModel.Language, _TaskRunnerBaseService._User);
//PolSourceObservationService polSourceObservationService = new PolSourceObservationService(_TaskRunnerBaseService._BWObj.appTaskModel.Language, _TaskRunnerBaseService._User);
//MWQMSubsectorService mwqmSubsectorService = new MWQMSubsectorService(_TaskRunnerBaseService._BWObj.appTaskModel.Language, _TaskRunnerBaseService._User);
MergeCells mergeCells = new MergeCells();
Row row = new Row();
Cell cell = new Cell();
Hyperlinks hyperlinks = new Hyperlinks();
string Id = XlsxBase.sheetNameAndIDList[SheetOrdinal].SheetID;
XlsxBase.CurrentColumn = 0;
XlsxBase.CurrentRow = 0;
XlsxBase.CurrentColumnProp = 0;
Worksheet worksheet = new Worksheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac"
}
};
worksheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
worksheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
worksheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
SheetViews sheetViews = new SheetViews();
SheetView sheetView = new SheetView()
{
TabSelected = true, WorkbookViewId = (UInt32Value)0U
};
Selection selection = new Selection()
{
ActiveCell = "A1", SequenceOfReferences = new ListValue <StringValue>()
{
InnerText = "A1"
}
};
sheetView.Append(selection);
sheetViews.Append(sheetView);
SheetFormatProperties sheetFormatProperties = new SheetFormatProperties()
{
DefaultRowHeight = 15D, DyDescent = 0.25D
};
Columns columns = new Columns();
List <double?> columnWidthList = new List <double?>()
{
7D, 10D, 90D, 12D, 12D, 10D, 14D, 20D
};
foreach (double?width in columnWidthList)
{
Column colum = XlsxBase.AddColumnProp(columns, width);
}
SheetData sheetData = new SheetData();
TVItemModel tvItemModelSubsector = tvItemService.GetTVItemModelWithTVItemIDDB(_TaskRunnerBaseService._BWObj.appTaskModel.TVItemID);
//MWQMSubsectorModel mwqmSubsectorModel = mwqmSubsectorService.GetMWQMSubsectorModelWithMWQMSubsectorTVItemIDDB(_TaskRunnerBaseService._BWObj.appTaskModel.TVItemID);
XlsxBase.CurrentRowHeight = 28D;
XlsxBase.CurrentFontSize = 24;
XlsxBase.CurrentHorizontalAlignmentValue = HorizontalAlignmentValues.Center;
XlsxBase.CurrentBorderStyleValue = BorderStyleValues.Thin;
XlsxBase.CurrentBottomBorder = true;
row = XlsxBase.AddRow();
string URL = _TaskRunnerBaseService.GetUrlFromTVItem(tvItemModelSubsector);
XlsxBase.CurrentFontColor = System.Drawing.Color.Blue;
cell = XlsxBase.AddCellHyperlink(hyperlinks, row, tvItemModelSubsector.TVText, URL);
XlsxBase.CurrentFontColor = null;
//cell = XlsxBase.AddCellString(row, tvItemModel.TVText);
cell = XlsxBase.AddCellString(row, null);
sheetData.Append(row);
MergeCell mergeCell = new MergeCell()
{
Reference = "A" + XlsxBase.CurrentRow.ToString() + ":H" + XlsxBase.CurrentRow.ToString()
};
mergeCells.Append(mergeCell);
List <TVItemModel> tvItemModelPolSourceList = tvItemService.GetChildrenTVItemModelListWithTVItemIDAndTVTypeDB(_TaskRunnerBaseService._BWObj.appTaskModel.TVItemID, TVTypeEnum.PolSourceSite);
XlsxBase.CurrentRowHeight = 24D;
XlsxBase.CurrentFontSize = 18;
XlsxBase.CurrentBorderStyleValue = BorderStyleValues.Thin;
XlsxBase.CurrentBottomBorder = true;
XlsxBase.CurrentHorizontalAlignmentValue = HorizontalAlignmentValues.Center;
row = XlsxBase.AddRow();
XlsxBase.CurrentFontColor = null;
cell = XlsxBase.AddCellString(row, "Site");
cell = XlsxBase.AddCellString(row, "Type");
cell = XlsxBase.AddCellString(row, "Observation");
cell = XlsxBase.AddCellString(row, "Lat");
cell = XlsxBase.AddCellString(row, "Lng");
cell = XlsxBase.AddCellString(row, "Active");
cell = XlsxBase.AddCellString(row, "Update");
cell = XlsxBase.AddCellString(row, "Civic Address");
sheetData.Append(row);
XlsxBase.CurrentRowHeight = 160D;
XlsxBase.CurrentFontSize = 12;
XlsxBase.CurrentHorizontalAlignmentValue = HorizontalAlignmentValues.Left;
List <PolSourceSiteModel> polSourceSiteModelList = polSourceSiteService.GetPolSourceSiteModelListWithSubsectorTVItemIDDB(tvItemModelSubsector.TVItemID);
int countPolSourceSite = 0;
foreach (PolSourceSiteModel polSourceSiteModel in polSourceSiteModelList.OrderBy(c => c.Site).ToList())
{
bool IsActive = (from c in tvItemModelPolSourceList
where c.TVItemID == polSourceSiteModel.PolSourceSiteTVItemID
select c.IsActive).FirstOrDefault();
if (ActivePollutionSource != IsActive)
{
continue;
}
countPolSourceSite += 1;
PolSourceObservationModel polSourceObservationModel = polSourceSiteService._PolSourceObservationService.GetPolSourceObservationModelLatestWithPolSourceSiteTVItemIDDB(polSourceSiteModel.PolSourceSiteTVItemID);
List <MapInfoPointModel> mapInfoPointModelList = polSourceSiteService._MapInfoService._MapInfoPointService.GetMapInfoPointModelListWithTVItemIDAndTVTypeAndMapInfoDrawTypeDB(polSourceSiteModel.PolSourceSiteTVItemID, TVTypeEnum.PolSourceSite, MapInfoDrawTypeEnum.Point);
row = XlsxBase.AddRow();
if (countPolSourceSite % 5 == 0)
{
XlsxBase.CurrentBorderStyleValue = BorderStyleValues.Thin;
XlsxBase.CurrentBottomBorder = true;
}
else
{
XlsxBase.CurrentBorderStyleValue = null;
XlsxBase.CurrentBottomBorder = false;
}
XlsxBase.CurrentHorizontalAlignmentValue = HorizontalAlignmentValues.Center;
cell = XlsxBase.AddCellString(row, polSourceSiteModel.Site.ToString());
if (polSourceObservationModel != null)
{
List <PolSourceObservationIssueModel> polSourceObservationIssueModelList = polSourceSiteService._PolSourceObservationService._PolSourceObservationIssueService.GetPolSourceObservationIssueModelListWithPolSourceObservationIDDB(polSourceObservationModel.PolSourceObservationID);
string SelectedObservation = "Selected: \r\n";
int PolSourceObsInfoInt = 0;
foreach (PolSourceObservationIssueModel polSourceObservationIssueModel in polSourceObservationIssueModelList)
{
List <int> observationInfoList = polSourceObservationIssueModel.ObservationInfo.Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries).ToList().Select(c => int.Parse(c)).ToList();
PolSourceObsInfoInt = observationInfoList.Where(c => (c > 10500 && c < 10599) || (c > 15200 && c < 15299)).FirstOrDefault();
foreach (PolSourceObsInfoEnum polSourceObsInfo in polSourceObservationIssueModel.PolSourceObsInfoList)
{
SelectedObservation += baseEnumService.GetEnumText_PolSourceObsInfoReportEnum(polSourceObsInfo);
}
SelectedObservation += "\r\n\r\n";
}
if (PolSourceObsInfoInt > 0)
{
cell = XlsxBase.AddCellString(row, baseEnumService.GetEnumText_PolSourceObsInfoEnum((PolSourceObsInfoEnum)PolSourceObsInfoInt));
}
else
{
cell = XlsxBase.AddCellString(row, "Empty");
}
}
XlsxBase.CurrentHorizontalAlignmentValue = HorizontalAlignmentValues.Left;
XlsxBase.WrapText = true;
if (polSourceObservationModel != null)
{
List <PolSourceObservationIssueModel> polSourceObservationIssueModelList = polSourceSiteService._PolSourceObservationService._PolSourceObservationIssueService.GetPolSourceObservationIssueModelListWithPolSourceObservationIDDB(polSourceObservationModel.PolSourceObservationID);
string SelectedObservation = "Selected: \r\n";
int PolSourceObsInfoInt = 0;
foreach (PolSourceObservationIssueModel polSourceObservationIssueModel in polSourceObservationIssueModelList)
{
List <int> observationInfoList = polSourceObservationIssueModel.ObservationInfo.Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries).ToList().Select(c => int.Parse(c)).ToList();
foreach (PolSourceObsInfoEnum polSourceObsInfo in polSourceObservationIssueModel.PolSourceObsInfoList)
{
SelectedObservation += baseEnumService.GetEnumText_PolSourceObsInfoReportEnum(polSourceObsInfo);
}
SelectedObservation += "\r\n\r\n";
}
if (PolSourceObsInfoInt > 0)
{
cell = XlsxBase.AddCellString(row, "Written: \r\n" + (string.IsNullOrWhiteSpace(polSourceObservationModel.Observation_ToBeDeleted) ? "" : polSourceObservationModel.Observation_ToBeDeleted.ToString()) + "\r\n\r\n"
+ SelectedObservation);
}
else
{
cell = XlsxBase.AddCellString(row, "Written: \r\n\r\n" + SelectedObservation);
}
}
XlsxBase.CurrentHorizontalAlignmentValue = HorizontalAlignmentValues.Center;
Alignment alignment1 = new Alignment()
{
WrapText = true
};
if (mapInfoPointModelList.Count > 0)
{
cell = XlsxBase.AddCellString(row, mapInfoPointModelList[0].Lat.ToString("F5"));
cell = XlsxBase.AddCellString(row, mapInfoPointModelList[0].Lng.ToString("F5"));
}
else
{
cell = XlsxBase.AddCellString(row, "");
cell = XlsxBase.AddCellString(row, "");
}
TVItemModel tvItemModelPolSource = tvItemService.GetTVItemModelWithTVItemIDDB(polSourceSiteModel.PolSourceSiteTVItemID);
cell = XlsxBase.AddCellString(row, tvItemModelPolSource.IsActive.ToString());
cell = XlsxBase.AddCellString(row, polSourceObservationModel.ObservationDate_Local.ToString("yyyy MMM dd"));
cell = XlsxBase.AddCellString(row, "empty");
sheetData.Append(row);
}
if (countPolSourceSite > 0)
{
DefinedNames definedNames1 = new DefinedNames();
DefinedName definedName1 = new DefinedName()
{
Name = "_xlnm._FilterDatabase", LocalSheetId = (UInt32Value)0U, Hidden = true
};
definedName1.Text = "'" + SheetName + "'" + "!$A$2:$H$" + (countPolSourceSite + 2).ToString();
definedNames1.Append(definedName1);
workbook.Append(definedNames1);
AutoFilter autoFilter = new AutoFilter()
{
Reference = "a2:B" + (countPolSourceSite + 2).ToString()
};
worksheet.Append(autoFilter);
}
PageMargins pageMargins = new PageMargins()
{
Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D
};
PageSetup pageSetup = new PageSetup()
{
Orientation = OrientationValues.Portrait, Id = "rId" + SheetOrdinal.ToString()
};
worksheet.Append(sheetViews);
worksheet.Append(sheetFormatProperties);
if (columns.ChildElements.Count > 0)
{
worksheet.Append(columns);
}
worksheet.Append(sheetData);
mergeCells.Count = (UInt32Value)((UInt32)mergeCells.ChildElements.Count);
if (mergeCells.ChildElements.Count > 0)
{
worksheet.Append(mergeCells);
}
if (XlsxBase.UsedHyperlinkList.Count > 0)
{
worksheet.Append(hyperlinks);
}
worksheet.Append(pageMargins);
worksheet.Append(pageSetup);
worksheetPart.Worksheet = worksheet;
}
