none
openxml2.0中的 BestFit 如何使用 RRS feed

  • 常规讨论

  • http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.column_properties(office.14).aspx中介绍了可以调整column宽度的 BestFit 属性,但是我不知道如何定义宽度。

    代码如下:其中CreateMockDataSet()为自定义的一个DateSet,返回了三列值  我不知道如何定位一个列,并设置其宽度

    要想运行如下代码,请安装openxml2.0 sdk并引用

    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;

    Code Snippet
    protected void Button1_Click(object sender, EventArgs e)
            {
                File.Copy(Server.MapPath(@"sampledoc/dhy.xlsx"), Server.MapPath(@"sampledoc/dhy1.xlsx"), true);
                // Open the document for editing.
                using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(Server.MapPath(@"sampledoc/dhy1.xlsx"), true))
                {
                    // Get the SharedStringTablePart. If it does not exist, create a new one.
                    SharedStringTablePart shareStringPart;
                    if (spreadSheet.WorkbookPart.GetPartsOfType().Count() > 0)
                    {
                        shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType().First();
                    }
                    else
                    {
                        shareStringPart = spreadSheet.WorkbookPart.AddNewPart();
                    }
                    // Insert a new worksheet.
                    //WorksheetPart worksheetPart = InsertWorksheet(spreadSheet.WorkbookPart);
                    WorksheetPart worksheetPart = spreadSheet.WorkbookPart.GetPartsOfType().First();
                  
                    // Insert the text into the SharedStringTablePart.
                    for (int i = 0; i < CreateMockDataSet().Tables[0].Rows.Count; i++)
                    {
                        for (int j = 0; j < CreateMockDataSet().Tables[0].Columns.Count; j++)
                        {
                            int index = InsertSharedStringItem(CreateMockDataSet().Tables[0].Rows[i][j].ToString(), shareStringPart);
                           
                            // Insert cell A1 into the new worksheet.
                            Cell cell;
                            switch (j)
                            {
                                case 0:
                                    cell = InsertCellInWorksheet("A", (uint)i+1, worksheetPart);
                                    cell.CellValue = new CellValue(index.ToString());
                                    cell.DataType = new EnumValue(CellValues.SharedString);
                                    break;
                                case 1:
                                    cell = InsertCellInWorksheet("B", (uint)i+1, worksheetPart);
                                    cell.CellValue = new CellValue(index.ToString());
                                    cell.DataType = new EnumValue(CellValues.SharedString);
                                    break;
                                case 2:
                                    cell = InsertCellInWorksheet("C", (uint)i+1, worksheetPart);
                                    cell.CellValue = new CellValue(index.ToString());
                                    cell.DataType = new EnumValue(CellValues.SharedString);
                                    break;
                            }
                        }
                    }
                  
                    worksheetPart.Worksheet.Save();
                }
                Response.Clear();
                Response.ClearHeaders();
                Response.Buffer = false;
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.document";
                Response.AddHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(@"aa.xlsx", System.Text.Encoding.UTF8));
                Response.Charset = "GB2312";
                Response.ContentEncoding = System.Text.Encoding.UTF8;
                Response.WriteFile(Server.MapPath(@"sampledoc/dhy1.xlsx"));
                Response.Flush();
                Response.End();
            }

     

     

    Code Snippet

    private static DataSet CreateMockDataSet()
            {
                DataTable table 
    = new DataTable("sample");
                DataColumn column;
                DataRow row;
                
    // Create the first column and add to the DataTable.
                column = new DataColumn();
                column.DataType 
    = System.Type.GetType("System.Int32");
                column.ColumnName 
    = "DocID";
                column.AutoIncrement 
    = true;
                column.Caption 
    = "ID";
                
    // Add the column to the DataColumnCollection.
                table.Columns.Add(column);
                
    // Create the second column and add it to the column set.
                column = new DataColumn();
                column.DataType 
    = System.Type.GetType("System.String");
                column.ColumnName 
    = "DocName";
                column.AutoIncrement 
    = false;
                column.Caption 
    = "DocName";
                table.Columns.Add(column);
                
    // Create the third column and add it to the column set.
                column = new DataColumn();
                column.DataType 
    = System.Type.GetType("System.String");
                column.ColumnName 
    = "ChapterTitle";
                column.AutoIncrement 
    = false;
                column.Caption 
    = "ChapterTitle";
                table.Columns.Add(column);
                
    // Create five DataRow objects, and add them to DataTable.
                for (int i = 0; i <= 4; i++)
                {
                    row 
    = table.NewRow();
                    row[
    "DocID"= i;
                    row[
    "DocName"= "Doc " + i;
                    row[
    "ChapterTitle"= "Chapter Title " + i;
                    table.Rows.Add(row);
                }
                
    // Create a data set from the table and return it.
                DataSet ds = new DataSet();
                ds.Tables.Add(table);
                
    return ds;
            }

     

     

    2009年1月10日 6:25

全部回复

  • Code Snippet

    设置宽度你可以

    cell.SetAttribute("Width",300);

    或者遍历一下 Column,设置

     

    worksheet.Column[i].Width = 300;

    worksheet.Column[i].BestFit = true;

     

     

     

    2009年1月10日 7:39
    版主
  • 不行,孟老师,我在如下位置加上会报错的

    Code Snippet

                             case 0:
                                    cell = InsertCellInWorksheet("A", (uint)i+1, worksheetPart);
                                    cell.CellValue = new CellValue(index.ToString());
                                    cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
                                    cell.SetAttribute("Width", 300);
                                    break;

     

    错误 1 “SetAttribute”方法没有采用“2”个参数的重载 

     

    而您说的第二种方法会报没有找到worksheet的错误。我将worksheet换成worksheetpart也不行

     

    在您方便的时候可以装一个openxml2.0试试。虽然生成的是 .xlsx ,但是不影响下载,因为.xlsx是基于xml和zip的

    openxml sdk 2.0下载地址:http://www.microsoft.com/downloads/details.aspx?FamilyID=c6e744e5-36e9-45f5-8d8c-331df206e0d0&DisplayLang=en

    2009年1月10日 9:18