public Cell(Worksheet sheet, string cellReference) { Sheet = sheet; Xml = new Spreadsheet.Cell( new Spreadsheet.Value { }) { CellReference = new StringValue(cellReference) }; }
private static DocumentFormat.OpenXml.Spreadsheet.Cell OpenXML_CreateBoolean(int nColumnIndex, int nRowIndex, string sText) { DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cell.CellReference = OpenXML_GetColumnName(nColumnIndex) + nRowIndex.ToString(); cell.DataType = CellValues.Boolean; cell.CellValue = new CellValue(sText); return cell; }
// Add a cell with the specified address to a row. private static DocumentFormat.OpenXml.Spreadsheet.Cell CreateCell(Row row, String address) { DocumentFormat.OpenXml.Spreadsheet.Cell cellResult; DocumentFormat.OpenXml.Spreadsheet.Cell refCell = null; // Cells must be in sequential order according to CellReference. // Determine where to insert the new cell. foreach (DocumentFormat.OpenXml.Spreadsheet.Cell cell in row.Elements<DocumentFormat.OpenXml.Spreadsheet.Cell>()) { // if (string.Compare(cell.CellReference.Value, address, true) > 0) if (CompareCellName(cell.CellReference.Value, address) > 0) { refCell = cell; break; } } cellResult = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cellResult.CellReference = address; row.InsertBefore(cellResult, refCell); return cellResult; }
private static DocumentFormat.OpenXml.Spreadsheet.Cell OpenXML_CreateDate(int nColumnIndex, int nRowIndex, DateTime dtValue, UInt32Value styleId) { DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cell.CellReference = OpenXML_GetColumnName(nColumnIndex) + nRowIndex.ToString(); cell.DataType = CellValues.Date; cell.CellValue = new CellValue(dtValue.ToUniversalTime().ToString("s")); cell.StyleIndex = styleId; return cell; }
private void ExportDatatableToExcel(DataTable table, string destination) { using (var workbook = SpreadsheetDocument.Create(destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook)) { var workbookPart = workbook.AddWorkbookPart(); workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook(); workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets(); uint sheetId = 1; var sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>(); var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData(); sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData); DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>(); string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart); if (sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0) { sheetId = sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1; } DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName }; sheets.Append(sheet); DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row(); List <String> columns = new List <string>(); foreach (DataColumn column in table.Columns) { columns.Add(column.ColumnName); DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName); headerRow.AppendChild(cell); } sheetData.AppendChild(headerRow); foreach (DataRow dsrow in table.Rows) { DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row(); foreach (String col in columns) { DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); // newRow.AppendChild(cell); } sheetData.AppendChild(newRow); } } }
private static bool Render(TextElement element, ICollection <Style> styles, SheetData shData, WorkbookPart workbookpart) { uint styleId; var row = shData.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Row>(new DocumentFormat.OpenXml.Spreadsheet.Row()); styleId = GetStyleId(element.Style, styles); var value = element.Value; if (element.Value == InternalContants.NewLine) { value = String.Empty; } if (element.Value == InternalContants.NewSection) { value = String.Empty; } var cell = new DocumentFormat.OpenXml.Spreadsheet.Cell { CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(value), DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String, StyleIndex = UInt32Value.FromUInt32(styleId + 1) }; row.AppendChild(cell); workbookpart.Workbook.Save(); return(false); }
/// <summary> /// Gets cell values. /// </summary> /// <param name="cFormula"> /// Cell range formula (c:f). /// <c:cat> /// <c:strRef> /// <c:f> /// Sheet1!$A$2:$A$3 /// </c:f> /// </c:strRef> /// </c:cat> /// </param> /// <param name="slideChart"></param> internal static List <X.Cell> GetXCellsByFormula(C.Formula cFormula, SlideChart slideChart) { // Get all <x:c> elements of formula sheet string filteredFormula = GetFilteredFormula(cFormula); string[] sheetNameAndCellsRange = filteredFormula.Split('!'); //eg: Sheet1!A2:A5 -> ['Sheet1', 'A2:A5'] WorkbookPart workbookPart = slideChart.ChartWorkbook.WorkbookPart; string chartSheetName = sheetNameAndCellsRange[0]; string chartSheetId = workbookPart.Workbook.Sheets.Elements <X.Sheet>() .First(xSheet => xSheet.Name == chartSheetName).Id; WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(chartSheetId); IEnumerable <X.Cell> allXCells = worksheetPart.Worksheet.GetFirstChild <X.SheetData>().ChildElements .SelectMany(e => e.Elements <X.Cell>()); //TODO: use HashSet List <string> formulaCellAddressList = new CellFormulaParser(sheetNameAndCellsRange[1]).GetCellAddresses(); var xCells = new List <X.Cell>(formulaCellAddressList.Count); foreach (string address in formulaCellAddressList) { X.Cell xCell = allXCells.First(xCell => xCell.CellReference == address); xCells.Add(xCell); } return(xCells); }
private DocumentFormat.OpenXml.Spreadsheet.Row MakeXLSXInfoRow(string title, string value, uint rowindex) { DocumentFormat.OpenXml.Spreadsheet.Row row = new DocumentFormat.OpenXml.Spreadsheet.Row() { RowIndex = rowindex }; DocumentFormat.OpenXml.Spreadsheet.Cell refCell = null; DocumentFormat.OpenXml.Spreadsheet.Cell newCell = new DocumentFormat.OpenXml.Spreadsheet.Cell() { CellReference = "A" + rowindex.ToString() }; row.InsertBefore(newCell, refCell); string cellValue = title + ": "; if (!String.IsNullOrEmpty(value)) { cellValue += value; } else { cellValue += "N/A"; } newCell.CellValue = new CellValue(cellValue); newCell.DataType = new EnumValue <CellValues>(CellValues.String); newCell.StyleIndex = 3; return(row); }
public static string ReadCell(Excel.Cell cell, StringKeyList buffer = null) { string value = cell.CellValue?.InnerText ?? string.Empty; if (cell.DataType != null) { if (cell.DataType.Value == Excel.CellValues.SharedString) { // shared strings table. if (int.TryParse(value, out int val)) { //if (strings.SharedStringTable.ChildElements.Count > val) if (buffer != null) { value = buffer[val]?.Key; } } } else if (cell.DataType.Value == Excel.CellValues.Boolean) { switch (value) { case "0": value = "FALSE"; break; default: value = "TRUE"; break; } } } return(value); }
/// <summary> /// Sets the data type and value of the cell according to the type of the supplied value /// </summary> /// <param name="cell">The cell into which the value is to be written</param> /// <param name="value">The value to be written into the cell</param> public void SetCellValue(OpenXmlSpreadsheet.Cell cell, object value) { // Convert the supplied type to some underlying type that Excel can interpret. value = ToUnderlyingTypeValue(value); // Convert this underlying type to an Excel string string excelString = ToExcelString(value); // Set the DataType only if we are setting the value to a string. if ((value is string) || (value is bool) || (value is bool?)) { cell.DataType = new DocumentFormat.OpenXml.EnumValue <OpenXmlSpreadsheet.CellValues>(OpenXmlSpreadsheet.CellValues.String); } else { cell.DataType = null; } cell.CellValue = new OpenXmlSpreadsheet.CellValue(excelString); // Preserve white space in our cell values if it is a string. if (value is string) { cell.CellValue.Space = DocumentFormat.OpenXml.SpaceProcessingModeValues.Preserve; } }
private static ColRow ReverseColumnRow(Cell cell) { int columnCode, rowCode; ColumnRow(cell, out columnCode, out rowCode); return(new ColRow(rowCode, columnCode)); }
private void Summarize(MSOpenXML.SheetData ws, int row, List <int[]> totais) { for (int i = 0; i < _columns.Count; i++) { string lCommand = ""; switch (_columns[i].SummaryOperation) { case ESummaryOperation.None: break; case ESummaryOperation.Sum: lCommand = "SUBTOTAL(9,"; break; case ESummaryOperation.Average: lCommand = "AVERAGE("; break; case ESummaryOperation.Count: lCommand = ""; break; case ESummaryOperation.Multiply: lCommand = "PRODUCT("; break; case ESummaryOperation.Acumulate: throw new InvalidOperationException("Acumulate is not supported by MS Excel."); case ESummaryOperation.Mode: lCommand = "MODE("; break; default: break; } MSOpenXML.Cell c = CreateCellIfNotExist(ws.Parent as MSOpenXML.Worksheet, CellName(row, i + _groups.Count)); if (lCommand != "") { c.CellFormula = new MSOpenXML.CellFormula("{0}{1})".Formata(lCommand, totais.Aggregate <int[], string>("", (r, n) => r = r + "," + GetColumnName(i + _groups.Count) + n[0] + ":" + GetColumnName(i + _groups.Count) + n[1]).Right(-1))); } if (_columns[i].SummaryOperation == ESummaryOperation.Count) { c.CellFormula = new MSOpenXML.CellFormula(totais.Aggregate <int[], string>("", (r, n) => r = r + "+ROWS(" + GetColumnName(i + _groups.Count) + n[0] + ":" + GetColumnName(i + _groups.Count) + n[1] + ")").Right(-1)); } } }
private static async Task ProcessRow(Row row, int Id) { using (HttpClient client = new HttpClient()) { client.DefaultRequestHeaders.Accept.Add( new System.Net.Http.Headers.MediaTypeWithQualityHeaderValue("application/json")); client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Basic", Convert.ToBase64String( System.Text.ASCIIEncoding.ASCII.GetBytes( string.Format("{0}:{1}", "", ConfigurationManager.AppSettings["PersonalAccessToken"])))); using (HttpResponseMessage response = await client.GetAsync( String.Format("{0}/_apis/wit/workItems/{1}", ConfigurationManager.AppSettings["RestApiBaseUri"], Id))) { response.EnsureSuccessStatusCode(); string responseBody = await response.Content.ReadAsStringAsync(); dynamic data = JObject.Parse(responseBody); DocumentFormat.OpenXml.Spreadsheet.Cell newcell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); newcell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; newcell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue( Regex.Replace((string)(data.fields["System.Description"].Value), "<.*?>", String.Empty).Replace(""", "\"").Replace(" ", "")); row.Append(newcell); } } }
private static void ColumnRow(Cell cell, out string column, out string row) { var m = Regex.Match(cell.CellReference.Value, @"^(?<Column>[A-Z]+)(?<Row>[0-9]+)$", RegexOptions.IgnoreCase); column = m.Groups["Column"].Value; row = m.Groups["Row"].Value; }
public static void UpdateValue( WorkbookPart wbPart, Worksheet ws, string addressName, string value, UInt32Value styleIndex, bool isString) { DocumentFormat.OpenXml.Spreadsheet.Cell cell = InsertCellInWorksheet(ws, addressName); if (isString) { // Either retrieve the index of an existing string, // or insert the string into the shared string table // and get the index of the new item. int stringIndex = InsertSharedStringItem(wbPart, value); cell.CellValue = new CellValue(stringIndex.ToString()); cell.DataType = new EnumValue <CellValues>(CellValues.SharedString); } else { cell.CellValue = new CellValue(value); cell.DataType = new EnumValue <CellValues>(CellValues.Number); } if (styleIndex > 0) { cell.StyleIndex = styleIndex; } // Save the worksheet. ws.Save(); }
//new public void ExportDSToExcel(DataSet ds, string destination) { using (var workbook = SpreadsheetDocument.Create(destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook)) { var workbookPart = workbook.AddWorkbookPart(); workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook(); workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets(); uint sheetId = 1; foreach (DataTable table in ds.Tables) { var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>(); var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData(); sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData); DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>(); string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart); if (sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0) { sheetId = sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1; } DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName }; sheets.Append(sheet); DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row(); List<String> columns = new List<string>(); foreach (DataColumn column in table.Columns) { columns.Add(column.ColumnName); DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName); headerRow.AppendChild(cell); } sheetData.AppendChild(headerRow); foreach (DataRow dsrow in table.Rows) { DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row(); foreach (String col in columns) { DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); // newRow.AppendChild(cell); } sheetData.AppendChild(newRow); } } } }
private string GetSharedString(eCell cell, WorkbookPart wbPart) { var stringTable = wbPart.GetPartsOfType <SharedStringTablePart>() .First(); var value = int.Parse(cell.InnerText); return(stringTable.SharedStringTable.ElementAt(value).InnerText); }
private static string GetCellValue(SharedStringTable sharedStrings, DocumentFormat.OpenXml.Spreadsheet.Cell cell) { return(cell.DataType != null && cell.DataType.HasValue && cell.DataType == CellValues.SharedString ? sharedStrings.ChildElements[ int.Parse(cell.CellValue.InnerText)].InnerText : cell.CellValue.InnerText); }
// parameters: // nRowIndex 行号。从 0 开始计算 public void WriteExcelLine( int nRowIndex, List <CellData> cells, WriteExcelLineStyle style = WriteExcelLineStyle.AutoString) { // 创建一个 Row。不负责查重 // row 的 cell 创建完成后,记得 ws.Save() Row row = ExcelUtil.CreateRow( worksheetPart.Worksheet, (UInt32)(nRowIndex + 1)); foreach (CellData data in cells) { string strCellName = GetColumnName(data.Col) + (nRowIndex + 1).ToString(); // 追加一个 Cell DocumentFormat.OpenXml.Spreadsheet.Cell cell = ExcelUtil.AppendCell( row, strCellName); bool isString = data.IsString; if ((style & WriteExcelLineStyle.AutoString) == WriteExcelLineStyle.AutoString) { isString = !IsExcelNumber(data.Value); } if (isString) { if ((style & WriteExcelLineStyle.ShareString) == WriteExcelLineStyle.ShareString) { // Either retrieve the index of an existing string, // or insert the string into the shared string table // and get the index of the new item. int stringIndex = ExcelUtil.InsertSharedStringItem(this.workbookpart, data.Value); cell.CellValue = new CellValue(stringIndex.ToString()); cell.DataType = new EnumValue <CellValues>(CellValues.SharedString); } else { cell.CellValue = new CellValue(data.Value); cell.DataType = new EnumValue <CellValues>(CellValues.String); } } else { cell.CellValue = new CellValue(data.Value); cell.DataType = new EnumValue <CellValues>(CellValues.Number); } if (data.StyleIndex > 0) { cell.StyleIndex = data.StyleIndex; } } }
public Excel.Cell GetCell(object value, int c, int r, uint styleIndex, StringKeyList sharedStrings) { Excel.Cell cell = new Excel.Cell() { CellReference = Helper.IntToChar(c) + (r).ToString(), StyleIndex = styleIndex, }; WriteCell(cell, value, sharedStrings); return(cell); }
private static Cell GetCell(Row row, DocumentFormat.OpenXml.Spreadsheet.Cell p) { return(new Cell { OXmlCell = p, Row = row, Column = row.Sheet.Columns().First(t => t.ColumnIndex == ColumnAddressToIndex(SplitAddress(p.CellReference)[0])), Value = p.CellValue != null ? p.CellValue.Text : p.InnerText }); }
private static DocumentFormat.OpenXml.Spreadsheet.Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart) { DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet = worksheetPart.Worksheet; SheetData sheetData = worksheet.GetFirstChild <SheetData>(); string cellReference = columnName + rowIndex; DocumentFormat.OpenXml.Spreadsheet.Row lastRow = sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().LastOrDefault(); // If the worksheet does not contain a row with the specified row index, insert one. DocumentFormat.OpenXml.Spreadsheet.Row row; if (sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(r => r.RowIndex == rowIndex).Count() != 0) { row = sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(r => r.RowIndex == rowIndex).First(); //set auto height -- don't know how this line is worked sheetData.InsertAfter(new DocumentFormat.OpenXml.Spreadsheet.Row() { RowIndex = (lastRow.RowIndex + 1) }, lastRow); } else { row = new DocumentFormat.OpenXml.Spreadsheet.Row() { RowIndex = rowIndex }; sheetData.Append(row); } // If there is not a cell with the specified column name, insert one. if (row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0) { return(row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(c => c.CellReference.Value == cellReference).First()); } else { // Cells must be in sequential order according to CellReference. Determine where to insert the new cell. DocumentFormat.OpenXml.Spreadsheet.Cell refCell = null; foreach (DocumentFormat.OpenXml.Spreadsheet.Cell cell in row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>()) { if (string.Compare(cell.CellReference.Value, cellReference, true) > 0) { refCell = cell; break; } } DocumentFormat.OpenXml.Spreadsheet.Cell newCell = new DocumentFormat.OpenXml.Spreadsheet.Cell() { CellReference = cellReference }; row.InsertBefore(newCell, refCell); worksheet.Save(); return(newCell); } }
public void OpenXmlSax() { using var xl = SpreadsheetDocument.Create(Stream, SpreadsheetDocumentType.Workbook); xl.CompressionOption = CompressionOption.SuperFast; var workbookPart = xl.AddWorkbookPart(); var wsp = workbookPart.AddNewPart<WorksheetPart>(); var oxw = OpenXmlWriter.Create(wsp); oxw.WriteStartElement(new DocumentFormat.OpenXml.Spreadsheet.Worksheet()); oxw.WriteStartElement(new SheetData()); var rowObject = new Row(); var cellAttributes = new[] { new OpenXmlAttribute("t", "", "inlineStr") }; var cell = new OpenXmlCell(); var inlineString = new InlineString(); for (var row = 0; row < NumberOfRows; ++row) { var rowAttributes = new[] { new OpenXmlAttribute("r", "", (row + 1).ToString()) }; oxw.WriteStartElement(rowObject, rowAttributes); var rowValues = Values[row]; for (var col = 0; col < rowValues.Count; ++col) { oxw.WriteStartElement(cell, cellAttributes); oxw.WriteStartElement(inlineString); oxw.WriteElement(new Text(rowValues[col])); oxw.WriteEndElement(); oxw.WriteEndElement(); } oxw.WriteEndElement(); } oxw.WriteEndElement(); oxw.WriteEndElement(); oxw.Close(); oxw = OpenXmlWriter.Create(workbookPart); oxw.WriteStartElement(new Workbook()); oxw.WriteStartElement(new Sheets()); oxw.WriteElement(new Sheet() { Name = "Sheet1", SheetId = 1, Id = workbookPart.GetIdOfPart(wsp) }); oxw.WriteEndElement(); oxw.WriteEndElement(); oxw.Close(); xl.Close(); }
static void ReadCell(Row row, Excel.Cell importCell, Dictionary <string, string> sharedStringTable, Dictionary <int, CellFormat> cellFormats) { string cellIndex = importCell.CellReference; string columnIndex = cellIndex.Substring(0, cellIndex.IndexOfAny("0123456789".ToCharArray())).ToUpper(); int index = 0; while (columnIndex.Length > 1) { index += ((byte)columnIndex[0]) - 64; index = index * 26; columnIndex = columnIndex.Substring(1); } index += ((byte)columnIndex[0]) - 65; Column column = row._rows._sheet.Column(index); Cell cell = row.Cell(column); if (importCell.CellValue != null) { if (importCell.DataType == null) { importCell.DataType = new DocumentFormat.OpenXml.EnumValue <Excel.CellValues>(Excel.CellValues.Number); } switch (importCell.DataType.Value) { case DocumentFormat.OpenXml.Spreadsheet.CellValues.String: cell.StringValue = importCell.CellValue.Text; break; case DocumentFormat.OpenXml.Spreadsheet.CellValues.Number: cell.NumberValue = Convert.ToDouble(importCell.CellValue.Text); break; case DocumentFormat.OpenXml.Spreadsheet.CellValues.SharedString: cell.StringValue = sharedStringTable[importCell.CellValue.Text]; break; case DocumentFormat.OpenXml.Spreadsheet.CellValues.Boolean: cell.BooleanValue = importCell.CellValue.Text == "1" ? true : false; break; case DocumentFormat.OpenXml.Spreadsheet.CellValues.Error: break; default: throw new Exception("Unsupported type"); } if (importCell.StyleIndex != null && importCell.StyleIndex >= 0 && cellFormats.ContainsKey((int)(uint)importCell.StyleIndex)) { cell.CellFormat = cellFormats[(int)(uint)importCell.StyleIndex]; } } }
private static bool Render(TableElement element, ICollection <Style> styles, SheetData shData, WorkbookPart workbookpart) { uint styleId; if (element.Table.Rows.Count == 0) { //throw new Exception("The table is empty"); return(true); } if (element.Headers.Count != 0) { styleId = GetStyleId(element.HeaderStyle, styles); var row = shData.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Row()); foreach (var head in element.Headers) { var cell = new DocumentFormat.OpenXml.Spreadsheet.Cell { CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(head), DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String, StyleIndex = UInt32Value.FromUInt32(styleId + 1) }; row.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Cell>(cell); } workbookpart.Workbook.Save(); } styleId = GetStyleId(element.TableStyle, styles); for (int i = 0; i < element.Table.Rows.Count; i++) { var row = shData.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Row()); for (int j = 0; j < element.Table.Columns.Count; j++) { var cell = new DocumentFormat.OpenXml.Spreadsheet.Cell { CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(element.Table.Rows[i][j].ToString()), DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String, StyleIndex = UInt32Value.FromUInt32(styleId + 1) }; row.AppendChild(cell); } } workbookpart.Workbook.Save(); return(false); }
private int PrintGroupFooter(MSOpenXML.SheetData ws, int row, object[] lLastKey, List <int[]>[] lGroupIndexes, int i) { MergeRange(ws.Parent as MSOpenXML.Worksheet, CellName(row, i), CellName(row, _groups.Count - 1)); //ws.Range[ws.Cells[row, i + 1], ws.Cells[row, _columns.Count + _groups.Count]].Font.Bold = true; //ws.Range[ws.Cells[row, i + 1], ws.Cells[row, _columns.Count + _groups.Count]].Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThin; MSOpenXML.Cell c = CreateCellIfNotExist(ws.Parent as MSOpenXML.Worksheet, CellName(row, i)); c.CellFormula = new MSOpenXML.CellFormula("CONCATENATE(\"Total \'" + lLastKey[i] + "\': \"," + lGroupIndexes[i].Aggregate <int[], string>("", (r, n) => r = r + "+ROWS(" + GetColumnName(_groups.Count) + n[0] + ":" + GetColumnName(_groups.Count + _columns.Count - 1) + n[1] + ")").Right(-1) + ")"); Summarize(ws, row, lGroupIndexes[i]); (c.Parent as MSOpenXML.Row).Descendants <MSOpenXML.Cell>().ForEach(x => x.StyleIndex = 6); lGroupIndexes[i].Clear(); return(++row); }
public string ReadExcelCell(Cell cell, WorkbookPart workbookPart) { CellValue cellValue = cell.CellValue; string text = (cellValue == null) ? cell.InnerText : cellValue.Text; string textEmail = (cellValue == null) ? cell.InnerText : cellValue.Text; if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString)) { text = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(Convert.ToInt32(cell.CellValue.Text)).InnerText; } return((text ?? string.Empty).Trim()); }
/// <summary> /// Processes the paragraph part. /// </summary> /// <param name="wkb">The WKB.</param> /// <param name="part">The part.</param> /// <param name="placeholder">The placeholder.</param> /// <param name="mapping">The mapping.</param> /// <param name="worksheetPart">The worksheet part.</param> /// <param name="reportWSPart">The report ws part.</param> /// <returns></returns> private static DrawingSpreadsheet.Shape ProcessParagraphPart(OpenXmlSpreadsheet.Workbook wkb, ExportPart part, MappingPlaceholder placeholder, ParagraphMapping mapping, WorksheetPart worksheetPart, WorksheetPart reportWSPart) { string SheetName = ""; uint RowStart = 0; uint RowEnd = 0; uint ColStart = 0; uint ColEnd = 0; // if no placeholder is specified then return now if (placeholder == null) { return(null); } DrawingSpreadsheet.Shape matchShape = worksheetPart.GetShapeByName(placeholder.Id); if (matchShape == null) { return(null); } // save the location of this shape, // this information will be used to position the incoming chart Extents extents = matchShape.ShapeProperties.Transform2D.Extents; Offset offset = matchShape.ShapeProperties.Transform2D.Offset; OpenXmlSpreadsheet.DefinedName rtfXmlDefinedName = wkb.GetDefinedNameByName(string.Format("{0}_{1}", part.DataSheetName, mapping.SourceFieldName)); wkb.BreakDownDefinedName(rtfXmlDefinedName, ref SheetName, ref RowStart, ref RowEnd, ref ColStart, ref ColEnd); WorksheetPart wksp = wkb.GetWorksheetPartByName(SheetName); OpenXmlSpreadsheet.SheetData sheetData = wksp.Worksheet.GetFirstChild <OpenXmlSpreadsheet.SheetData>(); OpenXmlSpreadsheet.Cell rtfXmlCell = sheetData.GetCell(ColStart, RowStart + 1); // Use the cell on the hidden data sheet as source for the XAML reader Section RTFSection = XamlSectionDocumentReader(rtfXmlCell.CellValue.InnerText); // The paragraph in the cell.inlinestring have a very different class structure to the paragraphs in the shape.textbody // So, the paragraph will need to go through a converter to do this. DrawingSpreadsheet.Shape targetShape = ConvertParagraph(worksheetPart, RTFSection, matchShape); // positon the new graphic frame after the shape its going to replace matchShape.Parent.InsertAfter <OpenXmlElement>(targetShape, matchShape); matchShape.Remove(); return(targetShape); }
private static void GenerateExcel(string excelFilePath, DataTable dataTable) { using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(excelFilePath, true)) { WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart; IEnumerable <Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>(); Sheet sheet = sheets.SingleOrDefault(s => s.Name == "EC 2 SAP Analysis"); if (sheet == null) { return; } string relationshipId = sheet.Id.Value; WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId); Worksheet workSheet = worksheetPart.Worksheet; SheetData sheetData = workSheet.GetFirstChild <SheetData>(); //DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row(); List <String> columns = new List <string>(); foreach (System.Data.DataColumn column in dataTable.Columns) { columns.Add(column.ColumnName); //DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); //cell.DataType = CellValues.String; //cell.CellValue = new CellValue(column.ColumnName); //headerRow.AppendChild(cell); } //sheetData.AppendChild(headerRow); foreach (DataRow dsrow in dataTable.Rows) { DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row(); foreach (String col in columns) { DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cell.DataType = CellValues.String; cell.CellValue = new CellValue(dsrow[col].ToString()); newRow.AppendChild(cell); } sheetData.AppendChild(newRow); } workbookPart.Workbook.Save(); } }
}//end method public static MemoryStream ExportDataSet(DataSet ds) { MemoryStream ms = new MemoryStream(); using (var workbook = SpreadsheetDocument.Create(ms, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook)) { var workbookPart = workbook.AddWorkbookPart(); workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook(); workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets(); foreach (System.Data.DataTable table in ds.Tables) { var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>(); var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData(); sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData); DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>(); string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart); uint sheetId = 1; if (sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 1) { sheetId = sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1; } DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName }; sheets.Append(sheet); DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row(); List<String> columns = new List<String>(); foreach (System.Data.DataColumn column in table.Columns) { columns.Add(column.ColumnName); DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName); headerRow.AppendChild(cell); }//end loop sheetData.AppendChild(headerRow); foreach (System.Data.DataRow dsrow in table.Rows) { DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row(); foreach (String col in columns) { DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); newRow.AppendChild(cell); }//end loop sheetData.AppendChild(newRow); }//end loop }//end loop workbookPart.Workbook.Save(); workbook.Close(); }//end using ms.Position = 0; return ms; }//end method
/// <summary> /// Inserts the text. /// </summary> /// <param name="spreadSheetData">The spread sheet data.</param> /// <param name="sharedStringPart">The share string part.</param> /// <param name="rowIndex">Index of the row.</param> /// <param name="column">The column.</param> /// <param name="text">The text.</param> /// <param name="cellValues">The cell values.</param> protected static void InsertText(SpreadSheet.SheetData spreadSheetData, SharedStringTablePart sharedStringPart, string column, int rowIndex, string text, SpreadSheet.CellValues cellValues) { int sharedStringIndex = 0; rowIndex += 1; if (cellValues == SpreadSheet.CellValues.SharedString) { sharedStringIndex = InsertSharedStringItem(sharedStringPart, text); } SpreadSheet.Cell cell = InsertCellInWorksheet(spreadSheetData, column, (uint)rowIndex); cell.CellValue = new SpreadSheet.CellValue(cellValues == SpreadSheet.CellValues.SharedString ? sharedStringIndex.ToString() : text); cell.DataType = new EnumValue <SpreadSheet.CellValues>(cellValues); }
private string GetValue(SpreadsheetDocument doc, Cell cell) { //if (cell.CellValue.InnerText == "") //{ // string emptymsg = "Required"; // var text = doc.WorkbookPart.SharedStringTablePart.SharedStringTable.ChildElements.GetItem(int.Parse(value)).InnerText; //} string value = cell.CellValue.InnerText; if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString) { return(doc.WorkbookPart.SharedStringTablePart.SharedStringTable.ChildElements.GetItem(int.Parse(value)).InnerText); } return(value); }
private string GetCellValue(WorkbookPart workbook, OpenXmlReader reader) { OpenXML.Cell c = (OpenXML.Cell)reader.LoadCurrentElement(); if (c.DataType != null && c.DataType == OpenXML.CellValues.SharedString) { OpenXML.SharedStringItem ssi = workbook.SharedStringTablePart. SharedStringTable.Elements <OpenXML.SharedStringItem>().ElementAt (int.Parse(c.CellValue.InnerText)); return(ssi.Text.Text); } else { return(c.CellValue.InnerText); } }
/* To create cell in Excel */ private void InsertCell(string value, CellValues dataType, int cell_num, Row row_index) { var t = row_index.RowIndex.ToString() + ":" + cell_num.ToString(); Cell refCell = null; var newCell = new Cell() { CellValue = new CellValue(value), DataType = new EnumValue <CellValues>(dataType), CellReference = t }; row_index.InsertBefore(newCell, refCell); newCell.CellValue = new CellValue(value); newCell.DataType = new EnumValue <CellValues>(dataType); }
private static DocumentFormat.OpenXml.Spreadsheet.Cell OpenXML_CreateNumber(int nColumnIndex, int nRowIndex, string sText, UInt32Value styleId) { DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cell.CellReference = OpenXML_GetColumnName(nColumnIndex) + nRowIndex.ToString(); cell.DataType = CellValues.Number; cell.CellValue = new CellValue(sText); cell.StyleIndex = styleId; return cell; }
/// <summary> /// Sets a cell value. The row and the cell are created if they do not exist. If the cell exists, the contents of the cell is overwritten /// </summary> /// <param name="spreadsheet">Spreadsheet to use</param> /// <param name="worksheet">Worksheet to use</param> /// <param name="columnIndex">Index of the column</param> /// <param name="rowIndex">Index of the row</param> /// <param name="valueType">Type of the value</param> /// <param name="value">The actual value</param> /// <param name="styleIndex">Index of the style to use. Null if no style is to be defined</param> /// <param name="save">Save the worksheet?</param> /// <returns>True if succesful</returns> private static bool SetCellValue(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet, uint columnIndex, uint rowIndex, DocumentFormat.OpenXml.Spreadsheet.CellValues valueType, string value, uint? styleIndex, bool save = true) { DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = worksheet.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.SheetData>(); DocumentFormat.OpenXml.Spreadsheet.Row row; DocumentFormat.OpenXml.Spreadsheet.Row previousRow = null; DocumentFormat.OpenXml.Spreadsheet.Cell cell; DocumentFormat.OpenXml.Spreadsheet.Cell previousCell = null; DocumentFormat.OpenXml.Spreadsheet.Columns columns; DocumentFormat.OpenXml.Spreadsheet.Column previousColumn = null; string cellAddress = Excel.ColumnNameFromIndex(columnIndex) + rowIndex; // Check if the row exists, create if necessary if (sheetData.Elements<DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == rowIndex).Count() != 0) { row = sheetData.Elements<DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == rowIndex).First(); } else { row = new DocumentFormat.OpenXml.Spreadsheet.Row() { RowIndex = rowIndex }; //sheetData.Append(row); for (uint counter = rowIndex - 1; counter > 0; counter--) { previousRow = sheetData.Elements<DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == counter).FirstOrDefault(); if (previousRow != null) { break; } } sheetData.InsertAfter(row, previousRow); } // Check if the cell exists, create if necessary if (row.Elements<DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == cellAddress).Count() > 0) { cell = row.Elements<DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == cellAddress).First(); } else { // Find the previous existing cell in the row for (uint counter = columnIndex - 1; counter > 0; counter--) { previousCell = row.Elements<DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == Excel.ColumnNameFromIndex(counter) + rowIndex).FirstOrDefault(); if (previousCell != null) { break; } } cell = new DocumentFormat.OpenXml.Spreadsheet.Cell() { CellReference = cellAddress }; row.InsertAfter(cell, previousCell); } // Check if the column collection exists columns = worksheet.Elements<DocumentFormat.OpenXml.Spreadsheet.Columns>().FirstOrDefault(); if (columns == null) { columns = worksheet.InsertAt(new DocumentFormat.OpenXml.Spreadsheet.Columns(), 0); } // Check if the column exists if (columns.Elements<DocumentFormat.OpenXml.Spreadsheet.Column>().Where(item => item.Min == columnIndex).Count() == 0) { // Find the previous existing column in the columns for (uint counter = columnIndex - 1; counter > 0; counter--) { previousColumn = columns.Elements<DocumentFormat.OpenXml.Spreadsheet.Column>().Where(item => item.Min == counter).FirstOrDefault(); if (previousColumn != null) { break; } } columns.InsertAfter( new DocumentFormat.OpenXml.Spreadsheet.Column() { Min = columnIndex, Max = columnIndex, CustomWidth = true, Width = 9 }, previousColumn); } // Add the value cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(value); if (styleIndex != null) { cell.StyleIndex = styleIndex; } if (valueType != DocumentFormat.OpenXml.Spreadsheet.CellValues.Date) { cell.DataType = new DocumentFormat.OpenXml.EnumValue<DocumentFormat.OpenXml.Spreadsheet.CellValues>(valueType); } if (save) { worksheet.Save(); } return true; }
// 追加一个 Cell public static DocumentFormat.OpenXml.Spreadsheet.Cell AppendCell( // Worksheet ws, Row row, string addressName) { // SheetData sheetData = ws.GetFirstChild<SheetData>(); DocumentFormat.OpenXml.Spreadsheet.Cell cell = null; cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cell.CellReference = addressName; row.InsertBefore(cell, null); return cell; }
// 11/29/2013 Use dictionary to increase performance. private static DocumentFormat.OpenXml.Spreadsheet.Cell OpenXML_CreateText(int nColumnIndex, int nRowIndex, SharedStringTablePart shareStringPart, string sText, Dictionary<string, int> dictStringToInt) { //int nSharedIndex = OpenXML_InsertSharedStringItem(shareStringPart, sText, dictStringToInt); DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cell.CellReference = OpenXML_GetColumnName(nColumnIndex) + nRowIndex.ToString(); // 12/02/2013 SharedString got very slow after 2000 records. 7000 records was taking an hour to export. // http://social.msdn.microsoft.com/Forums/office/en-US/0dcb58a0-5193-4ab7-b2c6-2742dd9e1be8/openxmlwriter-performance-issue-while-writing-large-excel-file-35000-rows-and-100-cells?forum=oxmlsdk //cell.DataType = CellValues.SharedString; //cell.CellValue = new CellValue(nSharedIndex.ToString()); cell.SetAttribute(new OpenXmlAttribute("", "t", "", "inlineStr")); // 12/02/2013 Try and filter invalid characters. if ( sText.Length > 0 ) { char[] arr = sText.ToCharArray(); for ( int i = 0; i < arr.Length; i++ ) { char ch = arr[i]; // http://social.technet.microsoft.com/Forums/en-US/4a51a8e8-7697-44a2-813f-d3704c8cfc02/hexadecimal-value-is-an-invalid-character-cant-generate-reports?forum=map if ( ch == ControlChars.Cr || ch == ControlChars.Lf || ch == ControlChars.Tab ) { continue; } else if ( ch < ' ' ) { throw(new Exception("Invalid character 0x" + Convert.ToInt32(ch).ToString("x"))); } } } cell.InlineString = new DocumentFormat.OpenXml.Spreadsheet.InlineString { Text = new Text { Text = sText } }; return cell; }
private static void ColumnRow(Cell cell, out int column, out int row) { string columnCode, rowCode; ColumnRow(cell, out columnCode, out rowCode); column = columnCode.Aggregate(0, (sum, c) => sum * ('Z' - 'A' + 1) + (char.ToUpper(c) - 'A') + 1) - 1; row = Convert.ToInt32(rowCode) - 1; }