private static void WriteExcelFile(ReportCondition dtCondtion, DataTable dtDetail, SpreadsheetDocument spreadsheet) { // Create the Excel file contents. This function is used when creating an Excel file either writing // to a file, or writing to a MemoryStream. spreadsheet.AddWorkbookPart(); spreadsheet.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook(); // My thanks to James Miera for the following line of code (which prevents crashes in Excel 2010) spreadsheet.WorkbookPart.Workbook.Append(new BookViews(new WorkbookView())); // If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file ! WorkbookStylesPart workbookStylesPart = spreadsheet.WorkbookPart.AddNewPart <WorkbookStylesPart>("rIdStyles"); //Stylesheet stylesheet = new Stylesheet(); //workbookStylesPart.Stylesheet = stylesheet; CustomStylesheet customStyleSheet = new CustomStylesheet(); Stylesheet styles = customStyleSheet;//CustomStylesheet(); styles.Save(workbookStylesPart); // Loop through each of the DataTables in our DataSet, and create a new Excel Worksheet for each. uint worksheetNumber = 1; Sheets sheets = spreadsheet.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets()); // For each worksheet you want to create string worksheetName = "BOC_DetailedUtilization"; // Create worksheet part, and add it to the sheets collection in workbook WorksheetPart newWorksheetPart = spreadsheet.WorkbookPart.AddNewPart <WorksheetPart>(); Sheet sheet = new Sheet() { Id = spreadsheet.WorkbookPart.GetIdOfPart(newWorksheetPart), SheetId = worksheetNumber, Name = worksheetName }; // If you want to define the Column Widths for a Worksheet, you need to do this *before* appending the SheetData // http://social.msdn.microsoft.com/Forums/en-US/oxmlsdk/thread/1d93eca8-2949-4d12-8dd9-15cc24128b10/ sheets.Append(sheet); // Append this worksheet's data to our Workbook, using OpenXmlWriter, to prevent memory problems WriteDataTableToExcelWorksheet(dtCondtion, dtDetail, newWorksheetPart, workbookStylesPart); worksheetNumber++; spreadsheet.WorkbookPart.Workbook.Save(); }
public static bool CreateExcelDocument(ReportCondition dtCondition, DataTable dtDetail, string excelFilename) { try { //When don't add reference WindowsBase, error appear at here using (SpreadsheetDocument document = SpreadsheetDocument.Create(excelFilename, SpreadsheetDocumentType.Workbook)) { WriteExcelFile(dtCondition, dtDetail, document); } //Trace.WriteLine("Successfully created: " + excelFilename); //EventLog EventLogger.WriteLog("Successfully created: Detail_Utilization_Report.xslx", System.Diagnostics.EventLogEntryType.Information); return(true); } catch (Exception ex) { //Trace.WriteLine("Failed, exception thrown: " + ex.Message); //EventLogger.LogException(ex); return(false); } }
private static string SetWeekendTitle(ReportCondition reportCondition) { string result = string.Empty; if (reportCondition.IncludeSaturday && reportCondition.IncludeSunday) { result = "Yes"; } if (!reportCondition.IncludeSaturday && reportCondition.IncludeSunday) { result = @"Sunday"; } if (reportCondition.IncludeSaturday && !reportCondition.IncludeSunday) { result = @"Saturday"; } if (!reportCondition.IncludeSaturday && !reportCondition.IncludeSunday) { result = "No"; } return(result); }
private static void WriteDataTableToExcelWorksheet(ReportCondition dtCondition, DataTable dtDetail, WorksheetPart worksheetPart, WorkbookStylesPart stylesPart) { OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart, Encoding.Unicode); //ASCII writer.WriteStartElement(new Worksheet()); writer.WriteStartElement(new SheetData()); string cellValue = string.Empty; uint rowIndexHeader = 1; // Create the Conditon Report writer.WriteStartElement(new Row { RowIndex = rowIndexHeader++ }); AppendTextCellFormat("A", "BOC Detailed Utilization", 1, stylesPart.Stylesheet, System.Drawing.Color.White, false, 20, true, 9, ref writer); writer.WriteEndElement(); writer.WriteStartElement(new Row { RowIndex = rowIndexHeader++ }); AppendTextCellFormat("A", "Date:", 2, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); AppendTextCellFormat( "B" , string.Format("{0} - {1}", dtCondition.FromDate.ToString("dd.MM.yyyy"), dtCondition.ToDate.AddDays(-1).ToString("dd.MM.yyyy")) , 2, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); writer.WriteEndElement(); writer.WriteStartElement(new Row { RowIndex = rowIndexHeader++ }); AppendTextCellFormat("A", "Location:", 3, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); AppendTextCellFormat("B", dtCondition.LocationPath.ToString(), 3, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); writer.WriteEndElement(); writer.WriteStartElement(new Row { RowIndex = rowIndexHeader++ }); AppendTextCellFormat("A", "Group:", 4, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); AppendTextCellFormat("B", dtCondition.GroupName.ToString(), 4, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); writer.WriteEndElement(); writer.WriteStartElement(new Row { RowIndex = rowIndexHeader++ }); AppendTextCellFormat("A", "Resource:", 5, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); AppendTextCellFormat("B", dtCondition.ResourceName.ToString(), 5, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); writer.WriteEndElement(); writer.WriteStartElement(new Row { RowIndex = rowIndexHeader++ }); AppendTextCellFormat("A", "Method:", 6, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); AppendTextCellFormat("B", dtCondition.Method.ToString(), 6, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); writer.WriteEndElement(); writer.WriteStartElement(new Row { RowIndex = rowIndexHeader++ }); AppendTextCellFormat("A", "Include weekends:", 7, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); AppendTextCellFormat("B", SetWeekendTitle(dtCondition), 7, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); writer.WriteEndElement(); writer.WriteStartElement(new Row { RowIndex = rowIndexHeader++ }); AppendTextCellFormat("A", "Upper threshold (%):", 8, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); AppendTextCellFormat("B", dtCondition.UpperThreshold.ToString(), 8, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); writer.WriteEndElement(); writer.WriteStartElement(new Row { RowIndex = rowIndexHeader++ }); AppendTextCellFormat("A", "Lower threshold (%):", 9, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); AppendTextCellFormat("B", dtCondition.LowerThreshold.ToString(), 9, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); writer.WriteEndElement(); // End of Conditon Report // Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable. int numberOfColumns = dtDetail.Columns.Count; bool[] IsNumericColumn = 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 writer.WriteStartElement(new Row { RowIndex = 11 }); AppendTextCellFormat("A", "Resource", 11, stylesPart.Stylesheet, System.Drawing.Color.White, true, 11, true, 10, ref writer); AppendTextCellFormat("B", "Hours Used", 11, stylesPart.Stylesheet, System.Drawing.Color.White, true, 11, true, 10, ref writer); AppendTextCellFormat("C", "% Used", 11, stylesPart.Stylesheet, System.Drawing.Color.White, true, 11, true, 10, ref writer); AppendTextCellFormat("D", "Flag", 11, stylesPart.Stylesheet, System.Drawing.Color.White, true, 11, true, 9, ref writer); AppendTextCellFormat("E", "Group", 11, stylesPart.Stylesheet, System.Drawing.Color.White, true, 11, true, 9, ref writer); AppendTextCellFormat("F", "Hours Used", 11, stylesPart.Stylesheet, System.Drawing.Color.White, true, 11, true, 9, ref writer); AppendTextCellFormat("G", "% Used", 11, stylesPart.Stylesheet, System.Drawing.Color.White, true, 11, true, 9, ref writer); for (int colInx = 0; colInx < numberOfColumns; colInx++) { DataColumn col = dtDetail.Columns[colInx]; //AppendTextCell(excelColumnNames[colInx] + "1", col.ColumnName, ref writer); IsNumericColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Int32") || (col.DataType.FullName == "System.Double") || (col.DataType.FullName == "System.Single"); IsDateColumn[colInx] = (col.DataType.FullName == "System.DateTime"); } writer.WriteEndElement(); // End of header row // Now, step through each row of data in our DataTable... uint rowIndex = 11; double cellNumericValue = 0; foreach (DataRow dr in dtDetail.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(); // Create cell with data if (IsNumericColumn[colInx]) { cellNumericValue = 0; if (double.TryParse(cellValue, out cellNumericValue)) { if (cellNumericValue >= 0) { cellValue = ((float)cellNumericValue).ToString("0.00"); AppendNumericCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer); } else { cellValue = string.Empty; //AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer); AppendTextCellFormat(excelColumnNames[colInx], cellValue, (int)rowIndex, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, false, 9, ref writer); } } } else if (IsDateColumn[colInx]) { DateTime dtValue = DateTime.Parse(cellValue); string strValue = dtValue.ToShortDateString(); AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), strValue, ref writer); } else if (colInx == 0) { AppendTextCellFormat(excelColumnNames[colInx], cellValue, (int)rowIndex, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, false, 9, ref writer); } else { //AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer); AppendTextCellFormat(excelColumnNames[colInx], cellValue, (int)rowIndex, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); } } writer.WriteEndElement(); // End of Row } writer.WriteEndElement(); // End of SheetData writer.WriteEndElement(); // End of worksheet writer.Close(); }