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