private static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart) { OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart, Encoding.ASCII); writer.WriteStartElement(new Worksheet()); writer.WriteStartElement(new SheetData()); string cellValue = ""; int numberOfColumns = dt.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); } // uint rowIndex = 1; writer.WriteStartElement(new Row { RowIndex = rowIndex }); for (int colInx = 0; colInx < numberOfColumns; colInx++) { DataColumn col = dt.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(); double cellNumericValue = 0; foreach (DataRow dr in dt.Rows) { ++rowIndex; writer.WriteStartElement(new Row { RowIndex = rowIndex }); for (int colInx = 0; colInx < numberOfColumns; colInx++) { cellValue = dr.ItemArray[colInx].ToString(); cellValue = ReplaceHexadecimalSymbols(cellValue); // Create cell with data if (IsNumericColumn[colInx]) { cellNumericValue = 0; if (double.TryParse(cellValue, out cellNumericValue)) { cellValue = cellNumericValue.ToString(); AppendNumericCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer); } } else if (IsDateColumn[colInx]) { DateTime dtValue; string strValue = ""; if (DateTime.TryParse(cellValue, out dtValue)) { strValue = dtValue.ToShortDateString(); } AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), strValue, ref writer); } else { AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer); } } writer.WriteEndElement(); // End of Row } writer.WriteEndElement(); // End of SheetData writer.WriteEndElement(); // End of worksheet writer.Close(); }
public static void ReadCountryData(ZandraUserPreferences userPreferences) { ObservableCollection <Country> countries = userPreferences.Countries; var fileContent = string.Empty; var filePath = string.Empty; using (System.Windows.Forms.OpenFileDialog openFileDialog = new OpenFileDialog()) { openFileDialog.InitialDirectory = "c:\\"; openFileDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*"; openFileDialog.FilterIndex = 1; openFileDialog.RestoreDirectory = true; if (openFileDialog.ShowDialog() == DialogResult.OK) { //Get the path of specified file filePath = openFileDialog.FileName; ; // Open the document for reading. using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filePath, false)) { bool countryDataFound = false; WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart; WorksheetPart worksheetPart = GetWorksheetPart(workbookPart, "Consolidated Country Data"); SheetData sheetData = null; if (worksheetPart != null) { countryDataFound = true; sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>(); } if (!countryDataFound) { MessageBox.Show("This File Does not contain the correct country data sheet." + "Please choose a different file.", "Invalid File!"); } else { SharedStringTablePart stringTable = workbookPart.GetPartsOfType <SharedStringTablePart>() .FirstOrDefault(); Row r = null; if (sheetData != null) { r = sheetData.Elements <Row>().Where(row => row.RowIndex == 1).First(); } string nameColumn = null; string numberColumn = null; string codeColumn = null; string tailNumberColumn = null; string nationalityColumn = null; //Get Column Letter char[] ch1 = { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' }; foreach (Cell c in r.Elements <Cell>()) { string cellvalue = stringTable.SharedStringTable .ElementAt(int.Parse(c.CellValue.InnerText)).InnerText; if (cellvalue == "Country") { string tempString = c.CellReference; int index = tempString.IndexOfAny(ch1); nameColumn = tempString.Substring(0, index); } else if (cellvalue == "3-Letter") { string tempString = c.CellReference; int index = tempString.IndexOfAny(ch1); codeColumn = tempString.Substring(0, index); } else if (cellvalue == "ISO 3166-1 Number") { string tempString = c.CellReference; int index = tempString.IndexOfAny(ch1); numberColumn = tempString.Substring(0, index); } else if (cellvalue == "Nationality") { string tempString = c.CellReference; int index = tempString.IndexOfAny(ch1); nationalityColumn = tempString.Substring(0, index); } else if (cellvalue == "Tail Number Prefix") { string tempString = c.CellReference; int index = tempString.IndexOfAny(ch1); tailNumberColumn = tempString.Substring(0, index); } } if (nameColumn != null || numberColumn != null || codeColumn != null || tailNumberColumn != null || nationalityColumn != null) { countries.Clear(); foreach (Row row in sheetData) { if (row.RowIndex > 1) { string referenceString = nameColumn + row.RowIndex; if (sheetData.Descendants <Cell>(). Where(c => c.CellReference == referenceString). FirstOrDefault().CellValue.Text != null && sheetData.Descendants <Cell>(). Where(c => c.CellReference == referenceString). FirstOrDefault().CellValue.Text != "") { string cellValue; Cell cell; referenceString = nameColumn + row.RowIndex; string countryName; cell = sheetData.Descendants <Cell>().Where(c => c.CellReference == referenceString).FirstOrDefault(); if (cell.DataType.Value == CellValues.SharedString) { cellValue = cell.CellValue.InnerText; countryName = stringTable.SharedStringTable .ElementAt(int.Parse(cellValue)).InnerText; } else { cellValue = cell.CellValue.InnerText; countryName = cellValue; } referenceString = codeColumn + row.RowIndex; string countryCode; cell = sheetData.Descendants <Cell>().Where(c => c.CellReference == referenceString).FirstOrDefault(); if (cell.DataType == null) { countryCode = null; } else if (cell.DataType.Value == CellValues.SharedString) { cellValue = cell.CellValue.InnerText; countryCode = stringTable.SharedStringTable .ElementAt(int.Parse(cellValue)).InnerText; } else { cellValue = cell.CellValue.InnerText; countryCode = cellValue; } referenceString = nationalityColumn + row.RowIndex; string nationality; cell = sheetData.Descendants <Cell>().Where(c => c.CellReference == referenceString).FirstOrDefault(); if (cell.DataType == null) { nationality = null; } else if (cell.DataType.Value == CellValues.SharedString) { cellValue = cell.CellValue.InnerText; nationality = stringTable.SharedStringTable .ElementAt(int.Parse(cellValue)).InnerText; } else { cellValue = cell.CellValue.InnerText; nationality = cellValue; } referenceString = tailNumberColumn + row.RowIndex; string tailPrefix; cell = sheetData.Descendants <Cell>().Where(c => c.CellReference == referenceString).FirstOrDefault(); if (cell.DataType == null) { tailPrefix = cell.InnerText; } else if (cell.DataType.Value == CellValues.SharedString) { cellValue = cell.CellValue.InnerText; tailPrefix = stringTable.SharedStringTable .ElementAt(int.Parse(cellValue)).InnerText; } else { cellValue = cell.CellValue.InnerText; tailPrefix = cellValue; } referenceString = numberColumn + row.RowIndex; string countryNumber; cell = sheetData.Descendants <Cell>().Where(c => c.CellReference == referenceString).FirstOrDefault(); if (cell.DataType == null) { countryNumber = cell.InnerText; } else if (cell.DataType.Value == CellValues.SharedString) { cellValue = cell.CellValue.InnerText; countryNumber = stringTable.SharedStringTable .ElementAt(int.Parse(cellValue)).InnerText; } else { cellValue = cell.CellValue.InnerText; countryNumber = cellValue; } uint countryISONumber = (uint)Int32.Parse(countryNumber); if (tailPrefix == "#N/A" || tailPrefix == "") { tailPrefix = null; } if (nationality == "#N/A" || nationality == "0" || nationality == "" || nationality == null) { nationality = null; } countries.Add(new Country( countryName, countryCode, nationality, tailPrefix, countryISONumber)); } } } userPreferences.SaveMe(); } else { MessageBox.Show("This File Does not contain the correct ICAO Aifield data sheet." + "Please choose a different file.", "Invalid File!"); } } } } } }
public bool WriteDataCollectors(IEnumerable <DataCollector> dataCollectors, Stream stream) { var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook); WorkbookPart workbook = document.AddWorkbookPart(); workbook.Workbook = new Workbook(); Sheets sheets = document.WorkbookPart.Workbook.AppendChild(new Sheets()); // Create a sheet in the document var data = new SheetData(); WorksheetPart worksheet = workbook.AddNewPart <WorksheetPart>(); worksheet.Worksheet = new Worksheet(data); Sheet sheet = new Sheet() { Id = document.WorkbookPart.GetIdOfPart(worksheet), SheetId = 1, Name = "Case Reports" }; sheets.Append(sheet); uint rowIndex = 0; // Add some headers { var row = new Row { RowIndex = ++rowIndex }; data.Append(row); var headers = new SortedDictionary <string, string> { { "A", "Full Name" }, { "B", "Display Name" }, { "C", "Year of birth" }, { "D", "Sex" }, { "E", "PreferredLanguage" }, { "F", "Lat. / Long." }, { "G", "Region" }, { "H", "District" }, { "I", "Village" }, { "J", "Phonenumbers" }, { "K", "Registered at" }, { "L", "Last Report Recieved At" } }; foreach (var header in headers) { var cell = new Cell { CellReference = header.Key + rowIndex }; row.Append(cell); cell.DataType = new EnumValue <CellValues>(CellValues.String); cell.CellValue = new CellValue(header.Value); } } // Insert data foreach (var dataCollector in dataCollectors.OrderBy(e => e.RegisteredAt)) { var row = new Row { RowIndex = ++rowIndex }; data.Append(row); var fullName = new Cell { CellReference = "A" + rowIndex }; row.Append(fullName); fullName.DataType = new EnumValue <CellValues>(CellValues.String); fullName.CellValue = new CellValue(dataCollector.FullName); var displayName = new Cell { CellReference = "B" + rowIndex }; row.Append(displayName); displayName.DataType = new EnumValue <CellValues>(CellValues.String); displayName.CellValue = new CellValue(dataCollector.DisplayName); var yearOfBirth = new Cell { CellReference = "C" + rowIndex }; row.Append(yearOfBirth); yearOfBirth.DataType = new EnumValue <CellValues>(CellValues.Number); yearOfBirth.CellValue = new CellValue(dataCollector.YearOfBirth.ToString()); var sex = new Cell { CellReference = "D" + rowIndex }; row.Append(sex); sex.DataType = new EnumValue <CellValues>(CellValues.String); sex.CellValue = new CellValue(Enum.GetName(typeof(Sex), dataCollector.Sex)); var preferredLanguage = new Cell { CellReference = "E" + rowIndex }; row.Append(preferredLanguage); preferredLanguage.DataType = new EnumValue <CellValues>(CellValues.String); preferredLanguage.CellValue = new CellValue(Enum.GetName(typeof(Language), dataCollector.PreferredLanguage)); var location = new Cell { CellReference = "F" + rowIndex }; row.Append(location); location.DataType = new EnumValue <CellValues>(CellValues.String); location.CellValue = new CellValue(dataCollector.Location.Latitude.ToString() + ", " + dataCollector.Location.Longitude.ToString()); var region = new Cell { CellReference = "G" + rowIndex }; row.Append(region); region.DataType = new EnumValue <CellValues>(CellValues.String); region.CellValue = new CellValue(dataCollector.Region ?? "Unknown"); var district = new Cell { CellReference = "H" + rowIndex }; row.Append(district); district.DataType = new EnumValue <CellValues>(CellValues.String); district.CellValue = new CellValue(dataCollector.District ?? "Unknown"); var village = new Cell { CellReference = "I" + rowIndex }; row.Append(village); village.DataType = new EnumValue <CellValues>(CellValues.String); village.CellValue = new CellValue(dataCollector.Village ?? "Unknown"); var phoneNumbers = new Cell { CellReference = "J" + rowIndex }; row.Append(phoneNumbers); phoneNumbers.DataType = new EnumValue <CellValues>(CellValues.String); phoneNumbers.CellValue = new CellValue(string.Join(", ", dataCollector.PhoneNumbers.Select(pn => pn.Value))); var registeredAt = new Cell { CellReference = "K" + rowIndex }; row.Append(registeredAt); registeredAt.DataType = new EnumValue <CellValues>(CellValues.Date); registeredAt.CellValue = new CellValue(dataCollector.RegisteredAt); var lastReportRecievedAt = new Cell { CellReference = "L" + rowIndex }; row.Append(lastReportRecievedAt); lastReportRecievedAt.DataType = new EnumValue <CellValues>(CellValues.Date); lastReportRecievedAt.CellValue = new CellValue(DateTimeOffset.UtcNow /* TODO: dataCollector.LastReportRecievedAt ?? dataCollector.RegisteredAt*/); } // Save the document in memory, and serve to client workbook.Workbook.Save(); document.Close(); return(true); }
public void LoadSpreadsheet(int worksheetId, string fileName) { // Create new stopwatch System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch(); // Begin timing stopwatch.Start(); string cellAddress = ""; string sheetName = Path.GetFileName(fileName).Replace(".xlsx", ""); sheetName = Path.GetFileName(fileName).Replace(".xls", ""); savedCells = new Dictionary <string, Cell>(); allCells = new Dictionary <string, Cell>(); string workbookPath = fileName; using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false)) { var sheets = spreadsheetDocument.WorkbookPart.Workbook.Descendants <Sheet>(); int?colIndex = 0; foreach (Sheet sheet in sheets) { WorksheetPart worksheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(sheet.Id); DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet = worksheetPart.Worksheet; SharedStringTablePart sharedStringTablePart1; if (spreadsheetDocument.WorkbookPart.GetPartsCountOfType <SharedStringTablePart>() > 0) { sharedStringTablePart1 = spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First(); } else//This is only needed to compile. If we don't have string table that means there are no strings { sharedStringTablePart1 = spreadsheetDocument.WorkbookPart.AddNewPart <SharedStringTablePart>(); } var rows = worksheet.GetFirstChild <SheetData>().Elements <Row>(); foreach (var r in rows) { if (r.RowIndex <= 30)//30 rows { foreach (DocumentFormat.OpenXml.Spreadsheet.Cell rCell in r.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>()) { colIndex = GetColumnIndex(rCell.CellReference.ToString()); if (colIndex <= 12) { Cell c = new Cell(); cellAddress = rCell.CellReference.ToString().Replace("$", ""); c.readOnly = true;//until updated c.isDerived = false; c.row = int.Parse(r.RowIndex.ToString()); c.column = (int)colIndex; c.cellAddress = cellAddress; c.value = rCell.CellValue.Text; if (cellAddress == "A3") { Console.WriteLine("A3"); } //Add Formulas if (rCell.CellFormula != null) { if (rCell.CellFormula.SharedIndex != null) { c.sharedForumlaIndex = int.Parse(rCell.CellFormula.SharedIndex.ToString()); if (rCell.CellFormula.Text != "") { c.isSharedForumla = true; } } c.fullFormula = rCell.CellFormula.Text; c.formula = "=" + //Add = (string)rCell.CellFormula.Text.Replace("$", ""); //Remove any $ string regex = @"\$?(?:\bXF[A-D]|X[A-E][A-Z]|[A-W][A-Z]{2}|[A-Z]{2}|[A-Z])\$?(?:104857[0-6]|10485[0-6]\d|1048[0-4]\d{2}|104[0-7]\d{3}|10[0-3]\d{4}|[1-9]\d{1,5}|[1-9])d?\b([:\s]\$?(?:\bXF[A-D]|X[A-E][A-Z]|[A-W][A-Z]{2}|[A-Z]{2}|[A-Z])\$?(?:104857[0-6]|10485[0-6]\d|1048[0-4]\d{2}|104[0-7]\d{3}|10[0-3]\d{4}|[1-9]\d{1,5}|[1-9])d?\b)?"; Match match = Regex.Match(c.formula, regex, RegexOptions.IgnoreCase); while (match.Success && c.formula.IndexOf(":") != -1) { if (match.Groups[0].Value.IndexOf(":") != -1) { //// Finally, we get the Group value and display it. string key = match.Groups[0].Value; string cellRef = key.Replace("$", ""); string newCells = RangeToSeries(key); c.formula = c.formula.Replace(key, newCells); } match = Regex.Match(c.formula, regex, RegexOptions.IgnoreCase); } } else //String (use shared string table) { c.value = sharedStringTablePart1.SharedStringTable.ChildElements[int.Parse(c.value)].InnerText; } allCells.Add(cellAddress, c); } } } } } Console.WriteLine(); } ReplaceSharedFormulas(ref allCells); Console.WriteLine("Loaded from Excel:" + stopwatch.Elapsed); //while (savedCells.Count < 360) //{ foreach (Cell rCell in allCells.Values) { if (!savedCells.ContainsKey(rCell.cellAddress)) { if (rCell.value == null) //Blank? { savedCells.Add(rCell.cellAddress, rCell); } else if (rCell.formula == null) //Input { rCell.readOnly = true; //until updated (May be label, May be input) rCell.isDerived = false; savedCells.Add(rCell.cellAddress, rCell); } else //Formula { bool allCellsHere = true; foreach (string s in GetCellReferences((string)rCell.formula)) { if (!savedCells.ContainsKey(s)) { allCellsHere = false; break; } else { if (savedCells[s].formula == null) //Is an input { savedCells[s].empty = false; savedCells[s].readOnly = false; savedCells[s].hasDependent = true; } //c.empty = false;//this cell impacts others } } if (allCellsHere) { rCell.formula = rCell.formula.Replace("$", ""); rCell.readOnly = true; rCell.isDerived = true; savedCells.Add(rCell.cellAddress, rCell); } } } } //} Console.WriteLine("Organized Cells:" + stopwatch.Elapsed); Dictionary <string, Cell> savedCellsTrimmed = new Dictionary <string, Cell>(); List <int> rowsToRemove = new List <int>(); bool includeRow = false; for (int k = 30; k > 0; k--) { foreach (Cell c in savedCells.Values) { if (c.row == k) { if (c.value != "" || c.hasDependent) { includeRow = true; break; //include row; } } } if (includeRow) { break; } else { rowsToRemove.Add(k); } } foreach (Cell c in savedCells.Values) { if (!rowsToRemove.Contains(c.row)) { savedCellsTrimmed.Add(c.cellAddress, c); } } savedCells = savedCellsTrimmed; DataTable dt = new DataTable(); dt.Columns.Add("worksheet_id"); dt.Columns.Add("row"); dt.Columns.Add("column"); dt.Columns.Add("cell_address"); dt.Columns.Add("value"); dt.Columns.Add("formula"); dt.Columns.Add("data_type"); dt.Columns.Add("format"); dt.Columns.Add("read_only"); dt.Columns.Add("is_derived"); dt.Columns.Add("has_dependent"); dt.Columns.Add("fore_color"); dt.Columns.Add("back_color"); dt.Columns.Add("font_family"); dt.Columns.Add("italics"); dt.Columns.Add("bold"); dt.Columns.Add("underline"); dt.Columns.Add("font_size"); foreach (Cell c in savedCells.Values) { DataRow row = dt.NewRow(); row[0] = worksheetId; row[1] = c.row; row[2] = c.column; row[3] = c.cellAddress; row[4] = c.value; row[5] = c.formula; row[6] = c.dataType; row[7] = c.format; row[8] = c.readOnly; row[9] = c.isDerived; row[10] = c.hasDependent; row[11] = c.ForeColor; row[12] = c.BackColor; row[13] = c.FontFamily; row[14] = c.Italics; row[15] = c.Bold; row[16] = c.Underline; row[17] = c.FontSize; dt.Rows.Add(row); } SqlConnection cn = new SqlConnection(ConnectionString); cn.Open(); using (SqlBulkCopy copy = new SqlBulkCopy(cn)) { copy.ColumnMappings.Add(0, 0); copy.ColumnMappings.Add(1, 1); copy.ColumnMappings.Add(2, 2); copy.ColumnMappings.Add(3, 3); copy.ColumnMappings.Add(4, 4); copy.ColumnMappings.Add(5, 5); copy.ColumnMappings.Add(6, 6); copy.ColumnMappings.Add(7, 7); copy.ColumnMappings.Add(8, 8); copy.ColumnMappings.Add(9, 9); copy.ColumnMappings.Add(10, 10); copy.ColumnMappings.Add(11, 11); copy.ColumnMappings.Add(12, 12); copy.ColumnMappings.Add(13, 13); copy.ColumnMappings.Add(14, 14); copy.ColumnMappings.Add(15, 15); copy.ColumnMappings.Add(16, 16); copy.ColumnMappings.Add(17, 17); copy.DestinationTableName = "worksheet_cell"; copy.WriteToServer(dt); } Console.WriteLine("Loaded DB:" + stopwatch.Elapsed); }
public static void Write(string fileName, Workbook workbook) { if (fileName == null) { throw new ArgumentNullException("fileName"); } if (workbook == null) { throw new ArgumentNullException("workbook"); } FileInfo fi = new FileInfo(fileName); if (fi.Exists) { fi.Delete(); } // create the blank workbook char[] base64CharArray = _EmptyXlsx .Where(c => c != '\r' && c != '\n').ToArray(); byte[] byteArray = System.Convert.FromBase64CharArray(base64CharArray, 0, base64CharArray.Length); File.WriteAllBytes(fi.FullName, byteArray); // open the workbook, and create the TableProperties sheet, populate it using (SpreadsheetDocument sDoc = SpreadsheetDocument.Open(fi.FullName, true)) { WorkbookPart workbookPart = sDoc.WorkbookPart; XDocument wXDoc = workbookPart.GetXDocument(); XElement sheetElement = wXDoc .Root .Elements(S.sheets) .Elements(S.sheet) .Where(s => (string)s.Attribute(SSNoNamespace.name) == "Sheet1") .FirstOrDefault(); if (sheetElement == null) { throw new SpreadsheetWriterInternalException(); } string id = (string)sheetElement.Attribute(R.id); sheetElement.Remove(); workbookPart.PutXDocument(); WorksheetPart sPart = (WorksheetPart)workbookPart.GetPartById(id); workbookPart.DeletePart(sPart); XDocument appXDoc = sDoc .ExtendedFilePropertiesPart .GetXDocument(); XElement vector = appXDoc .Root .Elements(EP.TitlesOfParts) .Elements(VT.vector) .FirstOrDefault(); if (vector != null) { vector.SetAttributeValue(SSNoNamespace.size, 0); XElement lpstr = vector.Element(VT.lpstr); lpstr.Remove(); } XElement vector2 = appXDoc .Root .Elements(EP.HeadingPairs) .Elements(VT.vector) .FirstOrDefault(); XElement variant = vector2 .Descendants(VT.i4) .FirstOrDefault(); if (variant != null) { variant.Value = "1"; } sDoc.ExtendedFilePropertiesPart.PutXDocument(); if (workbook.Worksheets != null) { foreach (var worksheet in workbook.Worksheets) { AddWorksheet(sDoc, worksheet); } } workbookPart.WorkbookStylesPart.PutXDocument(); } }
public List <Person> Load_sheet() { using (SpreadsheetDocument doc = SpreadsheetDocument.Open(file_path, false)) { WorkbookPart workbookPart = doc.WorkbookPart; List <Person> people = new List <Person>(); List <string> collected_strings = new List <string>(); Sheet theSheet = workbookPart.Workbook.Descendants <Sheet>().FirstOrDefault(); if (theSheet == null) { throw new ArgumentException("sheetName"); } WorksheetPart wsPart = (WorksheetPart)(workbookPart.GetPartById(theSheet.Id)); var rows = wsPart.Worksheet.Descendants <DocumentFormat.OpenXml.Spreadsheet.Row>(); foreach (var row in rows) { collected_strings.Clear(); foreach (DocumentFormat.OpenXml.Spreadsheet.Cell c in row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>()) { string cellValue = string.Empty; if (c.DataType != null) { if (c.DataType == CellValues.SharedString) { int id = -1; if (Int32.TryParse(c.InnerText, out id)) { SharedStringItem item = GetSharedStringItemById(workbookPart, id); if (item.Text != null) { cellValue = item.Text.Text; } else if (item.InnerText != null) { cellValue = item.InnerText; } else if (item.InnerXml != null) { cellValue = item.InnerXml; } } } } else { DateTime start_date = new DateTime(1900, 1, 1); try { int days = Convert.ToInt32(c.CellValue.Text); start_date = start_date.AddDays(days - 2); string str_date = start_date.ToString("d.MM.yyyy"); cellValue = str_date; } catch { cellValue = null; } } collected_strings.Add(cellValue); } people.Add(new Person(collected_strings)); } people.RemoveAt(0); return(people); } }
/// <summary> /// Считывание данных из потока xlsx в объект DataTable /// </summary> /// <param name="s">Stream xlsx</param> /// <returns></returns> public static DataSet FromStream(Stream s) { DataSet ds = new DataSet(); DataTable dt = new DataTable(); ds.Tables.Add(dt); try { using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(s, false)) { // Получение части книги SpreadSheetDocument WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart; // Получение всех листов SpreadSheetDocument IEnumerable <Sheet> sheetcollection = spreadsheetDocument.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>(); // Получение идентификатора отношения string relationshipId = sheetcollection.First().Id.Value; // Получение части листа 1 SpreadSheetDocument WorksheetPart worksheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(relationshipId); // Получение данных из файла Excel SheetData sheetData = worksheetPart.Worksheet.Elements <SheetData>().First(); IEnumerable <Row> rowcollection = sheetData.Elements <Row>(); if (rowcollection.Count() == 0) { ds = null; return(ds); } // Добавление столбцов. Для начала 10. При заполнении строк столбцы могут быть добавлены for (int ci = 0; ci < 10; ci++) { dt.Columns.Add(String.Format("Column{0}", ci), typeof(String)); } // Добавление строк в DataTable foreach (Row row in rowcollection) { DataRow temprow = dt.NewRow(); int ci = 0; foreach (Cell cell in row.Elements <Cell>()) { // Получение индекса столбца ячейки int cellColumnIndex = GetColumnIndex(GetColumnName(cell.CellReference)); // если ячейки не подряд - добавляем пустые ячейки if (ci < cellColumnIndex) { do { temprow[ci] = string.Empty; ci++; }while (ci < cellColumnIndex); } // если ячейка из колонки которой ещё не - добавляем колонки if (ci >= dt.Columns.Count) { do { dt.Columns.Add(String.Format("Column{0}", ci), typeof(String)); }while (ci >= dt.Columns.Count); } temprow[ci] = GetValueOfCell(spreadsheetDocument, cell); ci++; } // Добавление строки в DataTable // строки содержат строку заголовка dt.Rows.Add(temprow); } } } catch (Exception e) { ds = null; throw new Exception("Ошибка при разборе потока xlsx.", e); } return(ds); }
/// <summary> /// Save the styl for worksheet headers. /// </summary> /// <param name="cellLocation">Cell location.</param> /// <param name="spreadSheet">Spreadsheet to change.</param> /// <param name="workSheetPart">Worksheet to change.</param> private static void SeatHeaderStyle(string cellLocation, SpreadsheetDocument spreadSheet, WorksheetPart workSheetPart) { Stylesheet styleSheet = spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet; Cell cell = workSheetPart.Worksheet.Descendants<Cell>().Where( c => c.CellReference == cellLocation).FirstOrDefault(); if (cell == null) { throw new ArgumentNullException("Cell not found"); } cell.SetAttribute(new OpenXmlAttribute("", "s", "", "1")); OpenXmlAttribute cellStyleAttribute = cell.GetAttribute("s", ""); CellFormats cellFormats = spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats; // pick the first cell format. CellFormat cellFormat = (CellFormat)cellFormats.ElementAt(0); CellFormat cf = new CellFormat(cellFormat.OuterXml); cf.FontId = styleSheet.Fonts.Count; cf.FillId = styleSheet.Fills.Count; cellFormats.AppendChild(cf); int a = (int)styleSheet.CellFormats.Count.Value; cell.SetAttribute(cellStyleAttribute); cell.StyleIndex = styleSheet.CellFormats.Count; workSheetPart.Worksheet.Save(); }
/// <summary> /// Write the content of the <see cref="ExcelMapCoOrdinateContainer"> to the worksheet</see>. /// </summary> /// <param name="sheetName">Name of the sheet.</param> /// <param name="worksheetPart">The worksheet part.</param> /// <param name="mapCoOrdinateContainer">The map co ordinate container.</param> /// <param name="stylesManager">The styles manager.</param> /// <param name="spreadsheetDocument">The spreadsheet document.</param> public static void WriteMapToExcel(string sheetName, WorksheetPart worksheetPart, ExcelMapCoOrdinateContainer mapCoOrdinateContainer, ExcelStylesManager stylesManager, SpreadsheetDocument spreadsheetDocument) { TempDiagnostics.Output(string.Format("Writing map for ExcelMap[{0}] to worksheet '{1}'", mapCoOrdinateContainer.ContainerType, sheetName)); var dimensionConverter = new ExcelDimensionConverter("Calibri", 11.0f); // Manages the writing to Excel. var excelWriteManager = new OpenXmlExcelWriteManager(worksheetPart.Worksheet); //** First we need to clear the destination worksheet down (rows, columns and merged areas) excelWriteManager.EmptyWorksheet(); // Build up Columns models on all nested containers. RowOrColumnsModel columnsModel = mapCoOrdinateContainer.BuildColumnsModel(); int colCount = columnsModel.Count(); // ========================================================== // Traverse each column assigning start and end // column indexes to the maps associated with that column. // ========================================================== RowOrColumnInfo columnInfo = columnsModel.First; while (columnInfo != null) { double?width = columnInfo.HeightOrWidth.HasValue ? (double?)dimensionConverter.WidthToOpenXmlWidth(columnInfo.HeightOrWidth.Value) : null; excelWriteManager.AddColumn(width, columnInfo.Hidden); // Assign Excel start and end columns to maps for merge operations. foreach (ExcelMapCoOrdinate map in columnInfo.Maps) { // Extend any cells that are merged across maps. if (map is ExcelMapCoOrdinatePlaceholder) { var cell = map as ExcelMapCoOrdinatePlaceholder; if (cell.MergeWith != null) { cell.ExcelColumnStart = cell.MergeWith.ExcelColumnStart; cell.MergeWith.ExcelColumnEnd = columnInfo.ExcelIndex; //excelCol } } if (map.ExcelColumnStart == 0) { map.ExcelColumnStart = columnInfo.ExcelIndex; //excelCol; map.ExcelColumnEnd = columnInfo.ExcelIndex; //excelCol; } else { map.ExcelColumnEnd = columnInfo.ExcelIndex; //excelCol; } } // Move on to next column in list columnInfo = columnInfo.Next; } TempDiagnostics.Output(string.Format("Created ColumnsModel which contains '{0}' columns", colCount)); // Build up Rows models on all nested containers. RowOrColumnsModel rowsModel = mapCoOrdinateContainer.BuildRowsModel(); int rowCount = rowsModel.Count(); // ========================================================== // Traverse each row assigning start and end // row indexes to the maps associated with that row. // ========================================================== RowOrColumnInfo rowInfo = rowsModel.First; while (rowInfo != null) { double?height = rowInfo.HeightOrWidth.HasValue ? (double?)dimensionConverter.HeightToOpenXmlHeight(rowInfo.HeightOrWidth.Value) : null; excelWriteManager.AddRow(height, rowInfo.Hidden); // Assign Excel start and end rows to maps for merge operations. foreach (ExcelMapCoOrdinate map in rowInfo.Maps) { if (map.ExcelRowStart == 0) { map.ExcelRowStart = rowInfo.ExcelIndex; //excelRow; map.ExcelRowEnd = rowInfo.ExcelIndex; //excelRow; } else { map.ExcelRowEnd = rowInfo.ExcelIndex; //excelRow; } } // Move on to next Row in list rowInfo = rowInfo.Next; } TempDiagnostics.Output(string.Format("Created RowsModel which contains '{0}' rows", rowCount)); // Build a layered cells dictionary for all cells, keyed by Excel row and column index var layeredCellsDictionary = new LayeredCellsDictionary(); mapCoOrdinateContainer.UpdateLayeredCells(ref layeredCellsDictionary); TempDiagnostics.Output(string.Format("Updated Layered Cell Information for worksheet '{0}' = {1}", sheetName, layeredCellsDictionary.Count)); // Probe the Row, Column and Cell Layered maps, embellishing them with row, column and cell formatting information. for (uint worksheetRow = 1; worksheetRow <= rowCount; worksheetRow++) { for (uint worksheetCol = 1; worksheetCol <= colCount; worksheetCol++) { // We can now use the layeredCellsDictionary to build the // excel workbook based on layered cell information var currentCoOrdinate = new System.Drawing.Point((int)worksheetCol, (int)worksheetRow); LayeredCellInfo layeredCellInfo = layeredCellsDictionary[currentCoOrdinate]; // Work through the layered maps to determine what needs to be written to the Excel worksheet at that Row/Column ProcessLayeredCellMaps(currentCoOrdinate, layeredCellInfo); } } TempDiagnostics.Output(string.Format("Built Worksheet CellInfos[Cols={0},Rows={1}]", colCount, rowCount)); //** Write the 2D array of cell information to the Excel Worksheet //** building a list of areas that are to be merged. for (uint worksheetRow = 1; worksheetRow <= rowCount; worksheetRow++) { OpenXmlSpreadsheet.Row row = excelWriteManager.GetRow(worksheetRow); for (uint worksheetCol = 1; worksheetCol <= colCount; worksheetCol++) { // Pluck out information relating to the current cell var currentCoOrdinate = new System.Drawing.Point((int)worksheetCol, (int)worksheetRow); ExcelCellInfo cellInfo = layeredCellsDictionary[currentCoOrdinate].CellInfo; // Not sure if we need this as column letters are easily (quickly) translated using existing OpenXml helpers string columnLetter = excelWriteManager.GetColumnLetter(worksheetCol); // All merge cells should have the same style as the source merge cell. if (cellInfo.MergeFrom != null) { // If MergeFrom is non-null, then this cell has been already been marked as a merge cell, // whose style is to be the same as the source 'MergeFrom cell. // Update the source (MergeFrom) cell so it ends up containing a reference to the last (MergeTo) cell to be merged. cellInfo.MergeFrom.MergeTo = cellInfo; // Create/lookup styles in the target workbook and return index of created style uint cellStyleIndex = stylesManager.GetOrCreateStyle(cellInfo.MergeFrom.StyleInfo); // Write in to Excel (style information only) var cell = new OpenXmlSpreadsheet.Cell(); cell.SetCellReference(columnLetter, worksheetRow); cell.StyleIndex = cellStyleIndex; row.Append(cell); cellInfo.Cell = cell; } else { // Create/lookup styles in the target workbook and return index of created style uint cellStyleIndex = stylesManager.GetOrCreateStyle(cellInfo.StyleInfo); // NB! If we write a Null value then we lose cell formatting information for some reason object value = cellInfo.Value == null ? string.Empty : cellInfo.Value; // Write in to Excel var cell = new OpenXmlSpreadsheet.Cell(); cell.SetCellReference(columnLetter, worksheetRow); excelWriteManager.SetCellValue(cell, value); cell.StyleIndex = cellStyleIndex; row.Append(cell); cellInfo.Cell = cell; } // Span the cell accross it's parent columns if specified if (cellInfo.LastSpanRow == 0) { cellInfo.LastSpanRow = worksheetRow; } if (cellInfo.LastSpanColumn == 0) { cellInfo.LastSpanColumn = worksheetCol; } // Merge cells if required (spanning) for (uint rowIdx = worksheetRow; rowIdx <= cellInfo.LastSpanRow; rowIdx++) { for (uint colIdx = worksheetCol; colIdx <= cellInfo.LastSpanColumn; colIdx++) { // Mark processed (so we don't over-write) var coOrdinate = new System.Drawing.Point((int)colIdx, (int)rowIdx); if (coOrdinate != currentCoOrdinate) { var mergeCoOrdinate = new System.Drawing.Point((int)colIdx, (int)rowIdx); ExcelCellInfo mergeCellInfo = layeredCellsDictionary[mergeCoOrdinate].CellInfo; if (mergeCellInfo.MergeFrom == null) { mergeCellInfo.MergeFrom = cellInfo; } } } } } } //#if DEBUG // if (!skipMerge) // { //#endif // Merge cells that have been marked to merge in the cellInfos dictionary. MergeCells(worksheetPart.Worksheet, (uint)rowCount, (uint)colCount, layeredCellsDictionary); //#if DEBUG // } //#endif TempDiagnostics.Output(string.Format("Written CellInfos[Cols={0},Rows={1}] to Excel", colCount, rowCount)); // Create a list of all of the the defined names in the container. var definedNameList = new List <ExcelDefinedNameInfo>(); mapCoOrdinateContainer.UpdateDefinedNameList(ref definedNameList, sheetName); // And write into Excel workbook foreach (var definedNameInfo in definedNameList) { uint rangeColumnCount = definedNameInfo.EndColumnIndex - definedNameInfo.StartColumnIndex + 1; uint rangeRowCount = definedNameInfo.EndRowIndex - definedNameInfo.StartRowIndex + 1; if (rangeRowCount > 0 && rangeColumnCount > 0) { spreadsheetDocument.WorkbookPart.Workbook.AddDefinedName(sheetName, definedNameInfo.DefinedName, (uint)definedNameInfo.StartColumnIndex, (uint)definedNameInfo.StartRowIndex, (int)rangeColumnCount, (int)rangeRowCount); } } TempDiagnostics.Output(string.Format("Defined Names added - write map for {0} complete...", mapCoOrdinateContainer.ContainerType)); }
/// <summary> /// This method exports datatable to a excel file /// </summary> /// <param name="table">DataTable</param> /// <param name="exportFile">Excel file name</param> private static void ExportDataTableToExcel(DataTable table, string exportFile) { try { // Create a spreadsheet document by supplying the filepath. SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument. Create(exportFile, SpreadsheetDocumentType.Workbook); // Add a WorkbookPart to the document. WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart(); workbookpart.Workbook = new Workbook(); // Add a WorksheetPart to the WorkbookPart. WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(new SheetData()); // Add Sheets to the Workbook. Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook. AppendChild <Sheets>(new Sheets()); // Append a new worksheet and associate it with the workbook. Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet" }; sheets.Append(sheet); SheetData data = worksheetPart.Worksheet.GetFirstChild <SheetData>(); //add column names to the first row Row header = new Row(); header.RowIndex = (UInt32)1; foreach (DataColumn column in table.Columns) { Cell headerCell = createTextCell( table.Columns.IndexOf(column) + 1, 1, column.ColumnName); header.AppendChild(headerCell); } data.AppendChild(header); //loop through each data row DataRow contentRow; for (int i = 0; i < table.Rows.Count; i++) { contentRow = table.Rows[i]; data.AppendChild(createContentRow(contentRow, i + 2)); } workbookpart.Workbook.Save(); // Close the document. spreadsheetDocument.Close(); } catch (Exception ex) { Console.WriteLine(ex.Message); } }
/// <summary> /// Write values to the spreadsheet. /// </summary> /// <param name="cellLocation">Row Column Value.</param> /// <param name="strValue">Value to write.</param> /// <param name="spreadSheet">Spreadsheet to write to. </param> /// <param name="workSheet">Worksheet to write to. </param> private static void WriteValues(string cellLocation, string strValue, SpreadsheetDocument spreadSheet, WorksheetPart workSheet) { WorksheetWriter workSheetWriter = new WorksheetWriter(spreadSheet, workSheet); int intValue = 0; if (strValue.Contains("$")) { strValue = strValue.Replace("$", ""); strValue = strValue.Replace(",", ""); workSheetWriter.PasteValue(cellLocation, strValue, CellValues.Number); } else if (int.TryParse(strValue, out intValue)) { workSheetWriter.PasteValue(cellLocation, strValue, CellValues.Number); } else if (string.IsNullOrEmpty(strValue)) { workSheetWriter.PasteText(cellLocation, strValue); } else { workSheetWriter.PasteText(cellLocation, strValue); } }
/// <summary> /// Write the excel rows for the spreadsheet. /// </summary> /// <param name="rowData">Excel row values.</param> /// <param name="rowDataKeys">Excel row-key values.</param> /// <param name="rowNum">Row number.</param> /// <param name="maxWidth">Max width.</param> /// <param name="spreadSheet">Spreadsheet to write to. </param> /// <param name="workSheet">Worksheet to write to. </param> private static void WriteRowsFromKeys(IList rowData, string[] rowDataKeys, int rowNum, out int maxWidth, SpreadsheetDocument spreadSheet, WorksheetPart workSheet) { maxWidth = 0; foreach (object row in rowData) { int colNum = 0; foreach (string rowKey in rowDataKeys) { var rowValue = row.GetType().GetProperty(rowKey).GetValue(row, null); string strValue = rowValue == null ? "" : rowValue.ToString(); //row.GetType().GetProperty(rowKey).GetValue(row, null).ToString(); strValue = ReplaceSpecialCharacters(strValue); maxWidth = strValue.Length > maxWidth ? strValue.Length : maxWidth; string cellLocation = string.Format("{0}{1}", GetColumnLetter(colNum.ToString()), rowNum); ExcelDocument.WriteValues(cellLocation, strValue, spreadSheet, workSheet); colNum++; } rowNum++; } }
/// <summary> /// Write the excel headers for the spreadsheet. /// </summary> /// <param name="headerData">Excel header values.</param> /// <param name="rowNum">Row number.</param> /// <param name="colNum">Column Number.</param> /// <param name="maxWidth">Max column width</param> /// <param name="spreadSheet">Maximum Column Width to write to. </param> /// <param name="workSheet">Worksheet to write to. </param> private static void WriteHeaders(string[] headerData, out int rowNum, out int colNum, out int maxWidth, SpreadsheetDocument spreadSheet, WorksheetPart workSheet) { rowNum = 1; colNum = 0; maxWidth = 0; foreach (string header in headerData) { string strValue = ReplaceSpecialCharacters(header); string cellLocation = string.Format("{0}{1}", GetColumnLetter(colNum.ToString()), rowNum); maxWidth = strValue.Length > maxWidth ? strValue.Length : maxWidth; ExcelDocument.WriteValues(cellLocation, strValue, spreadSheet, workSheet); SeatHeaderStyle(cellLocation, spreadSheet, workSheet); colNum++; } rowNum++; }
public override void Export(string filename, LayoutList list) { using (SpreadsheetDocument xl = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook)) { // Add a WorkbookPart to the document. WorkbookPart workbookpart = xl.AddWorkbookPart(); workbookpart.Workbook = new Workbook(); //add styles WorkbookStylesPart wbsp = workbookpart.AddNewPart <WorkbookStylesPart>(); wbsp.Stylesheet = CreateStylesheet(); wbsp.Stylesheet.Save(); // Add a SharedStringTablePart to the WorkbookPart. var stringPart = workbookpart.AddNewPart <SharedStringTablePart>(); var stringTable = new StringKeyList(); // Add a WorksheetPart to the WorkbookPart. WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>(); SheetData sd = new SheetData(); Worksheet worksheet = new Worksheet(sd); worksheetPart.Worksheet = worksheet; // Add Sheets to the Workbook. Sheets sheets = xl.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets()); // Append a new worksheet and associate it with the workbook. Sheet sheet = new Sheet() { Id = xl.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "DataSheet" }; sheets.Append(sheet); workbookpart.Workbook.Save(); //List<ILayoutItem> cols = LayoutMapTool.GetVisibleItems(list.ListInfo.Columns); //columns ExpMapLayout(sd, list.ListInfo.Columns, 0, 1, out int mc, out int ind, null, null, stringTable); //data if (list.ListInfo.GroupVisible) { foreach (LayoutGroup g in list.Groups) { ind++; Cell cell = GetCell(g.TextValue, 0, (int)ind, 8, stringTable); GetRow(sd, ind, false).Append(cell); MergeCells mcells = GetMergeCells(worksheet); MergeCell mcell = new MergeCell() { Reference = new StringValue(cell.CellReference + ":" + Helper.IntToChar(mc) + (ind).ToString()) }; mcells.Append(mcell); for (int i = g.IndexStart; i <= g.IndexEnd; i++) { ind++; ExpMapLayout(sd, list.ListInfo.Columns, 0, ind, out mc, out ind, list, list.ListSource[i], stringTable); } } } else { foreach (object o in list.ListSource) { ind++; ExpMapLayout(sd, list.ListInfo.Columns, 0, ind, out mc, out ind, list, o, stringTable); } } worksheet.Save(); OpenXmlValidator validator = new OpenXmlValidator(); var errors = validator.Validate(xl); StringBuilder sb = new StringBuilder(); foreach (var error in errors) { sb.AppendLine(error.Description); sb.AppendLine(error.Path.XPath.ToString()); sb.AppendLine(); } if (sb.Length > 0) { //System.Windows.Forms.MessageDialog.ShowMessage(sb.ToString()); } xl.Close(); } }
/// <summary> /// This method get the content of the Excel file /// </summary> /// <param name="file">Excel File</param> /// <returns>Table of the content of the Excel file</returns> /// <author>Edwin (Origin) - Abigail Rodriguez(Edit)</author> public ActionResult ImpExcel(HttpPostedFileBase file) { Dictionary <string, int> orderCell = new Dictionary <string, int>(); string[] arrayalf = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" }; for (int i = 0; i < arrayalf.Length; i++) { orderCell.Add(arrayalf[i], i); } DataSet ds = new DataSet(); List <List <string> > tr = new List <List <string> >(); try { if (Request.Files["file"].ContentLength > 0 /* && (idcategory != "0" && idcategory != null)*/) { string fileExtension = System.IO.Path.GetExtension(Request.Files["file"].FileName); if (fileExtension == ".xls" || fileExtension == ".xlsx") { string fileLocation = Server.MapPath("~/Content/") + Request.Files["file"].FileName; if (System.IO.File.Exists(fileLocation)) { System.IO.File.Delete(fileLocation); } Request.Files["file"].SaveAs(fileLocation); } string fileLocation2 = Server.MapPath("~/Content/") + Request.Files["file"].FileName; if (System.IO.File.Exists(fileLocation2)) { System.IO.File.Delete(fileLocation2); } Request.Files["file"].SaveAs(fileLocation2); using (DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileLocation2, false)) { WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart; WorksheetPart worksheetPart = workbookPart.WorksheetParts.Last(); SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();; foreach (Row r in sheetData.Elements <Row>()) { List <string> td = new List <string>(); int index = 0; foreach (Cell c in r.Elements <Cell>()) { string cellIndex = c.CellReference.ToString().Substring(0, 1); bool validate = false; int numcellx = 0; foreach (var x in orderCell) { if (x.Key == cellIndex) { numcellx = x.Value; } if (x.Key == cellIndex && x.Value == index) { validate = true; break; } } if (validate == false) { numcellx = numcellx - index; for (int i = 0; i < numcellx; i++) { td.Add(""); } index = index + numcellx; } Int32 id = -1; if (c.DataType != null && c.DataType.Value == CellValues.SharedString) { if (Int32.TryParse(c.InnerText, out id)) { SharedStringItem item = GetSharedStringItemById(workbookPart, id); if (item.Text != null) { td.Add(item.Text.Text); } else if (item.InnerText != null) { td.Add(item.InnerText); } else if (item.InnerXml != null) { td.Add(item.InnerXml); } } else { td.Add(c.CellValue.Text); } } else { try { td.Add(c.CellValue.Text); } catch (Exception ex) { td.Add(""); } } index++; } tr.Add(td); } spreadsheetDocument.Close(); } List <List <string> > data = new List <List <string> >(); ViewData["categoriedata"] = data; ViewData["Filelocation"] = fileLocation2; ViewData["Filerequest"] = file; return(View(tr)); } else { return(null); } } catch (Exception ex) { //throw new Exception(ex.Message); // System.Windows.Forms.MessageBox.Show(ex.Message.ToString()); //System.Windows.Forms.MessageBox.Show(ex.Message.ToString()); return(null); } }
public ActionResult Search(FormCollection model) { string selectedReport = model["cboReports"]; string datePeriod = model["DatePeriod"]; if (selectedReport.HasValue()) { Guid idReport = new Guid(selectedReport); Reports report = Reports.Get(idReport); IEnumerable <dynamic> data = Reports.Run(report, datePeriod); HttpContext.Response.SetCookie(new HttpCookie("fileDownload", "true") { Path = "/" }); var context = HttpContext.Response; context.Buffer = context.BufferOutput = false; context.Cache.SetCacheability(HttpCacheability.Private); context.Cache.SetExpires(DateTime.Now); //context.ContentType = (new ContentType("text/csv") { CharSet = "utf-8" }).ToString(); // CSV //context.ContentType = (new ContentType("application/vnd.ms-excel") { CharSet = "utf-8" }).ToString(); context.ContentType = new ContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") { CharSet = "utf-8" }.ToString(); context.AppendHeader("Content-Disposition", new ContentDisposition { DispositionType = DispositionTypeNames.Attachment, FileName = string.Format(CultureInfo.InvariantCulture, report.FileNamePrefix + "_{0:yyyyMMdd_HHmmss}.xlsx", DateTime.Now) }.ToString() ); context.AppendHeader("X-Content-Type-Options", "nosniff"); using (MemoryStream mDocument = new MemoryStream()) { // Using SAX using (SpreadsheetDocument document = SpreadsheetDocument.Create(mDocument, SpreadsheetDocumentType.Workbook)) { List <OpenXmlAttribute> attributes; document.AddWorkbookPart(); // Stylesheet WorkbookStylesPart stylesheet = document.WorkbookPart.AddNewPart <WorkbookStylesPart>(); stylesheet.Stylesheet = new Stylesheet(new Fonts( new Font( // 0 = Default new Color() { Rgb = new HexBinaryValue() { Value = "000000" } } ), new Font( // 1 = Bold new Bold() ), new Font( // 2 = Red new Color() { Rgb = new HexBinaryValue() { Value = "FF0000" } } ) ), new Fills( new Fill() { } ), new Borders(new Border() { }), new CellFormats( new CellFormat() { FontId = 0 }, // 0 new CellFormat() { FontId = 1, ApplyFont = true }, // 1 new CellFormat() { FontId = 2, ApplyFont = true } // 2 ) ); stylesheet.Stylesheet.Save(); WorksheetPart workSheetPart = document.WorkbookPart.AddNewPart <WorksheetPart>(); OpenXmlWriter writer = OpenXmlWriter.Create(workSheetPart); writer.WriteStartElement(new Worksheet()); writer.WriteStartElement(new SheetData()); IDictionary <string, object> firstRow = data.FirstOrDefault(); if (firstRow != null) { int row = 1; attributes = new List <OpenXmlAttribute> { new OpenXmlAttribute("r", null, row.ToString()) }; writer.WriteStartElement(new Row(), attributes); int col1 = 1; foreach (var cols in firstRow.Keys.ToList()) { attributes = new List <OpenXmlAttribute> { new OpenXmlAttribute("t", null, "str"), new OpenXmlAttribute("r", "", GetColumnName(col1) + row), new OpenXmlAttribute("s", "", "1") // Bold (Style 1) }; writer.WriteStartElement(new Cell(), attributes); writer.WriteElement(new CellValue(cols)); writer.WriteEndElement(); col1++; } writer.WriteEndElement(); row++; foreach (IDictionary <string, object> row2 in data) { attributes = new List <OpenXmlAttribute> { new OpenXmlAttribute("r", null, row.ToString()) }; writer.WriteStartElement(new Row(), attributes); int col = 1; foreach (var key in row2.Keys) { attributes = new List <OpenXmlAttribute> { new OpenXmlAttribute("t", null, "str"), new OpenXmlAttribute("r", "", GetColumnName(col) + row) }; if (row2[key] is decimal) { if ((decimal)row2[key] < 0) { attributes.Add(new OpenXmlAttribute("s", "", "2")); // Red (Style 2) } } else if (row2[key] is double) { if ((double)row2[key] < 0) { attributes.Add(new OpenXmlAttribute("s", "", "2")); // Red (Style 2) } } writer.WriteStartElement(new Cell(), attributes); writer.WriteElement(new CellValue(row2[key] != null ? row2[key].ToString() : "")); writer.WriteEndElement(); col++; } writer.WriteEndElement(); row++; } } else { // Empty row (no data found) attributes = new List <OpenXmlAttribute> { new OpenXmlAttribute("r", null, "1") }; writer.WriteStartElement(new Row(), attributes); attributes = new List <OpenXmlAttribute> { new OpenXmlAttribute("t", null, "str"), new OpenXmlAttribute("r", "", GetColumnName(1) + 1), new OpenXmlAttribute("s", "", "1") // Bold (Style 1) }; writer.WriteStartElement(new Cell(), attributes); writer.WriteElement(new CellValue("")); writer.WriteEndElement(); writer.WriteEndElement(); } writer.WriteEndElement(); writer.WriteEndElement(); writer.Close(); writer = OpenXmlWriter.Create(document.WorkbookPart); writer.WriteStartElement(new Workbook()); writer.WriteStartElement(new Sheets()); writer.WriteElement(new Sheet() { Name = "Sheet 1", SheetId = 1, Id = document.WorkbookPart.GetIdOfPart(workSheetPart) }); writer.WriteEndElement(); writer.WriteEndElement(); writer.Close(); document.Save(); document.Close(); mDocument.WriteTo(context.OutputStream); } } return(null); } return(null); }
public static void InsertImage(this SpreadsheetDocument document, string sheet, long x, long y, long?width, long?height, string sImagePath) { try { WorksheetPart wsp = (WorksheetPart)document.WorkbookPart.GetPartById(sheet); DrawingsPart dp; ImagePart imgp; WorksheetDrawing wsd; ImagePartType ipt; switch (sImagePath.Substring(sImagePath.LastIndexOf('.') + 1).ToLower()) { case "png": ipt = ImagePartType.Png; break; case "jpg": case "jpeg": ipt = ImagePartType.Jpeg; break; case "gif": ipt = ImagePartType.Gif; break; case "bmp": ipt = ImagePartType.Bmp; break; default: return; } if (wsp.DrawingsPart == null) { dp = wsp.AddNewPart <DrawingsPart>(); imgp = dp.AddImagePart(ipt, wsp.GetIdOfPart(dp)); wsd = new WorksheetDrawing(); } else { dp = wsp.DrawingsPart; imgp = dp.AddImagePart(ipt); dp.CreateRelationshipToPart(imgp); wsd = dp.WorksheetDrawing; } using (FileStream fs = new FileStream(sImagePath, FileMode.Open)) { imgp.FeedData(fs); } int imageNumber = dp.ImageParts.Count <ImagePart>(); if (imageNumber == 1) { Drawing drawing = new Drawing(); drawing.Id = dp.GetIdOfPart(imgp); wsp.Worksheet.Append(drawing); } NonVisualDrawingProperties nvdp = new NonVisualDrawingProperties(); nvdp.Id = new UInt32Value((uint)(1024 + imageNumber)); nvdp.Name = "Picture " + imageNumber.ToString(); nvdp.Description = ""; DocumentFormat.OpenXml.Drawing.PictureLocks picLocks = new DocumentFormat.OpenXml.Drawing.PictureLocks(); picLocks.NoChangeAspect = true; picLocks.NoChangeArrowheads = true; NonVisualPictureDrawingProperties nvpdp = new NonVisualPictureDrawingProperties(); nvpdp.PictureLocks = picLocks; NonVisualPictureProperties nvpp = new NonVisualPictureProperties(); nvpp.NonVisualDrawingProperties = nvdp; nvpp.NonVisualPictureDrawingProperties = nvpdp; DocumentFormat.OpenXml.Drawing.Stretch stretch = new DocumentFormat.OpenXml.Drawing.Stretch(); stretch.FillRectangle = new DocumentFormat.OpenXml.Drawing.FillRectangle(); BlipFill blipFill = new BlipFill(); DocumentFormat.OpenXml.Drawing.Blip blip = new DocumentFormat.OpenXml.Drawing.Blip(); blip.Embed = dp.GetIdOfPart(imgp); blip.CompressionState = DocumentFormat.OpenXml.Drawing.BlipCompressionValues.Print; blipFill.Blip = blip; blipFill.SourceRectangle = new DocumentFormat.OpenXml.Drawing.SourceRectangle(); blipFill.Append(stretch); DocumentFormat.OpenXml.Drawing.Transform2D t2d = new DocumentFormat.OpenXml.Drawing.Transform2D(); DocumentFormat.OpenXml.Drawing.Offset offset = new DocumentFormat.OpenXml.Drawing.Offset(); offset.X = 0; offset.Y = 0; t2d.Offset = offset; System.Drawing.Bitmap bm = new System.Drawing.Bitmap(sImagePath); DocumentFormat.OpenXml.Drawing.Extents extents = new DocumentFormat.OpenXml.Drawing.Extents(); if (width == null) { extents.Cx = (long)bm.Width * (long)((float)914400 / bm.HorizontalResolution); } else { extents.Cx = width * (long)((float)914400 / bm.HorizontalResolution); } if (height == null) { extents.Cy = (long)bm.Height * (long)((float)914400 / bm.VerticalResolution); } else { extents.Cy = height * (long)((float)914400 / bm.VerticalResolution); } bm.Dispose(); t2d.Extents = extents; ShapeProperties sp = new ShapeProperties(); sp.BlackWhiteMode = DocumentFormat.OpenXml.Drawing.BlackWhiteModeValues.Auto; sp.Transform2D = t2d; DocumentFormat.OpenXml.Drawing.PresetGeometry prstGeom = new DocumentFormat.OpenXml.Drawing.PresetGeometry(); prstGeom.Preset = DocumentFormat.OpenXml.Drawing.ShapeTypeValues.Rectangle; prstGeom.AdjustValueList = new DocumentFormat.OpenXml.Drawing.AdjustValueList(); sp.Append(prstGeom); sp.Append(new DocumentFormat.OpenXml.Drawing.NoFill()); DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture picture = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture(); picture.NonVisualPictureProperties = nvpp; picture.BlipFill = blipFill; picture.ShapeProperties = sp; Position pos = new Position(); pos.X = x * 914400 / 72; pos.Y = y * 914400 / 72; Extent ext = new Extent(); ext.Cx = extents.Cx; ext.Cy = extents.Cy; AbsoluteAnchor anchor = new AbsoluteAnchor(); anchor.Position = pos; anchor.Extent = ext; anchor.Append(picture); anchor.Append(new ClientData()); wsd.Append(anchor); wsd.Save(dp); } catch (Exception ex) { throw ex; } }
private static void WriteingToExcel(WorksheetPart worksheetPart) { SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData()); Row row = new Row(); row.Append(new Cell() { CellValue = new CellValue("napis test "), DataType = CellValues.String, StyleIndex = 2 }, new Cell() { CellValue = new CellValue("napis test "), DataType = CellValues.String, StyleIndex = 2 }, new Cell() { CellValue = new CellValue("napis test "), DataType = CellValues.String, StyleIndex = 2 }); sheetData.AppendChild(row); for (int i = 0; i < 20; i++) { row = new Row(); row.Append(new Cell() { CellValue = new CellValue("napis test " + i), DataType = CellValues.String, StyleIndex = 1 }, new Cell() { CellValue = new CellValue("napis test " + i), DataType = CellValues.String, StyleIndex = 1 }, new Cell() { CellValue = new CellValue("napis test " + i), DataType = CellValues.String, StyleIndex = 1 }, new Cell() { CellValue = new CellValue("napis test " + i), DataType = CellValues.String, StyleIndex = 1 }, new Cell() { CellValue = new CellValue("napis test "), DataType = CellValues.String, StyleIndex = 1 }, new Cell() { CellValue = new CellValue("napis test " + i), DataType = CellValues.String, StyleIndex = 1 }, new Cell() { CellValue = new CellValue("napis test "), DataType = CellValues.String, StyleIndex = 1 }, new Cell() { CellValue = new CellValue("napis test " + i), DataType = CellValues.String, StyleIndex = 1 }, new Cell() { CellValue = new CellValue("napis test "), DataType = CellValues.String, StyleIndex = 1 }); sheetData.AppendChild(row); } worksheetPart.Worksheet.Save(); }
static void WritePlainExcel(ResultTable results, Stream stream, string title) { stream.WriteAllBytes(Template); if (results == null) { throw new ApplicationException(ExcelMessage.ThereAreNoResultsToWrite.NiceToString()); } using (SpreadsheetDocument document = SpreadsheetDocument.Open(stream, true)) { document.PackageProperties.Creator = ""; document.PackageProperties.LastModifiedBy = ""; WorkbookPart workbookPart = document.WorkbookPart; WorksheetPart worksheetPart = document.GetWorksheetPartById("rId1"); worksheetPart.Worksheet = new Worksheet(); worksheetPart.Worksheet.Append(new Columns(results.Columns.Select((c, i) => new spreadsheet.Column() { Min = (uint)i + 1, Max = (uint)i + 1, Width = GetColumnWidth(c.Column.Type), BestFit = true, CustomWidth = true }).ToArray())); Dictionary <ResultColumn, (DefaultStyle defaultStyle, UInt32Value styleIndex)> indexes = results.Columns.ToDictionary(c => c, c => CellBuilder.GetDefaultStyleAndIndex(c)); var ss = document.WorkbookPart.WorkbookStylesPart.Stylesheet; { var maxIndex = ss.NumberingFormats.ChildElements.Cast <NumberingFormat>() .Max(f => (uint)f.NumberFormatId) + 1; var decimalCellFormat = ss.CellFormats.ElementAt((int)(uint)CellBuilder.DefaultStyles[DefaultStyle.Decimal]); foreach (var kvp in CellBuilder.CustomDecimalStyles) { var numberingFormat = new NumberingFormat { NumberFormatId = maxIndex++, FormatCode = kvp.Key }; ss.NumberingFormats.AppendChild(numberingFormat); var cellFormat = (CellFormat)decimalCellFormat.CloneNode(false); cellFormat.NumberFormatId = numberingFormat.NumberFormatId; ss.CellFormats.AppendChild(cellFormat); ss.CellFormats.Count = (uint)ss.CellFormats.ChildElements.Count; if (ss.CellFormats.Count != kvp.Value + 1) { throw new InvalidOperationException("Unexpected CellFormats count"); } } } worksheetPart.Worksheet.Append(new Sequence <Row>() { new [] { CellBuilder.Cell(title, DefaultStyle.Title) }.ToRow(), (from c in results.Columns select CellBuilder.Cell(c.Column.DisplayName, DefaultStyle.Header)).ToRow(), from r in results.Rows select(from c in results.Columns let t = indexes.GetOrThrow(c) select CellBuilder.Cell(r[c], t.defaultStyle, t.styleIndex)).ToRow() }.ToSheetData()); workbookPart.Workbook.Save(); document.Close(); } }
private static void AddWorksheet(SpreadsheetDocument sDoc, Worksheet worksheetData) { Regex validSheetName = new Regex(@"^[^'*\[\]/\\:?][^*\[\]/\\:?]{0,30}$"); if (!validSheetName.IsMatch(worksheetData.Name)) { throw new InvalidSheetNameException(worksheetData.Name); } // throw WorksheetAlreadyExistsException if a sheet with the same name (case-insensitive) already exists in the workbook string UCName = worksheetData.Name.ToUpper(); XDocument wXDoc = sDoc.WorkbookPart.GetXDocument(); if (wXDoc .Root .Elements(S.sheets) .Elements(S.sheet) .Attributes(SSNoNamespace.name) .Select(a => ((string)a).ToUpper()) .Contains(UCName)) { throw new WorksheetAlreadyExistsException(worksheetData.Name); } // create the worksheet with the supplied name XDocument appXDoc = sDoc .ExtendedFilePropertiesPart .GetXDocument(); XElement vector = appXDoc .Root .Elements(EP.TitlesOfParts) .Elements(VT.vector) .FirstOrDefault(); if (vector != null) { int?size = (int?)vector.Attribute(SSNoNamespace.size); if (size == null) { size = 1; } else { size = size + 1; } vector.SetAttributeValue(SSNoNamespace.size, size); vector.Add( new XElement(VT.lpstr, worksheetData.Name)); XElement i4 = appXDoc .Root .Elements(EP.HeadingPairs) .Elements(VT.vector) .Elements(VT.variant) .Elements(VT.i4) .FirstOrDefault(); if (i4 != null) { i4.Value = ((int)i4 + 1).ToString(); } sDoc.ExtendedFilePropertiesPart.PutXDocument(); } WorkbookPart workbook = sDoc.WorkbookPart; string rId = "R" + Guid.NewGuid().ToString().Replace("-", ""); WorksheetPart worksheetPart = workbook.AddNewPart <WorksheetPart>(rId); XDocument wbXDoc = workbook.GetXDocument(); XElement sheets = wbXDoc.Descendants(S.sheets).FirstOrDefault(); sheets.Add( new XElement(S.sheet, new XAttribute(SSNoNamespace.name, worksheetData.Name.ToString()), new XAttribute(SSNoNamespace.sheetId, sheets.Elements(S.sheet).Count() + 1), new XAttribute(R.id, rId))); workbook.PutXDocument(); string ws = S.s.ToString(); string relns = R.r.ToString(); using (Stream partStream = worksheetPart.GetStream(FileMode.Create, FileAccess.Write)) { using (XmlWriter partXmlWriter = XmlWriter.Create(partStream)) { partXmlWriter.WriteStartDocument(); partXmlWriter.WriteStartElement("worksheet", ws); partXmlWriter.WriteStartElement("sheetData", ws); int numColumnHeadingRows = 0; int numColumns = 0; int numColumnsInRows = 0; int numRows; if (worksheetData.ColumnHeadings != null) { Row row = new Row { Cells = worksheetData.ColumnHeadings }; SerializeRows(sDoc, partXmlWriter, new[] { row }, 1, out numColumns, out numColumnHeadingRows); } SerializeRows(sDoc, partXmlWriter, worksheetData.Rows, numColumnHeadingRows + 1, out numColumnsInRows, out numRows); int totalRows = numColumnHeadingRows + numRows; int totalColumns = Math.Max(numColumns, numColumnsInRows); if (worksheetData.ColumnHeadings != null && worksheetData.TableName != null) { partXmlWriter.WriteEndElement(); string rId2 = "R" + Guid.NewGuid().ToString().Replace("-", ""); partXmlWriter.WriteStartElement("tableParts", ws); partXmlWriter.WriteStartAttribute("count"); partXmlWriter.WriteValue(1); partXmlWriter.WriteEndAttribute(); partXmlWriter.WriteStartElement("tablePart", ws); partXmlWriter.WriteStartAttribute("id", relns); partXmlWriter.WriteValue(rId2); TableDefinitionPart tdp = worksheetPart.AddNewPart <TableDefinitionPart>(rId2); XDocument tXDoc = tdp.GetXDocument(); XElement table = new XElement(S.table, new XAttribute(SSNoNamespace.id, 1), new XAttribute(SSNoNamespace.name, worksheetData.TableName), new XAttribute(SSNoNamespace.displayName, worksheetData.TableName), new XAttribute(SSNoNamespace._ref, "A1:" + SpreadsheetMLUtil.IntToColumnId(totalColumns - 1) + totalRows.ToString()), new XAttribute(SSNoNamespace.totalsRowShown, 0), new XElement(S.autoFilter, new XAttribute(SSNoNamespace._ref, "A1:" + SpreadsheetMLUtil.IntToColumnId(totalColumns - 1) + totalRows.ToString())), new XElement(S.tableColumns, new XAttribute(SSNoNamespace.count, totalColumns), worksheetData.ColumnHeadings.Select((ch, i) => new XElement(S.tableColumn, new XAttribute(SSNoNamespace.id, i + 1), new XAttribute(SSNoNamespace.name, ch.Value)))), new XElement(S.tableStyleInfo, new XAttribute(SSNoNamespace.name, "TableStyleMedium2"), new XAttribute(SSNoNamespace.showFirstColumn, 0), new XAttribute(SSNoNamespace.showLastColumn, 0), new XAttribute(SSNoNamespace.showRowStripes, 1), new XAttribute(SSNoNamespace.showColumnStripes, 0))); tXDoc.Add(table); tdp.PutXDocument(); } } } }
/// <summary> /// Write excel file of a list of object as T /// Assume that maximum of 24 columns /// </summary> /// <typeparam name="T">Object type to pass in</typeparam> /// <param name="fileName">Full path of the file name of excel spreadsheet</param> /// <param name="objects">list of the object type</param> /// <param name="sheetName">Sheet names of Excel File</param> /// <param name="headerNames">Header names of the object</param> public void Create <T>( string fileName, List <T> objects, string sheetName, List <string> headerNames) { //Open the copied template workbook. using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook)) { WorkbookPart workbookPart = myWorkbook.AddWorkbookPart(); WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); // Create Styles and Insert into Workbook WorkbookStylesPart stylesPart = myWorkbook.WorkbookPart.AddNewPart <WorkbookStylesPart>(); Stylesheet styles = new CustomStylesheet(); styles.Save(stylesPart); string relId = workbookPart.GetIdOfPart(worksheetPart); Workbook workbook = new Workbook(); FileVersion fileVersion = new FileVersion { ApplicationName = "Microsoft Office Excel" }; SheetData sheetData = CreateSheetData <T>(objects, headerNames, stylesPart); Worksheet worksheet = new Worksheet(); //PageMargins pageM = worksheet.GetFirstChild<PageMargins>(); //SheetProtection sheetProtection = new SheetProtection(); //sheetProtection.Sheet = true; //sheetProtection.Objects = true; //sheetProtection.Scenarios = true; ////add column C:Z to allow edit range, which means column A,B and after Z are locked //ProtectedRanges pRanges = new ProtectedRanges(); //ProtectedRange pRange = new ProtectedRange(); //ListValue<StringValue> lValue = new ListValue<StringValue>(); //lValue.InnerText = "D1:Z1048576"; //pRange.SequenceOfReferences = lValue; //pRange.Name = "AllowEditRange1"; //pRanges.Append(pRange); //worksheet.InsertBefore(sheetProtection, pageM); //worksheet.InsertBefore(pRanges, pageM); int numCols = headerNames.Count; int width = headerNames.Max(h => h.Length) + 5; Columns columns = new Columns(); for (int col = 0; col < numCols; col++) { Column c = CreateColumnData((UInt32)col + 1, (UInt32)numCols + 1, width); if (col == 0) { c.Hidden = BooleanValue.FromBoolean(true); } columns.Append(c); } worksheet.Append(columns); Sheets sheets = new Sheets(); Sheet sheet = new Sheet { Name = sheetName, SheetId = 1, Id = relId }; sheets.Append(sheet); workbook.Append(fileVersion); workbook.Append(sheets); worksheet.Append(sheetData); worksheetPart.Worksheet = worksheet; worksheetPart.Worksheet.Save(); myWorkbook.WorkbookPart.Workbook = workbook; myWorkbook.WorkbookPart.Workbook.Save(); myWorkbook.Close(); } }
static void Main(string[] args) { Student myrecord = new Student { StudentId = "200429013", FirstName = "Priyanka", LastName = "Garg" }; List <string> directories = FTP.GetDirectory(Constants.FTP.BaseUrl); List <Student> students = new List <Student>(); foreach (var directory in directories) { Student student = new Student() { AbsoluteUrl = Constants.FTP.BaseUrl }; student.FromDirectory(directory); //Console.WriteLine(student); string infoFilePath = student.FullPathUrl + "/" + Constants.Locations.InfoFile; bool fileExists = FTP.FileExists(infoFilePath); if (fileExists == true) { string csvPath = $@"/Users/priyankagarg/Desktop/Student Data/{directory}.csv"; // FTP.DownloadFile(infoFilePath, csvPath); byte[] bytes = FTP.DownloadFileBytes(infoFilePath); string csvData = Encoding.Default.GetString(bytes); string[] csvlines = csvData.Split("\r\n", StringSplitOptions.RemoveEmptyEntries); if (csvlines.Length != 2) { Console.WriteLine("Error in CSV format"); } else { student.FromCSV(csvlines[1]); Console.WriteLine(" \t Age of Student is: {0} ", student.age); } Console.WriteLine("Found info file:"); } else { Console.WriteLine("Could not find info file:"); } Console.WriteLine("\t" + infoFilePath); string imageFilePath = student.FullPathUrl + "/" + Constants.Locations.ImageFile; bool imageFileExists = FTP.FileExists(imageFilePath); if (imageFileExists == true) { Console.WriteLine("Found image file:"); } else { Console.WriteLine("Could not find image file:"); } Console.WriteLine("\t" + imageFilePath); students.Add(student); Console.WriteLine(directory); Console.WriteLine(" \t Count of student is: {0}", students.Count); Console.WriteLine(" \t Age of Student is: {0} ", student.age); } Student me = students.SingleOrDefault(x => x.StudentId == myrecord.StudentId); Student meUsingFind = students.Find(x => x.StudentId == myrecord.StudentId); var avgage = students.Average(x => x.age); var minage = students.Min(x => x.age); var maxage = students.Max(x => x.age); Console.WriteLine(" \n\t Name Searched With Query: {0} ", meUsingFind); Console.WriteLine(" \t Average of Student age is: {0} ", avgage); Console.WriteLine(" \t Minimum of Student age is: {0} ", minage); Console.WriteLine(" \t Maximum of Student age is: {0} ", maxage); //save to csv string studentsCSVPath = $"{Constants.Locations.DataFolder}//students.csv"; //Establish a file stream to collect data from the response using (StreamWriter fs = new StreamWriter(studentsCSVPath)) { foreach (var student in students) { fs.WriteLine(student.ToCSV()); } } string studentsWordPath = $"{Constants.Locations.DataFolder}//students.docx"; //string studentsImagePath1 = $"{Constants.Locations.ImagesFolder}//images.jpg"; // Create a document by supplying the filepath. using (WordprocessingDocument wordDocument = WordprocessingDocument.Create(studentsWordPath, WordprocessingDocumentType.Document)) { // Add a main document part. MainDocumentPart mainPart = wordDocument.AddMainDocumentPart(); // Create the document structure and add some text. mainPart.Document = new Document(); Body body = mainPart.Document.AppendChild(new Body()); Paragraph para = body.AppendChild(new Paragraph()); Run run = para.AppendChild(new Run()); foreach (var student in students) { run.AppendChild(new Text("My name is : ")); //run.AppendChild(new Text(student.ToString())); run.AppendChild(new Text(student.FirstName.ToString())); run.AppendChild(new Text(" , ")); run.AppendChild(new Text("My Student id is: ")); run.AppendChild(new Text(student.StudentId.ToString())); run.AppendChild(new Text(" , ")); run.AppendChild(new Break() { Type = BreakValues.Page }); } } string studentsjsonPath = $"{Constants.Locations.DataFolder}//students.json"; //Establish a file stream to collect data from the response using (StreamWriter fs = new StreamWriter(studentsjsonPath)) { foreach (var student in students) { string Student = Newtonsoft.Json.JsonConvert.SerializeObject(student); fs.WriteLine(Student.ToString()); //Console.WriteLine(jStudent); } } string studentsExcelPath = $"{Constants.Locations.DataFolder}//students.xlsx"; SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument. Create(studentsExcelPath, SpreadsheetDocumentType.Workbook); // Add a WorkbookPart to the document. WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart(); workbookpart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook(); // Add a WorksheetPart to the WorkbookPart. WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>(); worksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(new DocumentFormat.OpenXml.Spreadsheet.SheetData()); // Add Sheets to the Workbook. DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook. AppendChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>(new DocumentFormat.OpenXml.Spreadsheet.Sheets()); // Append a new worksheet and associate it with the workbook. DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = spreadsheetDocument.WorkbookPart. GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet" }; sheets.Append(sheet); workbookpart.Workbook.Save(); spreadsheetDocument.Close(); string studentsxmlPath = $"{Constants.Locations.DataFolder}//students.xml"; //Establish a file stream to collect data from the response using (StreamWriter fs = new StreamWriter(studentsxmlPath)) { XmlSerializer x = new XmlSerializer(students.GetType()); x.Serialize(fs, students); Console.WriteLine(); } // 4.Upload the files to My FTP foreach (var student in students) { FTP.UploadFile(studentsCSVPath, Constants.FTP.BaseUrl + "/200429013 Priyanka Garg/students.csv"); FTP.UploadFile(studentsjsonPath, Constants.FTP.BaseUrl + "/200429013 Priyanka Garg/students.json"); FTP.UploadFile(studentsxmlPath, Constants.FTP.BaseUrl + "/200429013 Priyanka Garg/students.xml"); } return; }
public static string GetCellValue(string fileName, string sheetName, string addressName) { string value = null; using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, false)) { WorkbookPart wbPart = document.WorkbookPart; // Find the sheet with the supplied name, and then use that Sheet // object to retrieve a reference to the appropriate worksheet. Sheet theSheet = wbPart.Workbook.Descendants <Sheet>(). Where(s => s.Name == sheetName).FirstOrDefault(); if (theSheet == null) { throw new ArgumentException("sheetName"); } // Retrieve a reference to the worksheet part, and then use its // Worksheet property to get a reference to the cell whose // address matches the address you supplied: WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id)); Cell theCell = wsPart.Worksheet.Descendants <Cell>(). Where(c => c.CellReference == addressName).FirstOrDefault(); // If the cell does not exist, return an empty string: if (theCell != null) { value = theCell.InnerText; // If the cell represents a numeric value, you are done. // For dates, this code returns the serialized value that // represents the date. The code handles strings and Booleans // individually. For shared strings, the code looks up the // corresponding value in the shared string table. For Booleans, // the code converts the value into the words TRUE or FALSE. if (theCell.DataType != null) { switch (theCell.DataType.Value) { case CellValues.SharedString: // For shared strings, look up the value in the shared // strings table. var stringTable = wbPart. GetPartsOfType <SharedStringTablePart>().FirstOrDefault(); // If the shared string table is missing, something is // wrong. Return the index that you found in the cell. // Otherwise, look up the correct text in the table. if (stringTable != null) { value = stringTable.SharedStringTable. ElementAt(int.Parse(value)).InnerText; } break; case CellValues.Boolean: switch (value) { case "0": value = "FALSE"; break; default: value = "TRUE"; break; } break; } } } } return(value); }
public static void CreateDoc(ExcelInfoEmployee info) { using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(info.FileName, SpreadsheetDocumentType.Workbook)) { // Создаем книгу (в ней хранятся листы) WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart(); workbookpart.Workbook = new Workbook(); CreateStyles(workbookpart); // Получаем/создаем хранилище текстов для книги SharedStringTablePart shareStringPart = spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0 ? spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First() : spreadsheetDocument.WorkbookPart.AddNewPart <SharedStringTablePart>(); // Создаем SharedStringTable, если его нет if (shareStringPart.SharedStringTable == null) { shareStringPart.SharedStringTable = new SharedStringTable(); } // Создаем лист в книгу WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(new SheetData()); // Добавляем лист в книгу Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets()); Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Лист" }; sheets.Append(sheet); InsertCellInWorksheet(new ExcelCellParameters { Worksheet = worksheetPart.Worksheet, ShareStringPart = shareStringPart, ColumnName = "A", RowIndex = 1, Text = info.Title, StyleIndex = 2U }); MergeCells(new ExcelMergeParameters { Worksheet = worksheetPart.Worksheet, CellFromName = "A1", CellToName = "C1" }); uint rowIndex = 2; var list = info.Purchases.GroupBy(purchase => purchase.CosmeticName, purchase => purchase.Price, (name, cost) => new { Key = name, Price = cost, Count = cost.Count() }); var j = 0; foreach (var cosmetic in list) { rowIndex++; InsertCellInWorksheet(new ExcelCellParameters { Worksheet = worksheetPart.Worksheet, ShareStringPart = shareStringPart, ColumnName = "A", RowIndex = rowIndex, Text = "Наименование", StyleIndex = 0U }); InsertCellInWorksheet(new ExcelCellParameters { Worksheet = worksheetPart.Worksheet, ShareStringPart = shareStringPart, ColumnName = "B", RowIndex = rowIndex, Text = cosmetic.Key, StyleIndex = 0U }); rowIndex++; InsertCellInWorksheet(new ExcelCellParameters { Worksheet = worksheetPart.Worksheet, ShareStringPart = shareStringPart, ColumnName = "A", RowIndex = rowIndex, Text = "Стоимость", StyleIndex = 0U }); InsertCellInWorksheet(new ExcelCellParameters { Worksheet = worksheetPart.Worksheet, ShareStringPart = shareStringPart, ColumnName = "B", RowIndex = rowIndex, Text = cosmetic.Price.First().ToString(), StyleIndex = 0U }); rowIndex += 2; InsertCellInWorksheet(new ExcelCellParameters { Worksheet = worksheetPart.Worksheet, ShareStringPart = shareStringPart, ColumnName = "B", RowIndex = rowIndex, Text = "Дата", StyleIndex = 1U }); InsertCellInWorksheet(new ExcelCellParameters { Worksheet = worksheetPart.Worksheet, ShareStringPart = shareStringPart, ColumnName = "C", RowIndex = rowIndex, Text = "Количество", StyleIndex = 1U }); InsertCellInWorksheet(new ExcelCellParameters { Worksheet = worksheetPart.Worksheet, ShareStringPart = shareStringPart, ColumnName = "D", RowIndex = rowIndex, Text = "ID клиента", StyleIndex = 1U }); rowIndex++; for (var i = 0; i < cosmetic.Count; i++) { InsertCellInWorksheet(new ExcelCellParameters { Worksheet = worksheetPart.Worksheet, ShareStringPart = shareStringPart, ColumnName = "B", RowIndex = rowIndex, Text = info.Purchases[j].Date.ToString(), StyleIndex = 1U }); InsertCellInWorksheet(new ExcelCellParameters { Worksheet = worksheetPart.Worksheet, ShareStringPart = shareStringPart, ColumnName = "C", RowIndex = rowIndex, Text = info.Purchases[j].Count.ToString(), StyleIndex = 1U }); InsertCellInWorksheet(new ExcelCellParameters { Worksheet = worksheetPart.Worksheet, ShareStringPart = shareStringPart, ColumnName = "D", RowIndex = rowIndex, Text = info.Purchases[j].ClientId.ToString(), StyleIndex = 1U }); rowIndex++; j++; } } rowIndex++; InsertCellInWorksheet(new ExcelCellParameters { Worksheet = worksheetPart.Worksheet, ShareStringPart = shareStringPart, ColumnName = "A", RowIndex = rowIndex, Text = "Общая сумма покупок", StyleIndex = 0U }); InsertCellInWorksheet(new ExcelCellParameters { Worksheet = worksheetPart.Worksheet, ShareStringPart = shareStringPart, ColumnName = "B", RowIndex = rowIndex, Text = info.Purchases[0].TotalCost.ToString(), StyleIndex = 0U }); workbookpart.Workbook.Save(); } }
/// <summary> /// 向当前工作区的表中插入一个单元格 /// </summary> /// <param name="worksheetPart">当前的工作区对象</param> /// <param name="cell">设置要插入的单元格信息</param> /// <returns>返回插入成功的数量</returns> public static bool InsertCell(this WorksheetPart worksheetPart, ExcelCell cell) { return(InsertCell(worksheetPart, new List <ExcelCell> { cell }) > 0); }
private static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart) { OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart, Encoding.UTF8); writer.WriteStartElement(new Worksheet()); 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 // uint rowIndex = 1; writer.WriteStartElement(new Row { RowIndex = rowIndex }); 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"); } 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; if (double.TryParse(cellValue, out cellFloatValue)) { cellValue = cellFloatValue.ToString(ci); AppendNumericCell(cellReference, cellValue, writer); } } else if (IsDateColumn[colInx]) { // This is a date value. 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(); dt.Clear(); }
public static async Task <IActionResult> Run( [HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)] HttpRequest req, ExecutionContext context, ILogger log) { log.LogInformation("C# HTTP trigger function processed a request."); //// 1. Get data from SQL DataSet ds = new DataSet(); using (SqlConnection connection = new SqlConnection(Environment.GetEnvironmentVariable("sqlConnection"))) { using (SqlDataAdapter dataAdapter = new SqlDataAdapter("select * from Test", connection)) { dataAdapter.Fill(ds); } } //// 2. Create and Format the excel using (SpreadsheetDocument workbook = SpreadsheetDocument.Create(context.FunctionDirectory + "\\test.xlsx", SpreadsheetDocumentType.Workbook)) { // Add a WorkbookPart to the document. WorkbookPart workbookPart = workbook.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); // Add a WorksheetPart to the WorkbookPart. WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(); Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets()); Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Test Sheet" }; sheets.Append(sheet); workbookPart.Workbook.Save(); SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData()); Row row = new Row(); foreach (DataTable dataTable in ds.Tables) { foreach (DataRow dataRow in dataTable.Rows) { row = new Row(); row.Append( ConstructCell(dataRow["id"].ToString(), CellValues.String), ConstructCell(dataRow["name"].ToString(), CellValues.String)); sheetData.AppendChild(row); } } workbookPart.Workbook.Save(); } //// 3. Uplaod the resulting excel to Storage blob CloudStorageAccount storageAccount = CloudStorageAccount.Parse(Environment.GetEnvironmentVariable("MyStorageConnectionAppSetting")); CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient(); CloudBlobContainer container = blobClient.GetContainerReference("test"); CloudBlockBlob blockBlob = container.GetBlockBlobReference("test.xlsx"); blockBlob.UploadFromFile(context.FunctionDirectory + "\\test.xlsx"); return(new OkResult()); }
private DataTable ReadExcelFileDOM(string filename) { DataTable table; using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filename, true)) { WorkbookPart workbookPart = myDoc.WorkbookPart; Sheet worksheet = workbookPart.Workbook.Descendants <Sheet>().First(); WorksheetPart worksheetPart = (WorksheetPart)(workbookPart.GetPartById(worksheet.Id)); SheetData sheetData = worksheetPart.Worksheet.Elements <SheetData>().First(); List <List <string> > totalRows = new List <List <string> >(); int maxCol = 0; foreach (Row r in sheetData.Elements <Row>())//Skip header row { // Add the empty row. string value = null; while (totalRows.Count < r.RowIndex - 1) { List <string> emptyRowValues = new List <string>(); for (int i = 0; i < maxCol; i++) { emptyRowValues.Add(""); } totalRows.Add(emptyRowValues); } List <string> tempRowValues = new List <string>(); foreach (Cell c in r.Elements <Cell>()) { #region get the cell value of c. if (c != null) { value = c.InnerText; // If the cell represents a numeric value, you are done. // For dates, this code returns the serialized value that // represents the date. The code handles strings and Booleans // individually. For shared strings, the code looks up the // corresponding value in the shared string table. For Booleans, // the code converts the value into the words TRUE or FALSE. if (c.DataType != null) { switch (c.DataType.Value) { case CellValues.SharedString: // For shared strings, look up the value in the shared // strings table. var stringTable = workbookPart. GetPartsOfType <SharedStringTablePart>().FirstOrDefault(); // If the shared string table is missing, something is // wrong. Return the index that you found in the cell. // Otherwise, look up the correct text in the table. if (stringTable != null) { value = stringTable.SharedStringTable. ElementAt(int.Parse(value)).InnerText; } break; case CellValues.Boolean: switch (value) { case "0": value = "FALSE"; break; default: value = "TRUE"; break; } break; } } Console.Write(value + " "); } #endregion // Add the cell to the row list. int i = Convert.ToInt32(c.CellReference.ToString().ToCharArray().First() - 'A'); // Add the blank cell in the row. while (tempRowValues.Count < i) { tempRowValues.Add(""); } tempRowValues.Add(value); } // add the row to the totalRows. maxCol = processList(tempRowValues, totalRows, maxCol); Console.WriteLine(); } table = ConvertListListStringToDataTable(totalRows, maxCol); } return(table); }
static void Main(string[] args) { //Get Template and create new file to edit System.IO.File.Copy("Templates\\MediumTemplate.xlsx", "CustomersReport.xlsx", true); //Generate Data List <CustomersReport> reportData = new CustomersReport().GetRandomData(); using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open("CustomersReport.xlsx", true)) { WorkbookPart wBookPart = null; wBookPart = spreadsheetDoc.WorkbookPart; wBookPart.Workbook = new Workbook(); spreadsheetDoc.WorkbookPart.Workbook.Sheets = new Sheets(); Sheets sheets = spreadsheetDoc.WorkbookPart.Workbook.GetFirstChild <Sheets>(); //Get worksheetpart WorksheetPart wSheetPart = spreadsheetDoc.WorkbookPart.WorksheetParts.First(); //Get existing workSheetPart WorksheetPart newWorksheetPart = spreadsheetDoc.WorkbookPart.WorksheetParts.First(); //add Styles WorkbookStylesPart stylesPart = spreadsheetDoc.WorkbookPart.WorkbookStylesPart; //stylesPart.Stylesheet = Styles.GenerateStyleSheet(); stylesPart.Stylesheet.Save(); string relationshipId = spreadsheetDoc.WorkbookPart.GetIdOfPart(newWorksheetPart); // Get a unique ID for the new worksheet. uint sheetId = 1; if (sheets.Elements <Sheet>().Count() > 0) { sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1; } // Give the new worksheet a name. Sheet sheet = new Sheet() { Id = spreadsheetDoc.WorkbookPart.GetIdOfPart(newWorksheetPart), SheetId = sheetId, Name = "Customer_Report" + sheetId }; sheets.Append(sheet); //get existing sheetData SheetData sheetData = newWorksheetPart.Worksheet.GetFirstChild <SheetData>(); foreach (CustomersReport data in reportData) { Row contentRow = new Row(); contentRow.Append(new Cell { DataType = CellValues.String, CellValue = new CellValue { Text = data.Name } }); contentRow.Append(new Cell { DataType = CellValues.String, CellValue = new CellValue { Text = data.RegisterDate } }); contentRow.Append(new Cell { DataType = CellValues.String, CellValue = new CellValue { Text = data.LastBuy } }); contentRow.Append(new Cell { DataType = CellValues.String, CellValue = new CellValue { Text = data.Item } }); contentRow.Append(new Cell { DataType = CellValues.Number, CellValue = new CellValue { Text = data.Quantity.ToString() } }); contentRow.Append(new Cell { DataType = CellValues.Number, CellValue = new CellValue { Text = data.ItemCost.ToString() } }); contentRow.Append(new Cell { DataType = CellValues.Number, CellValue = new CellValue { Text = string.Format("{0}", data.Quantity * data.ItemCost) } }); sheetData.AppendChild(contentRow); } } }
public static void ReadAirfieldData(ZandraUserPreferences userPreferences) { Dictionary <string, Point> points = userPreferences.EntryToValidPoint; var fileContent = string.Empty; var filePath = string.Empty; using (System.Windows.Forms.OpenFileDialog openFileDialog = new OpenFileDialog()) { openFileDialog.InitialDirectory = "c:\\"; openFileDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*"; openFileDialog.FilterIndex = 1; openFileDialog.RestoreDirectory = true; if (openFileDialog.ShowDialog() == DialogResult.OK) { //Get the path of specified file filePath = openFileDialog.FileName; ; // Open the document for reading. using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filePath, false)) { bool countryDataFound = false; WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart; WorksheetPart worksheetPart = GetWorksheetPart(workbookPart, "icao"); SheetData sheetData = null; if (worksheetPart != null) { countryDataFound = true; sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>(); } if (!countryDataFound) { MessageBox.Show("This File Does not contain the correct ICAO Aifield data sheet." + "Please choose a different file.", "Invalid File!"); } else { SharedStringTablePart stringTable = workbookPart.GetPartsOfType <SharedStringTablePart>() .FirstOrDefault(); Row r = null; if (sheetData != null) { r = sheetData.Elements <Row>().Where(row => row.RowIndex == 1).First(); } string nameColumn = null; string icaoColumn = null; string countryCodeColumn = null; //Get Column Letter char[] ch1 = { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' }; foreach (Cell c in r.Elements <Cell>()) { string cellvalue = stringTable.SharedStringTable .ElementAt(int.Parse(c.CellValue.InnerText)).InnerText; if (cellvalue == "ISO 3-Letter") { string tempString = c.CellReference; int index = tempString.IndexOfAny(ch1); countryCodeColumn = tempString.Substring(0, index); } else if (cellvalue == "Name") { string tempString = c.CellReference; int index = tempString.IndexOfAny(ch1); nameColumn = tempString.Substring(0, index); } else if (cellvalue == "ICAO") { string tempString = c.CellReference; int index = tempString.IndexOfAny(ch1); icaoColumn = tempString.Substring(0, index); } } if (nameColumn != null || countryCodeColumn != null || icaoColumn != null) { points.Clear(); foreach (Row row in sheetData) { if (row.RowIndex > 1) { string referenceString = nameColumn + row.RowIndex; if (sheetData.Descendants <Cell>(). Where(c => c.CellReference == referenceString). FirstOrDefault().CellValue.Text != null && sheetData.Descendants <Cell>(). Where(c => c.CellReference == referenceString). FirstOrDefault().CellValue.Text != "") { string cellValue; Cell cell; referenceString = nameColumn + row.RowIndex; string airfieldName; cell = sheetData.Descendants <Cell>().Where(c => c.CellReference == referenceString).FirstOrDefault(); if (cell.DataType.Value == CellValues.SharedString) { cellValue = cell.CellValue.InnerText; airfieldName = stringTable.SharedStringTable .ElementAt(int.Parse(cellValue)).InnerText; } else { cellValue = cell.CellValue.InnerText; airfieldName = cellValue; } referenceString = countryCodeColumn + row.RowIndex; string countryCode; cell = sheetData.Descendants <Cell>().Where(c => c.CellReference == referenceString).FirstOrDefault(); if (cell.DataType == null) { countryCode = null; } else if (cell.DataType.Value == CellValues.SharedString) { cellValue = cell.CellValue.InnerText; countryCode = stringTable.SharedStringTable .ElementAt(int.Parse(cellValue)).InnerText; } else { cellValue = cell.CellValue.InnerText; countryCode = cellValue; } referenceString = icaoColumn + row.RowIndex; string icao; cell = sheetData.Descendants <Cell>().Where(c => c.CellReference == referenceString).FirstOrDefault(); if (cell.DataType == null) { icao = null; } else if (cell.DataType.Value == CellValues.SharedString) { cellValue = cell.CellValue.InnerText; icao = stringTable.SharedStringTable .ElementAt(int.Parse(cellValue)).InnerText; } else { cellValue = cell.CellValue.InnerText; icao = cellValue; } Country country = null; try { country = userPreferences.Countries.First(c => c.Code == countryCode) as Country; } catch (System.InvalidOperationException) { continue; } if (country != null) { ObservableCollection <Country> borderingCountries = new ObservableCollection <Country>(); borderingCountries.Add(country); points.Add(icao, new Point( icao, true, false, false, airfieldName, borderingCountries)); } else { points.Add(icao, new Point( icao, true, false, false, airfieldName, null)); } } } } userPreferences.SaveMe(); } else { MessageBox.Show("This File Does not contain the correct ICAO Aifield data sheet." + "Please choose a different file.", "Invalid File!"); } } } } } }
public static void GenerateExcel(IEnumerable <IExcelRecord> records) { if (records.Any()) { var firstRecord = records.First(); Type recordType = firstRecord.GetType(); var props = recordType.GetProperties(); string fileName = "TestExcelFile.XLSX"; using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook)) { // Add a WorkbookPart to the document. WorkbookPart workbookPart = document.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); // Add a WorksheetPart to the WorkbookPart. WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(); Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets()); Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Tab1" }; sheets.Append(sheet); workbookPart.Workbook.Save(); SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData()); // Constructing header Row row = new Row(); foreach (var item in props) { var propattr = item.GetCustomAttributes(false); object attr = (from attribute in propattr where attribute.GetType() == typeof(ExcelColumnAttribute) select attribute).FirstOrDefault(); if (attr == null) { continue; } ExcelColumnAttribute excelColumnAttribute = (ExcelColumnAttribute)attr; row.Append(ConstructCell(excelColumnAttribute.DisplayName, CellValues.String)); } // Insert the header row to the Sheet Data sheetData.AppendChild(row); // Inserting each record foreach (var record in records) { var dataRow = new Row(); foreach (var property in props) { var propattr = property.GetCustomAttributes(false); object attr = (from attribute in propattr where attribute.GetType() == typeof(ExcelColumnAttribute) select attribute).FirstOrDefault(); if (attr == null) { continue; } var value = property.GetValue(record); //var value = property.GetValue(property, null); dataRow.Append(ConstructCell(value.ToString(), CellValues.String)); } sheetData.AppendChild(dataRow); } worksheetPart.Worksheet.Save(); } } }
/* * report.GetReportData() */ public ActionResult Export(Reports report) { try { var reportData = report.GetReportData(true); HttpContext.Response.SetCookie(new HttpCookie("fileDownload", "true") { Path = "/" }); var context = HttpContext.Response; context.Buffer = context.BufferOutput = false; context.Cache.SetCacheability(HttpCacheability.Private); context.Cache.SetExpires(DateTime.Now); context.ContentType = new ContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") { CharSet = "utf-8" }.ToString(); context.AppendHeader("Content-Disposition", new ContentDisposition { DispositionType = DispositionTypeNames.Attachment, FileName = string.Format(CultureInfo.InvariantCulture, RemoveInvalidFilePathCharacters(reportData.title) + "_{0:yyyyMMdd_HHmmss}.xlsx", DateTime.Now) }.ToString() ); context.AppendHeader("X-Content-Type-Options", "nosniff"); using (MemoryStream mDocument = new MemoryStream()) { // Using SAX using (SpreadsheetDocument document = SpreadsheetDocument.Create(mDocument, SpreadsheetDocumentType.Workbook)) { List <OpenXmlAttribute> attributes; document.AddWorkbookPart(); // Stylesheet WorkbookStylesPart stylesheet = document.WorkbookPart.AddNewPart <WorkbookStylesPart>(); stylesheet.Stylesheet = new Stylesheet(new Fonts( new Font(new Color() { Rgb = new HexBinaryValue() { Value = "000000" } }), // 0 new Font(new Bold()), // 1 new Font(new Color() { Rgb = new HexBinaryValue() { Value = "FF0000" } }) // 2 ), new Fills(new Fill() { }), new Borders(new Border() { }), new CellFormats( new CellFormat() { FontId = 0 }, // 0 new CellFormat() { FontId = 1, ApplyFont = true }, // 1 new CellFormat() { FontId = 2, ApplyFont = true } // 2 ) ); stylesheet.Stylesheet.Save(); WorksheetPart workSheetPart = document.WorkbookPart.AddNewPart <WorksheetPart>(); OpenXmlWriter writer = OpenXmlWriter.Create(workSheetPart); writer.WriteStartElement(new Worksheet()); writer.WriteStartElement(new SheetData()); IDictionary <string, object> firstRow = new Dictionary <string, object>() { { Words.Reports_Name, "" }, { Words.Reports_Value, "" } }; if (firstRow != null) { int row = 1; AddLine(writer, row, new string[] { reportData.title }); row++; AddLine(writer, row, new string[] { "" }); row++; attributes = new List <OpenXmlAttribute> { new OpenXmlAttribute("r", null, row.ToString()) }; writer.WriteStartElement(new Row(), attributes); int col1 = 1; foreach (var cols in firstRow.Keys.ToList()) { attributes = new List <OpenXmlAttribute> { new OpenXmlAttribute("t", null, "str"), new OpenXmlAttribute("r", "", GetColumnName(col1) + row), new OpenXmlAttribute("s", "", "1") // Bold (Style 1) }; writer.WriteStartElement(new Cell(), attributes); writer.WriteElement(new CellValue(cols)); writer.WriteEndElement(); col1++; } writer.WriteEndElement(); row++; for (int i = 0; i < reportData.labels.Length; i++) { attributes = new List <OpenXmlAttribute> { new OpenXmlAttribute("r", null, row.ToString()) }; writer.WriteStartElement(new Row(), attributes); int col = 1; var row2 = new Dictionary <string, object>() { { "Name", reportData.labels[i] }, { "Value", reportData.datasets[0].data[i] } }; foreach (var key in row2.Keys) { attributes = new List <OpenXmlAttribute> { new OpenXmlAttribute("t", null, "str"), new OpenXmlAttribute("r", "", GetColumnName(col) + row) }; if (row2[key] is decimal) { if ((decimal)row2[key] < 0) { attributes.Add(new OpenXmlAttribute("s", "", "2")); // Red (Style 2) } } else if (row2[key] is double) { if ((double)row2[key] < 0) { attributes.Add(new OpenXmlAttribute("s", "", "2")); // Red (Style 2) } } writer.WriteStartElement(new Cell(), attributes); writer.WriteElement(new CellValue(row2[key] != null ? row2[key].ToString() : "")); writer.WriteEndElement(); col++; } writer.WriteEndElement(); row++; } } else { // Empty row (no data found) attributes = new List <OpenXmlAttribute> { new OpenXmlAttribute("r", null, "1") }; writer.WriteStartElement(new Row(), attributes); attributes = new List <OpenXmlAttribute> { new OpenXmlAttribute("t", null, "str"), new OpenXmlAttribute("r", "", GetColumnName(1) + 1), new OpenXmlAttribute("s", "", "1") // Bold (Style 1) }; writer.WriteStartElement(new Cell(), attributes); writer.WriteElement(new CellValue("")); writer.WriteEndElement(); writer.WriteEndElement(); } writer.WriteEndElement(); writer.WriteEndElement(); writer.Close(); writer = OpenXmlWriter.Create(document.WorkbookPart); writer.WriteStartElement(new Workbook()); writer.WriteStartElement(new Sheets()); writer.WriteElement(new Sheet() { Name = "Sheet 1", SheetId = 1, Id = document.WorkbookPart.GetIdOfPart(workSheetPart) }); writer.WriteEndElement(); writer.WriteEndElement(); writer.Close(); document.Save(); document.Close(); mDocument.WriteTo(context.OutputStream); } } } catch (Exception ex) { ex.Log(); } return(null); }
static void WriteRandomValuesSAX(string filename, int numRows, int numCols) { using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filename, true)) { WorkbookPart workbookPart = myDoc.WorkbookPart; WorksheetPart worksheetPart = workbookPart.WorksheetParts.First(); string origninalSheetId = workbookPart.GetIdOfPart(worksheetPart); WorksheetPart replacementPart = workbookPart.AddNewPart <WorksheetPart>(); string replacementPartId = workbookPart.GetIdOfPart(replacementPart); OpenXmlReader reader = OpenXmlReader.Create(worksheetPart); OpenXmlWriter writer = OpenXmlWriter.Create(replacementPart); Row r = new Row(); Cell c = new Cell(); CellFormula f = new CellFormula(); f.CalculateCell = true; f.Text = "RAND()"; c.Append(f); CellValue v = new CellValue(); c.Append(v); while (reader.Read()) { if (reader.ElementType == typeof(SheetData)) { if (reader.IsEndElement) { continue; } writer.WriteStartElement(new SheetData()); for (int row = 0; row < numRows; row++) { writer.WriteStartElement(r); for (int col = 0; col < numCols; col++) { writer.WriteElement(c); } writer.WriteEndElement(); } writer.WriteEndElement(); } else { if (reader.IsStartElement) { writer.WriteStartElement(reader); } else if (reader.IsEndElement) { writer.WriteEndElement(); } } } reader.Close(); writer.Close(); Sheet sheet = workbookPart.Workbook.Descendants <Sheet>().Where(s => s.Id.Value.Equals(origninalSheetId)) .First(); sheet.Id.Value = replacementPartId; workbookPart.DeletePart(worksheetPart); } }
private static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart, bool writeHeader) { var worksheet = worksheetPart.Worksheet; var sheetData = worksheet.GetFirstChild <SheetData>(); string cellValue = ""; // 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[] IsNumericColumn = 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 // uint rowIndex = 1; var headerRow = new Row { RowIndex = rowIndex }; // add a row at the top of spreadsheet sheetData.Append(headerRow); for (int colInx = 0; colInx < numberOfColumns; colInx++) { DataColumn col = dt.Columns[colInx]; if (writeHeader) { AppendTextCell(excelColumnNames[colInx] + "1", col.ColumnName, headerRow); } IsNumericColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Int32") || col.ColumnName.StartsWith("Count") || col.ColumnName.StartsWith("Amount") || col.ColumnName.StartsWith("Budget"); } // // Now, step through each row of data in our DataTable... // double cellNumericValue = 0; foreach (DataRow dr in dt.Rows) { // ...create a new row, and append a set of this row's data to it. ++rowIndex; var newExcelRow = new Row { RowIndex = rowIndex }; // add a row at the top of spreadsheet sheetData.Append(newExcelRow); for (int colInx = 0; colInx < numberOfColumns; colInx++) { cellValue = dr.ItemArray[colInx].ToString(); // Create cell with data if (IsNumericColumn[colInx]) { // For numeric cells, make sure our input data IS a number, then write it out to the Excel file. // If this numeric value is NULL, then don't write anything to the Excel file. cellNumericValue = 0; if (double.TryParse(cellValue, out cellNumericValue)) { cellValue = cellNumericValue.ToString(); AppendNumericCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, newExcelRow); } else { AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, newExcelRow); } } else { // For text cells, just write the input data straight out to the Excel file. AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, newExcelRow); } } } }
/// <summary> /// Add cell width styles. /// </summary> /// <param name="minCol">Minimum column index.</param> /// <param name="maxCol">Maximum column index.</param> /// <param name="maxWidth">Maximum column width.</param> /// <param name="spreadSheet">Spread sheet.</param> /// <param name="workSheetPart">Work sheet.</param> private static void AddCellWidthStyles(uint minCol, uint maxCol, int maxWidth, SpreadsheetDocument spreadSheet, WorksheetPart workSheetPart) { Columns cols = new Columns(new Column() { CustomWidth = true, Min = minCol, Max = maxCol, Width = maxWidth, BestFit = false }); workSheetPart.Worksheet.InsertBefore<Columns>(cols, workSheetPart.Worksheet.GetFirstChild<SheetData>()); }