如何使用EPPlus设置XLSX像元宽度？ [英] How to set XLSX cell width with EPPlus?

问题描述

你好，我有这段代码在其中创建一个xlsx文件，我需要预先设置xlsx表格单元格的宽度。
实际的问题是，当我打开excell时，我需要用鼠标双击列之间的间隙以展开列并重新显示隐藏的数据。
是否可以使用Epplus以编程方式执行此操作？

Hello I have this code where i create an xlsx file and i need to pre set the width of the xlsx sheet cells. The actual problem is that when i open the excell i need to double click on the gap between the columns with the mouse in order to unwrap the columns and revieal the data that is hidden. Is there a way to do this programmaticaly with Epplus?

using (ExcelPackage p = new ExcelPackage())
            {
                String filepath = "C://StatsYellowPages.csv";
                DataSet ds = ExportCSVFileToDataset(filepath, "tblCustomers", "\t");
                //Here setting some document properties              
                p.Workbook.Properties.Title = "StatsYellowPages";

                //Create a sheet
                p.Workbook.Worksheets.Add("Sample WorkSheet");
                ExcelWorksheet ws = p.Workbook.Worksheets[1];
                ws.Name = "StatsYellowPages"; //Setting Sheet's name

                //Merging cells and create a center heading for out table
                ws.Cells[1, 1].Value = "StatsYellowPages";
                ws.Cells[1, 1, 1, ds.Tables[0].Columns.Count].Merge = true;
                ws.Cells[1, 1, 1, ds.Tables[0].Columns.Count].Style.Font.Bold = true;
                ws.Cells[1, 1, 1, ds.Tables[0].Columns.Count].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

                int colIndex = 1;
                int rowIndex = 2;

                foreach (DataColumn dc in ds.Tables[0].Columns) //Creating Headings
                {
                    var cell = ws.Cells[rowIndex, colIndex];

                    //Setting the background color of header cells to Gray
                    var fill = cell.Style.Fill;
                    fill.PatternType = ExcelFillStyle.Solid;
                    fill.BackgroundColor.SetColor(Color.Gray);


                    //Setting Top/left,right/bottom borders.
                    var border = cell.Style.Border;
                    border.Bottom.Style = ExcelBorderStyle.Thin;
                    border.Top.Style = ExcelBorderStyle.Thin;
                    border.Left.Style = ExcelBorderStyle.Thin;
                    border.Right.Style = ExcelBorderStyle.Thin;

                    //Setting Heading Value in cell
                    cell.Value = dc.ColumnName;

                    colIndex++;
                }

                foreach (DataRow dr in ds.Tables[0].Rows) // Adding Data into rows
                {
                    colIndex = 1;
                    rowIndex++;
                    foreach (DataColumn dc in ds.Tables[0].Columns)
                    {
                        var cell = ws.Cells[rowIndex, colIndex];
                        //Setting Value in cell
                        cell.Value = dr[dc.ColumnName].ToString();
                        //Setting borders of cell
                        var border = cell.Style.Border;                      
                        colIndex++;
                    }
                }


                //Generate A File with Random name
                Byte[] bin = p.GetAsByteArray();
                string file = "c:\\StatsYellowPages.xlsx";
                File.WriteAllBytes(file, bin);

推荐答案

我发现在填充后设置列宽在工作表上的所有数据中：

I find that setting the column widths after I have filled in all the data on the sheet works:

ws.Column(1).Width = 50;

还有autoFitColumns方法，但是它会忽略带有公式和换行文本的单元格，因此不适用于

There is also the autoFitColumns method but this ignores cells with formulas and wrapped text so it did not work for me.

ws.Cells["A1:K20"].AutoFitColumns();

这篇关于如何使用EPPlus设置XLSX像元宽度？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！