Example #1
2
 public Cell(Worksheet sheet, string cellReference)
 {
     Sheet = sheet;
     Xml = new Spreadsheet.Cell(
         new Spreadsheet.Value { })
     {
         CellReference = new StringValue(cellReference)
     };
 }
Example #2
1
		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;
		}
Example #3
1
        // 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;
        }
Example #4
1
		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);
                }
            }
        }
Example #6
0
        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);
        }
Example #9
0
        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));
        }
Example #12
0
        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));
                }
            }
        }
Example #13
0
        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("&quot;", "\"").Replace("&nbsp;", ""));
                    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;
        }
Example #15
0
        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();
        }
Example #16
0
        //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);
                    }
                }
            }
        }
Example #17
0
        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);
        }
Example #18
0
 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);
 }
Example #19
0
        // 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;
                }
            }
        }
Example #20
0
 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);
 }
Example #21
0
 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
     });
 }
Example #22
0
        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);
            }
        }
Example #23
0
    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];
                }
            }
        }
Example #25
0
        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);
        }
Example #26
0
 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);
 }
Example #27
0
        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);
        }
Example #29
0
        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
Example #31
0
        /// <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);
        }
Example #32
0
        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);
        }
Example #35
0
		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;
		}
Example #36
0
        /// <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;
        }
Example #37
0
        // 追加一个 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;
        }
Example #38
0
		// 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;
		}
Example #39
-1
        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;
        }