C# IXLWorksheet.Columns方法代码示例

        public static double getTotalWidth(IXLWorksheet ws, int startCol)
        {
            var totalWidth = 0.0;
            foreach (var col in ws.Columns(startCol, ws.LastColumnUsed().ColumnNumber()))
            {
                totalWidth += col.Width * 5.69;
            }

            return totalWidth;
        }

        private void AddPerformanceFormatting(IXLWorksheet performanceSheet)
        {
            int lastRowUsed = performanceSheet.LastRowUsed().RowNumber();
            //freeze panels
            performanceSheet.SheetView.Freeze(1, 2);
            //performance global styles
            performanceSheet.Range(1, 1, performanceSheet.LastCellUsed().Address.RowNumber, performanceSheet.LastCellUsed().Address.ColumnNumber)
                .Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
            //column specific formatting

            //date columns
            performanceSheet.Range(string.Format("A2:A{0}", lastRowUsed)).Style.DateFormat.Format = "dd/MM/yyyy";

            //percentage formatting (0.00%)
            performanceSheet.Ranges(string.Format("E2:E{0},H2:I{1},Y2:Y{2},AC2:AC{3},AE2:AF{4},AH2:AI{5},AK2:AL{6},AN2:AO{7}",
                lastRowUsed, lastRowUsed, lastRowUsed, lastRowUsed, lastRowUsed, lastRowUsed, lastRowUsed, lastRowUsed
            )).Style.NumberFormat.NumberFormatId = 10;

            //no decimal points
            performanceSheet.Range(string.Format("R2:R{0}", lastRowUsed)).Style.NumberFormat.Format = "0";

            //decimal format (0.00)
            performanceSheet.Ranges(string.Format("J2:J{0},L2:N{1}",
                lastRowUsed, lastRowUsed
            )).Style.NumberFormat.Format = "0.00";

            //three decimal points (0.000)
            performanceSheet.Range(string.Format("U2:U{0}",
                lastRowUsed
            )).Style.NumberFormat.Format = "0.000";

            //money with two decimals ($ 0.00)
            performanceSheet.Ranges(string.Format("T2:T{0},W2:W{1},Z2:Z{2}",
                lastRowUsed, lastRowUsed, lastRowUsed
            )).Style.NumberFormat.Format = "$ 0.00";

            //money with three decimals ($ 0.000)
            performanceSheet.Range(string.Format("V2:V{0}",
                lastRowUsed, lastRowUsed, lastRowUsed
            )).Style.NumberFormat.Format = "$ 0.000";

            // adjust to content
            performanceSheet.Columns().AdjustToContents();
        }

        // parameters:
        //      output_columns    输出列定义。如果为空，表示全部输出。这是一个数字列表，例如 "1,2,3,5"
        // return:
        //      -1  出错
        //      0   放弃或中断
        //      1   成功
        public static int ExportToExcel(
            Stop stop,
            ListView list,
            List<string> output_columns,
            List<ListViewItem> items,
            IXLWorksheet sheet,
            out string strError)
        {
            strError = "";
#if NO
            if (items == null || items.Count == 0)
            {
                strError = "items == null || items.Count == 0";
                return -1;
            }
#endif

            // ListView list = items[0].ListView;
            if (stop != null)
                stop.SetProgressRange(0, items.Count);

            List<int> indices = new List<int>();
            if (output_columns == null && output_columns.Count == 0)
            {
                int i = 0;
                foreach (ColumnHeader header in list.Columns)
                {
                    indices.Add(i++);
                }
            }
            else
            {
                foreach (string s in output_columns)
                {
                    int v = 0;
                    if (Int32.TryParse(s, out v) == false)
                    {
                        strError = "output_columns 数组中有非数字的字符串，格式错误";
                        return -1;
                    }
                    indices.Add(v - 1);   // 从 0 开始计数
                }
            }

            // 每个列的最大字符数
            List<int> column_max_chars = new List<int>();

            List<XLAlignmentHorizontalValues> alignments = new List<XLAlignmentHorizontalValues>();
            //foreach (ColumnHeader header in list.Columns)
            foreach (int index in indices)
            {
                ColumnHeader header = list.Columns[index];

                if (header.TextAlign == HorizontalAlignment.Center)
                    alignments.Add(XLAlignmentHorizontalValues.Center);
                else if (header.TextAlign == HorizontalAlignment.Right)
                    alignments.Add(XLAlignmentHorizontalValues.Right);
                else
                    alignments.Add(XLAlignmentHorizontalValues.Left);

                column_max_chars.Add(0);
            }

            string strFontName = list.Font.FontFamily.Name;

            int nRowIndex = 1;
            int nColIndex = 1;
            // foreach (ColumnHeader header in list.Columns)
            foreach (int index in indices)
            {
                ColumnHeader header = list.Columns[index];

                IXLCell cell = sheet.Cell(nRowIndex, nColIndex).SetValue(header.Text);
                cell.Style.Alignment.WrapText = true;
                cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                cell.Style.Font.Bold = true;
                cell.Style.Font.FontName = strFontName;
                cell.Style.Alignment.Horizontal = alignments[nColIndex - 1];
                nColIndex++;
            }
            nRowIndex++;

            //if (stop != null)
            //    stop.SetMessage("");
            foreach (ListViewItem item in items)
            {
                Application.DoEvents();

                if (stop != null
    && stop.State != 0)
                {
                    strError = "用户中断";
                    return 0;
                }
//.........这里部分代码省略.........

        /// <summary>
        /// Create a worksheet
        /// </summary>
        /// <param name="worksheet">IXLWorksheet</param>
        /// <param name="sheet">CExcelSheet</param>
        private void CreateAWorkSheet(IXLWorksheet worksheet, CExcelSheet sheet)
        {
            //title
            IXLAddress firstAdd = worksheet.Cell(1, COL_BEGIN).Address;
            worksheet.Cell(1, 1).Value = sheet.Title;
            worksheet.Cell(1, 1).Style.Font.Bold = true;
            worksheet.Cell(1, 1).Style.Font.FontSize = 15;
            worksheet.Cell(1, 1).Style.Font.FontColor = XLColor.White;
            worksheet.Cell(1, 1).Style.Fill.BackgroundColor = XLColor.FromArgb(0x0066cc);

            #region Export By

            int rowIdx = ROW_BEGIN;
            if (sheet.ExportBy != null)
            {
                foreach (var item in sheet.ExportBy)
                {
                    // reset column
                    int colIdx = COL_BEGIN;

                    worksheet.Cell(rowIdx, colIdx).DataType = XLCellValues.Text;
                    worksheet.Cell(rowIdx, colIdx).Value = "'" + item.key;
                    worksheet.Range(
                        worksheet.Cell(rowIdx, colIdx).Address,
                        worksheet.Cell(rowIdx, colIdx + 2).Address).Merge();
                    colIdx += 3;
                    worksheet.Cell(rowIdx, colIdx).DataType = XLCellValues.Text;
                    worksheet.Cell(rowIdx, colIdx).Value = "'" + item.value;
                    // new row
                    rowIdx++;
                }
            }

            #endregion

            // Header
            int col = COL_BEGIN;

            #region Header
            //Add No
            if (sheet.IsRenderNo)
            {
                worksheet.Cell(rowIdx, col).Value = "No";
                worksheet.Cell(rowIdx, col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                worksheet.Cell(rowIdx, col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                col++;
            }
            foreach (string header in sheet.Header)
            {
                worksheet.Cell(rowIdx, col).Value = header;
                worksheet.Cell(rowIdx, col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                col++;
            }
            //Style for header
            worksheet.Range(worksheet.Cell(rowIdx, COL_BEGIN).Address, worksheet.Cell(rowIdx, col - 1).Address).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
            worksheet.Range(worksheet.Cell(rowIdx, COL_BEGIN).Address, worksheet.Cell(rowIdx, col - 1).Address).Style.Font.Bold = true;
            worksheet.Range(worksheet.Cell(rowIdx, COL_BEGIN).Address, worksheet.Cell(rowIdx, col - 1).Address).Style.Font.FontSize = 12;
            worksheet.Range(worksheet.Cell(rowIdx, COL_BEGIN).Address, worksheet.Cell(rowIdx, col - 1).Address).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
            //merge title
            IXLAddress secondAdd = worksheet.Cell(1, col - 1).Address;
            worksheet.Range(firstAdd, secondAdd).Merge();
            worksheet.Range(firstAdd, secondAdd).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
            // Merge export by
            for (int i = ROW_BEGIN; i < rowIdx; i++)
            {
                worksheet.Range(
                    worksheet.Cell(i, 4).Address,
                    worksheet.Cell(i, col - 1).Address).Merge();
            }
            #endregion

            // detail
            // Comment when adding export by, rowIdx is the number of export by
            //int idx_row = ROW_BEGIN + 1;
            int idx_row = rowIdx + 1;
            string preGroup = string.Empty;
            ArrayList beginSubList = new ArrayList();
            ArrayList endSubList = new ArrayList();
            //linh.quang.le: Freeze panels
            worksheet.SheetView.FreezeRows(sheet.FreezeRow);
            worksheet.SheetView.FreezeColumns(sheet.FreezeColumn);
            //linh.quang.le number
            int no = 1;

            #region Detail
            foreach (Object row in sheet.List)
            {
                int idx_col = COL_BEGIN;
                int index = 0;
                bool hasMainColumnValue = HasMainColumnValue(row, sheet);
                //linh.quang.le
                #region GroupName
                if (sheet.IsGroup)
                {
                    string groupName = string.Empty;
//.........这里部分代码省略.........