C# ExcelWorksheet.Column方法代码示例

        /// <summary>
        /// BuildTeachersTable
        /// </summary>
        /// <param name="ws"></param>
        /// <param name="Teachers"></param>
        private static void BuildTeachersTable(ExcelWorksheet ws, IEnumerable<OfficeVisitsByTeacher> Teachers)
        {

            ws.Column(1).Width = 17.86;
            ws.Column(2).Width = 12.43;

            //Set Header titles
            ws.Cells[4, 1].Value = "Teachers";
            ws.Cells[4, 1].Style.Font.Bold = true;
            ws.Cells[5, 1].Value = "Teacher Name";
            ws.Cells[5, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin);
           // ws.Cells[5, 1].AutoFilter = true;
            ws.Cells[5, 2].Value = "Office Visits";
            ws.Cells[5, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin);
           // ws.Cells[5, 2].AutoFilter = true;

            //Get Data for Teachers       
            for (int i = 0; i < Teachers.Count(); i++)
            {
                ws.Cells[i + 6, 1].Value = Teachers.ElementAt(i).sent_by_contact_name;
                ws.Cells[i + 6, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin);
                ws.Cells[i + 6, 2].Value = Teachers.ElementAt(i).total_visits;
                ws.Cells[i + 6, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin);
            }

            //Set Header style
            using (ExcelRange rng = ws.Cells[4, 1, 5 + Teachers.Count(), 2])
            {
                rng.Style.Border.BorderAround(ExcelBorderStyle.Medium);            
            }

        }

 private void SetColumns(ExcelWorksheet worksheet)
 {
     worksheet.Column(1).Width = 20;
     worksheet.Column(2).Width = 20;
     worksheet.Column(3).Width = 20;
     worksheet.Column(4).Width = 20;
     worksheet.Column(5).Width = 20;
 }

        private static EpplusResult FormatResult(ExcelWorksheet ws, int count, PropertyInfo[] cols, ExcelPackage ep)
        {
            var range = ws.Cells[1, 1, count + 1, cols.Length];
            var table = ws.Tables.Add(range, "Members");
            table.ShowFilter = false;
            table.TableStyle = TableStyles.Light9;
            int userdatacol = 1;
            int groupcol = 1;
            int questionscol = 1;
            for (var i = 0; i < cols.Length; i++)
            {
                var col = i + 1;
                var name = cols[i].Name;
                table.Columns[i].Name = name;
                var colrange = ws.Cells[1, col, count + 2, col];

                if (name.Contains("Date") || name == "LastAttend")
                {
                    colrange.Style.Numberformat.Format = "mm-dd-yy";
                    colrange.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                    ws.Column(col).Width = 12;
                }
                switch (name)
                {
                    case "UserData":
                        colrange.Style.WrapText = true;
                        userdatacol = col;
                        break;
                    case "Groups":
                        colrange.Style.WrapText = true;
                        groupcol = col;
                        break;
                    case "Questions":
                        colrange.Style.WrapText = true;
                        questionscol = col;
                        break;
                }
            }
            ws.Cells[ws.Dimension.Address].AutoFitColumns();
            if(userdatacol > 1)
                ws.Column(userdatacol).Width = 40.0;
            if(groupcol > 1)
                ws.Column(groupcol).Width = 60.0;
            if(questionscol > 1)
                ws.Column(questionscol).Width = 40.0;
            return new EpplusResult(ep, "OrgMember.xlsx");
        }

		private static void AdjustExcel(int count, ExcelWorksheet excel, int iRow)
		{
			for(var j = 1; j <= count; j++)
			{
				excel.Column(j).AutoFit();
				for(var i = 1; i < iRow; i++)
				{
					excel.Cells[i, j].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Gray);
				}
			}
		}

 private int SetColumnWidth(ExcelWorksheet reportSheet, int columnId, double width)
 {
     reportSheet.Column(columnId).Width = ExcelExt.GetTrueColumnWidth(width);
     return columnId + 1;
 }

    private ExcelWorksheet CreateListView_XML(ExcelWorksheet worksheet,
        int row, string fieldformat, System.Drawing.Color colFromHex, int i, ExcelConfiguration conf)
    {
        worksheet.Column(1).Width = 15;
        worksheet.Column(2).Width = 15;

        worksheet.Cells["A1"].Value = "TableName";
        worksheet.Cells["B1"].Value = conf.ViewList[i].TableName;
        worksheet.Cells["A2"].Value = "ViewType";
        worksheet.Cells["B2"].Value = "ListView";
        worksheet.Cells["A3"].Value = "ViewName";
        worksheet.Cells["B3"].Value = conf.ViewList[i].ViewName;
        worksheet.Cells["A4"].Value = "Title";
        worksheet.Cells["B4"].Value = conf.ViewList[i].ViewTitle;
        worksheet.Cells["A5"].Value = "Root Service URL";
        worksheet.Cells["B5"].Value = conf.ViewList[i].RootServiceUrl;
        worksheet.Cells["A6"].Value = "Order By";
        worksheet.Cells["B6"].Value = conf.ViewList[i].OrderBy;

        worksheet.Cells["A7"].Value = "Filter";
        worksheet.Cells["B7"].Value = conf.ViewList[i].IsHasFilter;
        worksheet.Cells["A8"].Value = "PageSize";
        worksheet.Cells["B8"].Value = conf.ViewList[i].PageSize;
        //
        worksheet.Cells["A9"].Value = "EditView";
        worksheet.Cells["A10"].Value = "FilterView";
        worksheet.Cells["A11"].Value = "InputParameters";
        worksheet.Cells["A12"].Value = "FilterParameters";
        worksheet.Cells["A13"].Value = "MasterPage";

        worksheet.Cells["B9"].Value = conf.ViewList[i].EditView;
        worksheet.Cells["B10"].Value = conf.ViewList[i].FilterView;
        worksheet.Cells["B11"].Value = conf.ViewList[i].InputParameters;
        worksheet.Cells["B12"].Value = conf.ViewList[i].FilterParameters;
        worksheet.Cells["B13"].Value = conf.ViewList[i].MasterPage;
        //

        worksheet.Cells["A14"].Value = "ButtonNew";
        worksheet.Cells["B14"].Value = conf.ViewList[i].IsHasNewButton;
        worksheet.Cells["C14"].Value = "Caption";
        worksheet.Cells["D14"].Value = "เพิ่ม";
        worksheet.Cells["E14"].Value = "AppearanceRules";
        worksheet.Cells["F14"].Value = "TRUE";
        worksheet.Cells["A15"].Value = "ButtonSave";
        worksheet.Cells["B15"].Value = conf.ViewList[i].IsHasSaveButton;
        worksheet.Cells["C15"].Value = "Caption";
        worksheet.Cells["D15"].Value = "บันทึก";
        worksheet.Cells["E15"].Value = "AppearanceRules";
        worksheet.Cells["F15"].Value = "FALSE";
        worksheet.Cells["A16"].Value = "ButtonDelete";
        worksheet.Cells["B16"].Value = conf.ViewList[i].IsHasDeleteButton;
        worksheet.Cells["C16"].Value = "Caption";
        worksheet.Cells["D16"].Value = "ลบ";
        worksheet.Cells["E16"].Value = "AppearanceRules";
        worksheet.Cells["F16"].Value = "TRUE";
        //
        worksheet.Cells["A17"].Value = "SqlSelect";
        worksheet.Cells["A18"].Value = conf.ViewList[i].SQLSelect;
        worksheet.Cells["A19"].Value = "SqlUpdate";
        worksheet.Cells["A20"].Value = conf.ViewList[i].SQLUpdate;
        worksheet.Cells["F17"].Value = "SqlSelectParameter";
        worksheet.Cells["F18"].Value = conf.ViewList[i].SQLInsertParams;
        worksheet.Cells["F19"].Value = "SqlUpdateParameter";
        worksheet.Cells["F20"].Value = conf.ViewList[i].SQLUpdateParams;
        //

        ////Add the headers
        worksheet.Cells["A21"].Value = "Caption";
        worksheet.Cells["B21"].Value = "Column";
        worksheet.Cells["C21"].Value = "Format";
        worksheet.Cells["D21"].Value = "TextAlignment";
        worksheet.Cells["E21"].Value = "IsHideResize";
        worksheet.Cells["F21"].Value = "CustomStyle";

        colFromHex = System.Drawing.ColorTranslator.FromHtml("#FFFF00");
        worksheet.Cells["A21:F21"].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
        worksheet.Cells["A21:F21"].Style.Fill.BackgroundColor.SetColor(colFromHex);

        row = 22;
        for(int j=0;j<conf.ViewList[i].Items.Count;j++)
        {
        //foreach (DataRow dr_columns in ds_columns.Tables["columns"].Rows)
        //{

            worksheet.Cells["A" + row.ToString()].Value = conf.ViewList[i].Items[j].Caption;
            worksheet.Cells["B" + row.ToString()].Value = conf.ViewList[i].Items[j].DataBinding;
            worksheet.Cells["C" + row.ToString()].Value = conf.ViewList[i].Items[j].Formats;
            //worksheet.Cells["D" + row.ToString()].Value = conf.ViewList[i].Items[j].Formats;
            //worksheet.Cells["E" + row.ToString()].Value = conf.ViewList[i].Items[j].Formats;
            //worksheet.Cells["F" + row.ToString()].Value = conf.ViewList[i].Items[j].Formats;
            row++;
        }

        row--;
        worksheet.Cells["A1:F" + row.ToString()].Style.Border.Top.Style = ExcelBorderStyle.Thin;
        worksheet.Cells["A1:F" + row.ToString()].Style.Border.Right.Style = ExcelBorderStyle.Thin;
        worksheet.Cells["A1:F" + row.ToString()].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
        worksheet.Cells["A1:F" + row.ToString()].Style.Border.Left.Style = ExcelBorderStyle.Thin;
        worksheet.Cells["A1:F" + row.ToString()].Style.Font.Name = "Tahoma";
        worksheet.Cells["A1:F" + row.ToString()].Style.Font.Size = 10;
//.........这里部分代码省略.........

        private void CreateHeaderPassport(ExcelWorksheet ws, List<TechOperation> techOperations)
        {
            CreateHeaderPassport(ws);

            for (var i = 0; i < techOperations.Count; i++)
            {
                var colNumInt = CountFixedField + i;
                FillHeaderSquare(ws.Cells[3, colNumInt], techOperations[i].ShortName);

                ws.Column(colNumInt).Width = 3;
            }

            ws.Cells["A2:Q2"].Merge = false;

            var cell = ws.Cells[2, 1, 2, CountFixedField + techOperations.Count - 1];
            cell.Merge = true;
            cell.Value = "";
            SetCenterOrientation(cell);
            SetStyleToAllLinesSquare(cell, ExcelBorderStyle.Thick);
        }

        private void CreateHeaderBigNorms(ExcelWorksheet workSheet)
        {
            var cell = workSheet.Cells["A6:N6"];
            cell.Merge = true;
            cell.Value = "";
            cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

            SetStyleToAllLinesSquare(cell, ExcelBorderStyle.Thick);

            cell = workSheet.Cells["L1:N4"];
            cell.Merge = true;
            cell.Value = "<<Утверждаю>>\nИсполнительный директор\nООО \"ДетальКомплект\"\n______________ Воловиков В. А.";
            cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            cell.Style.WrapText = true;

            FillHeaderSquare(workSheet.Cells["A7"], "№ п/п");
            workSheet.Column(1).Width = 8;

            FillHeaderSquare(workSheet.Cells["B7"], "Профиль");
            workSheet.Column(2).Width = 11;

            FillHeaderSquare(workSheet.Cells["C7"], "Типоразмер");
            workSheet.Column(3).Width = 15;

            FillHeaderSquare(workSheet.Cells["D7"], "Марка стали");
            workSheet.Column(4).Width = 25;

            FillHeaderSquare(workSheet.Cells["E7"], "ГОСТ на сортамент");
            workSheet.Column(5).Width = 25;

            FillHeaderSquare(workSheet.Cells["F7"], "Коэфф. использ.");
            workSheet.Column(6).Width = 8;

            FillHeaderSquare(workSheet.Cells["G7"], "Вес");
            workSheet.Column(7).Width = 16;

            FillHeaderSquare(workSheet.Cells["H7"], "Вес с отходом");
            workSheet.Column(8).Width = 16;

            FillHeaderSquare(workSheet.Cells["I7"], "Доп. ед. изм.");
            workSheet.Column(9).Width = 14;

            FillHeaderSquare(workSheet.Cells["J7"], "Доп. ед. изм. с отходом");
            workSheet.Column(10).Width = 14;

            FillHeaderSquare(workSheet.Cells["K7"], "Тип доп. ед. изм.");
            workSheet.Column(11).Width = 14;

            FillHeaderSquare(workSheet.Cells["L7"], "Примечание");
            workSheet.Column(12).Width = 20;

            FillHeaderSquare(workSheet.Cells["M7"], "Менеджер (сдал)");
            workSheet.Column(13).Width = 10;

            FillHeaderSquare(workSheet.Cells["N7"], "Мастер (принял)");
            workSheet.Column(14).Width = 10;
        }

        private void CreateHeaderLZK(ExcelWorksheet workSheet)
        {
            workSheet.PrinterSettings.Orientation = eOrientation.Landscape;
            workSheet.PrinterSettings.PaperSize = ePaperSize.A4;

            workSheet.Cells["B2"].Value = "Заказ ";
            workSheet.Cells["B2"].Style.Font.Size = 12;
            workSheet.Cells["B2"].Style.Font.Bold = true;

            workSheet.Cells["B3"].Value = "ЛЗК ";


            FillHeaderSquare(workSheet.Cells["A7"], "№ п/п");
            workSheet.Column(1).Width = 4;
            FillHeaderSquare(workSheet.Cells["B7"], "Профиль");
            workSheet.Column(2).Width = 18;
            FillHeaderSquare(workSheet.Cells["C7"], "Типоразмер");
            workSheet.Column(3).Width = 11;
            FillHeaderSquare(workSheet.Cells["D7"], "Марка стали");
            workSheet.Column(4).Width = 18;
            FillHeaderSquare(workSheet.Cells["E7"], "ГОСТ на сортамент");
            workSheet.Column(5).Width = 15;
            FillHeaderSquare(workSheet.Cells["F7"], "Ед. изм.");
            workSheet.Column(6).Width = 29;
            FillHeaderSquare(workSheet.Cells["G7"], "Номер плавки");
            FillHeaderSquare(workSheet.Cells["H7"], "Подп. клад.");
            FillHeaderSquare(workSheet.Cells["I7"], "Подп. маст.");
        }

 /// <summary>
 /// Sets the column widths to a preset size.
 /// </summary>
 /// <param name="worksheet">The worksheet.</param>
 private void SetColumnWidths(ExcelWorksheet worksheet)
 {
     worksheet.Column(1).Width = ExcelHelper.Pixel2ColumnWidth(worksheet, 87);
     worksheet.Column(2).Width = ExcelHelper.Pixel2ColumnWidth(worksheet, 93);
     worksheet.Column(3).Width = ExcelHelper.Pixel2ColumnWidth(worksheet, 40);
     worksheet.Column(4).Width = ExcelHelper.Pixel2ColumnWidth(worksheet, 40);
     worksheet.Column(5).Width = ExcelHelper.Pixel2ColumnWidth(worksheet, 40);
     worksheet.Column(6).Width = ExcelHelper.Pixel2ColumnWidth(worksheet, 40);
     worksheet.Column(7).Width = ExcelHelper.Pixel2ColumnWidth(worksheet, 55);
     worksheet.Column(8).Width = ExcelHelper.Pixel2ColumnWidth(worksheet, 54);
     worksheet.Column(9).Width = ExcelHelper.Pixel2ColumnWidth(worksheet, 61);
     worksheet.Column(10).Width = ExcelHelper.Pixel2ColumnWidth(worksheet, 72);
 }

        /// <summary>
        /// BuildHomeroomsTable
        /// </summary>
        /// <param name="ws"></param>
        /// <param name="Homerooms"></param>
        private static void BuildHomeroomsTable(ExcelWorksheet ws, IEnumerable<OfficeVisitsByHomeroom> Homerooms)
        {


            ws.Column(5).Width = 12.86;
            ws.Column(6).Width = 16;
            ws.Column(7).Width = 6.71;
            ws.Column(8).Width = 11.71;

            ws.Cells[4, 5].Value = "Homerooms";
            ws.Cells[4, 5].Style.Font.Bold = true;
            ws.Cells[5, 5].Value = "School";
            ws.Cells[5, 5].Style.Border.BorderAround(ExcelBorderStyle.Thin);
            ws.Cells[5, 6].Value = "Homeroom";
            ws.Cells[5, 6].Style.Border.BorderAround(ExcelBorderStyle.Thin);
            ws.Cells[5, 7].Value = "Grade";
            ws.Cells[5, 7].Style.Border.BorderAround(ExcelBorderStyle.Thin);
            ws.Cells[5, 8].Value = "Office Visits";
            ws.Cells[5, 8].Style.Border.BorderAround(ExcelBorderStyle.Thin);


            for (int i = 0; i < Homerooms.Count(); i++)
            {
                ws.Cells[i + 6, 5].Value = Homerooms.ElementAt(i).school_name;
                ws.Cells[i + 6, 5].Style.Border.BorderAround(ExcelBorderStyle.Thin);

                ws.Cells[i + 6, 6].Value = Homerooms.ElementAt(i).homeroom_name;
                ws.Cells[i + 6, 6].Style.Border.BorderAround(ExcelBorderStyle.Thin);

                ws.Cells[i + 6, 7].Value = Homerooms.ElementAt(i).grade.ToString();
                ws.Cells[i + 6, 7].Style.Border.BorderAround(ExcelBorderStyle.Thin);

                ws.Cells[i + 6, 8].Value = Homerooms.ElementAt(i).total_visits;
                ws.Cells[i + 6, 8].Style.Border.BorderAround(ExcelBorderStyle.Thin);

            }

            //Set Header style
            using (ExcelRange rng = ws.Cells[4, 5, 5 + Homerooms.Count(), 8])
            {
                rng.Style.Border.BorderAround(ExcelBorderStyle.Medium);
            }
           

        }

        /// <summary>
        /// BuildViolationsTable
        /// </summary>
        /// <param name="ws"></param>
        /// <param name="OffenseTypes"></param>
        private static void BuildViolationsTable(ExcelWorksheet ws, IEnumerable<OfficeVisitsByOffenseType> OffenseTypes)
        {

            ws.Column(11).Width = 50;
            ws.Column(11).Style.WrapText = true;
            ws.Column(12).Width = 12.14;


            ws.Cells[4, 11].Value = "Violations";
            ws.Cells[4, 11].Style.Font.Bold = true;
            ws.Cells[5, 11].Value = "Offense Type";
            ws.Cells[5, 11].Style.Border.BorderAround(ExcelBorderStyle.Thin);
            ws.Cells[5, 12].Value = "Office Visits";
            ws.Cells[5, 12].Style.Border.BorderAround(ExcelBorderStyle.Thin);

            for (int i = 0; i < OffenseTypes.Count(); i++)
            {
                ws.Cells[i + 6, 11].Value = OffenseTypes.ElementAt(i).offense_type;
                ws.Cells[i + 6, 11].Style.Border.BorderAround(ExcelBorderStyle.Thin);
                ws.Cells[i + 6, 12].Value = OffenseTypes.ElementAt(i).total_visits;
                ws.Cells[i + 6, 12].Style.Border.BorderAround(ExcelBorderStyle.Thin);
            }

            //Set Header style
            using (ExcelRange rng = ws.Cells[4, 11, 5 + OffenseTypes.Count(), 12])
            {
                rng.Style.Border.BorderAround(ExcelBorderStyle.Medium);
            }
        }

        void SetColumnWidth(ExcelWorksheet ws, int columnLength)
        {
            ws.Column(1).Width = 20.0;

            for (int i = 2; i <= columnLength; i++) {
                ws.Column(i).Width = 11.0;
            }
        }

        public static void ExportGridToExcel(ExcelWorksheet ws, DataGridView grid)
        {
            for (int i = 0; i < grid.Columns.Count; i++)
            {
                // header
                var hCell = ws.Cells[1, i + 1];
                hCell.Value = grid.Columns[i].HeaderText;
                hCell.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                hCell.Style.Fill.BackgroundColor.SetColor(Color.Wheat);
                hCell.Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);

                for (int j = 0; j < grid.Rows.Count; j++)
                {
                    ws.Cells[j + 2, i + 1].Value = (grid[i, j].Value ?? string.Empty);
                }

                ws.Column(i + 1).AutoFit();
            }
        }

        private void InsertFullTable(ExcelWorksheet ws, int headerRow, int column, DataTable table, string format)
        {
            ws.Cells[headerRow, column, headerRow + table.Rows.Count - 1, column + table.Columns.Count - 1].LoadFromDataTable(table, true);

            if (string.IsNullOrEmpty(format))
            {
                //This is a bit hacky, but what can you do...
                //we have to guess the format of the columns because we can't provide them directly in the template
                GuessColumnFormats(ws, headerRow, column, table);
            }
            else
            {
                ws.Cells[headerRow + 1, column, headerRow + 1 + table.Rows.Count, column + table.Columns.Count].Style.Numberformat.Format = format;
            }

            for (int i = 0; i < table.Columns.Count; i++)
            {
                if (column + i == 1) continue; //We don't want to autosize the first column
                ws.Column(column + i).AutoFit();
            }
        }