C# Row.InsertAt方法代码示例

        public static void CreateCell(Row row, string text, uint columnIndex)
        {
            Cell newCell = new Cell();

            newCell.CellValue = new CellValue(text);

            try
            {
                int value = Convert.ToInt32(text);
                newCell.DataType = new EnumValue<CellValues>(CellValues.Number);
            }
            catch (FormatException e)
            {
                newCell.DataType = new EnumValue<CellValues>(CellValues.String);
            }

            row.InsertAt(newCell, (int)columnIndex);
        }

        protected void ExcelDl_Click(object sender, EventArgs e)
        {
            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", "attachment;filename=ViewHistoryChart.xlsx");
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.Charset = "";
            StringWriter sw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(sw);
            Chart1.RenderControl(hw);

            using (MemoryStream stream = new MemoryStream())
            {
                using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
                {
                    var fileName = HttpContext.Current.Request.PhysicalApplicationPath + @"\Charts\ChartImage.png";

                    WorkbookPart wbp = spreadsheetDocument.AddWorkbookPart();
                    WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
                    Workbook wb = new Workbook();
                    FileVersion fv = new FileVersion();
                    fv.ApplicationName = "Microsoft Office Excel";
                    Worksheet ws = new Worksheet();
                    SheetData sd = new SheetData();

                    // add contents
                    List<string> values = new List<string>();

                    //values.Add(lblSectionFunctionSelected.Text);

                    uint i = 1;
                    foreach (var value in values)
                    {
                        UInt32Value rowIndex = UInt32Value.FromUInt32(i);
                        var row = new Row { RowIndex = rowIndex }; // add a row at the top of spreadsheet
                        sd.Append(row);

                        var cell = new Cell
                        {
                            CellValue = new CellValue(value),
                            DataType = new EnumValue<CellValues>(CellValues.String)
                        };
                        row.InsertAt(cell, 0);
                        i++;
                    }
                    // add image
                    DrawingsPart dp = wsp.AddNewPart<DrawingsPart>();
                    ImagePart imgp = dp.AddImagePart(ImagePartType.Png, wsp.GetIdOfPart(dp));
                    using (FileStream fs = new FileStream(fileName, FileMode.Open))
                    {
                        imgp.FeedData(fs);
                    }
                    DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties nvdp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties();
                    nvdp.Id = 1025;
                    nvdp.Name = "Picture 1";
                    nvdp.Description = "Chart";
                    DocumentFormat.OpenXml.Drawing.PictureLocks picLocks = new DocumentFormat.OpenXml.Drawing.PictureLocks();
                    picLocks.NoChangeAspect = true;
                    picLocks.NoChangeArrowheads = true;
                    DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureDrawingProperties nvpdp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureDrawingProperties();
                    nvpdp.PictureLocks = picLocks;
                    DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureProperties nvpp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureProperties();
                    nvpp.NonVisualDrawingProperties = nvdp;
                    nvpp.NonVisualPictureDrawingProperties = nvpdp;

                    DocumentFormat.OpenXml.Drawing.Stretch stretch = new DocumentFormat.OpenXml.Drawing.Stretch();
                    stretch.FillRectangle = new DocumentFormat.OpenXml.Drawing.FillRectangle();

                    DocumentFormat.OpenXml.Drawing.Spreadsheet.BlipFill blipFill = new DocumentFormat.OpenXml.Drawing.Spreadsheet.BlipFill();
                    DocumentFormat.OpenXml.Drawing.Blip blip = new DocumentFormat.OpenXml.Drawing.Blip();
                    blip.Embed = dp.GetIdOfPart(imgp);
                    blip.CompressionState = DocumentFormat.OpenXml.Drawing.BlipCompressionValues.Print;
                    blipFill.Blip = blip;
                    blipFill.SourceRectangle = new DocumentFormat.OpenXml.Drawing.SourceRectangle();
                    blipFill.Append(stretch);

                    DocumentFormat.OpenXml.Drawing.Transform2D t2d = new DocumentFormat.OpenXml.Drawing.Transform2D();
                    DocumentFormat.OpenXml.Drawing.Offset offset = new DocumentFormat.OpenXml.Drawing.Offset();
                    offset.X = 0;
                    offset.Y = 0;
                    t2d.Offset = offset;
                    Bitmap bm = new Bitmap(fileName);
                    //http://en.wikipedia.org/wiki/English_Metric_Unit#DrawingML
                    //http://stackoverflow.com/questions/1341930/pixel-to-centimeter
                    //http://stackoverflow.com/questions/139655/how-to-convert-pixels-to-points-px-to-pt-in-net-c
                    DocumentFormat.OpenXml.Drawing.Extents extents = new DocumentFormat.OpenXml.Drawing.Extents();
                    extents.Cx = (long)bm.Width * (long)((float)914400 / bm.HorizontalResolution);
                    extents.Cy = (long)bm.Height * (long)((float)914400 / bm.VerticalResolution);
                    bm.Dispose();
                    t2d.Extents = extents;
                    DocumentFormat.OpenXml.Drawing.Spreadsheet.ShapeProperties sp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.ShapeProperties();
                    sp.BlackWhiteMode = DocumentFormat.OpenXml.Drawing.BlackWhiteModeValues.Auto;
                    sp.Transform2D = t2d;
                    DocumentFormat.OpenXml.Drawing.PresetGeometry prstGeom = new DocumentFormat.OpenXml.Drawing.PresetGeometry();
                    prstGeom.Preset = DocumentFormat.OpenXml.Drawing.ShapeTypeValues.Rectangle;
                    prstGeom.AdjustValueList = new DocumentFormat.OpenXml.Drawing.AdjustValueList();
                    sp.Append(prstGeom);
                    sp.Append(new DocumentFormat.OpenXml.Drawing.NoFill());

                    DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture picture = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture();
//.........这里部分代码省略.........

        /// <summary>
        /// Writes an <see cref="ITable"/> instance as an <see cref="ExcelFile"/> to the specified stream.
        /// </summary>
        /// <param name="table"></param>
        /// <param name="file"></param>
        public static void Write(ITable table, SpreadsheetDocument document, TimeZoneInfo timezone = null)
        {
            var worksheetPart = document.WorkbookPart.AddNewPart<WorksheetPart>();
            worksheetPart.Worksheet = new Worksheet(new SheetData());
            Sheet sheet = new Sheet()
            {
                Id = document.WorkbookPart.GetIdOfPart(worksheetPart),
                SheetId = (uint)document.WorkbookPart.Workbook.Sheets.Count() + 1,
                Name = table.Name
            };
            document.WorkbookPart.Workbook.Sheets.Append(sheet);

            // Get the sheetData cell table.
            var sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

            // Add a row to the cell table.
            Row row;
            row = new Row() { RowIndex = 1 };
            sheetData.Append(row);

            // Add cells for each column header and create styles for each column
            var index = 0;
            var formats = new List<ColumnFormat>();
            foreach (var column in table.Columns)
            {
                // Add the cell to the cell table at A1.
                var cell = new Cell()
                {
                    CellReference = GetCellReference((uint)index + 1, 1),
                    CellValue = new CellValue(column.Name),
                    DataType = new EnumValue<CellValues>(CellValues.String)
                };
                row.InsertAt(cell, index++);
                formats.Add(new ColumnFormat(column, document, timezone));
            }

            // Add rows for each table entity
            uint rowIndex = 2;
            foreach (var tableRow in table.Rows)
            {
                row = new Row() { RowIndex = rowIndex };
                sheetData.Append(row);
                var columnIndex = 0;
                foreach (var value in tableRow)
                {
                    var format = formats[columnIndex];

                    // Add the cell to the cell table at A1.
                    var cell = new Cell()
                    {
                        CellReference = GetCellReference((uint)columnIndex + 1, rowIndex),
                        CellValue = new CellValue(format.GetCellValue(value)),
                        DataType = format.DataType,
                        StyleIndex = format.Style
                    };
                    row.InsertAt(cell, columnIndex);
                    columnIndex++;
                }
                rowIndex++;
            }

            // Recursively write child tables
            foreach (var child in table.Children)
                Write(child, document);
        }