Example #1
0
        static void ExportToExcel(object sender, ExportExcelEventArgs e)
        {
            var workBook = new HSSFWorkbook();
            var sheet    = workBook.CreateSheet("exported-data");

            if (e.Cannelled)
            {
                return;
            }

            var titleStyle1 = CreateTitleStyle(workBook);

            var dataStyle  = CreateDataStyle(workBook);
            var dataStyle1 = workBook.CreateCellStyle(); // 文本。

            dataStyle1.CloneStyleFrom(dataStyle);

            var dataStyle2 = workBook.CreateCellStyle(); // 整数。

            dataStyle2.CloneStyleFrom(dataStyle);
            dataStyle2.DataFormat = workBook.CreateDataFormat().GetFormat("0");
            dataStyle2.Alignment  = HorizontalAlignment.Right;

            var dataStyle3 = workBook.CreateCellStyle(); // 整数金额。

            dataStyle3.CloneStyleFrom(dataStyle);
            dataStyle3.DataFormat = workBook.CreateDataFormat().GetFormat("#,##0");
            dataStyle3.Alignment  = HorizontalAlignment.Right;

            var dataStyle4 = workBook.CreateCellStyle(); // 金额。

            dataStyle4.CloneStyleFrom(dataStyle);
            dataStyle4.DataFormat = workBook.CreateDataFormat().GetFormat("#,##0.00");
            dataStyle4.Alignment  = HorizontalAlignment.Right;

            var dataStyle5 = workBook.CreateCellStyle(); // 日期。

            dataStyle5.CloneStyleFrom(dataStyle);
            dataStyle5.DataFormat = workBook.CreateDataFormat().GetFormat("yyyy/MM/dd");

            var dataStyle6 = workBook.CreateCellStyle(); // 日期时间。

            dataStyle6.CloneStyleFrom(dataStyle);
            dataStyle6.DataFormat = workBook.CreateDataFormat().GetFormat("yyyy/MM/dd HH:mm:ss");

            var dataStyle7 = workBook.CreateCellStyle(); // 百分比

            dataStyle7.CloneStyleFrom(dataStyle);
            dataStyle7.DataFormat = workBook.CreateDataFormat().GetFormat("0.00%");

            var dataStyle8 = workBook.CreateCellStyle(); // 是否。

            dataStyle8.CloneStyleFrom(dataStyle);

            // 创建空白行
            sheet.CreateRow(0);

            if (e.Cannelled)
            {
                return;
            }

            // 创建标题行。
            var titleRow = sheet.CreateRow(1);

            for (var i = 0; i < e.Columns.Count; ++i)
            {
                if (e.Cannelled)
                {
                    return;
                }

                var colIndex = i + 1; // Excel表格中的列序号。
                var col      = e.Columns[i];

                var cell = titleRow.CreateCell(colIndex);
                cell.SetCellValue(col.Title);
                cell.CellStyle = titleStyle1;
                sheet.SetColumnWidth(colIndex, col.Width * 50 /* 1/5 of character width */);
            }

            var total = e.Binding.DataTable.Count;

            for (int j = 0; j < total; ++j)
            {
                if (e.Cannelled)
                {
                    return;
                }

                var rowIndex = j + 2; // Excel表格中的行序号。
                var dataRow  = sheet.CreateRow(rowIndex);
                for (int i = 0; i < e.Columns.Count; ++i)
                {
                    if (e.Cannelled)
                    {
                        return;
                    }

                    var colIndex = i + 1; // Excel表格中的列序号。
                    var col      = e.Columns[i];

                    var cell = dataRow.CreateCell(colIndex);

                    var value = e.Binding.GetCellValue(j, col.DataKey);
                    if (value == null || string.IsNullOrWhiteSpace(value.ToString()))
                    {
                        continue;
                    }
                    switch (col.Type)
                    {
                    case MyGridColumnType.Boolean:
                        cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                        cell.SetCellValue(value + "");
                        cell.CellStyle = dataStyle8;
                        break;

                    case MyGridColumnType.Date:
                        cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                        cell.SetCellValue(Convert.ToDateTime(value));
                        cell.CellStyle = dataStyle5;
                        break;

                    case MyGridColumnType.DateTime:
                        cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                        cell.SetCellValue(Convert.ToDateTime(value));
                        cell.CellStyle = dataStyle6;
                        break;

                    case MyGridColumnType.Money:
                        cell.SetCellType(NPOI.SS.UserModel.CellType.Numeric);
                        cell.SetCellValue(Convert.ToDouble(value));
                        cell.CellStyle = dataStyle4;
                        break;

                    case MyGridColumnType.IntMoney:
                        cell.SetCellType(NPOI.SS.UserModel.CellType.Numeric);
                        cell.SetCellValue(Convert.ToInt64(value));
                        cell.CellStyle = dataStyle3;
                        break;

                    case MyGridColumnType.Percent:
                        cell.SetCellType(NPOI.SS.UserModel.CellType.Numeric);
                        cell.SetCellValue(Convert.ToDouble(value));
                        cell.CellStyle = dataStyle7;
                        break;

                    case MyGridColumnType.Number:
                        cell.SetCellType(NPOI.SS.UserModel.CellType.Numeric);
                        cell.SetCellValue(Convert.ToInt64(value));
                        cell.CellStyle = dataStyle2;
                        break;

                    default:
                        cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                        cell.SetCellValue(Convert.ToString(value));
                        cell.CellStyle = dataStyle1;
                        break;
                    }
                }

                e.UpdateProgress(j + 1);
            }

            if (e.Cannelled)
            {
                return;
            }

            using (var fs = File.OpenWrite(e.FileName)) {
                e.UpdateProgress(total, "保存文件...");
                workBook.Write(fs);
            }

            e.UpdateProgress(total, "已保存");
        } // end of Export.
Example #2
0
        static void ExportToExcel(object sender, ExportExcelEventArgs e)
        {
            var workBook = new HSSFWorkbook();

            CreateStyles(workBook);
            var sheet = workBook.CreateSheet("exported-data");

            if (e.Cannelled)
            {
                return;
            }

            var titleStyle1 = CreateTitleStyle(workBook);



            // 创建空白行
            sheet.CreateRow(0);

            if (e.Cannelled)
            {
                return;
            }

            // 创建标题行。
            var titleRow = sheet.CreateRow(1);

            for (var i = 0; i < e.Columns.Count; ++i)
            {
                if (e.Cannelled)
                {
                    return;
                }

                var colIndex = i + 1; // Excel表格中的列序号。
                var col      = e.Columns[i];

                var cell = titleRow.CreateCell(colIndex);
                cell.SetCellValue(col.Title);
                cell.CellStyle = titleStyle1;
                sheet.SetColumnWidth(colIndex, col.Width * 50 /* 1/5 of character width */);
            }

            var total = e.Binding.DataTable.Count;

            for (int j = 0; j < total; ++j)
            {
                if (e.Cannelled)
                {
                    return;
                }

                var rowIndex = j + 2; // Excel表格中的行序号。
                var dataRow  = sheet.CreateRow(rowIndex);
                for (int i = 0; i < e.Columns.Count; ++i)
                {
                    if (e.Cannelled)
                    {
                        return;
                    }

                    var colIndex = i + 1; // Excel表格中的列序号。
                    var col      = e.Columns[i];

                    var cell = dataRow.CreateCell(colIndex);

                    var value = e.Binding.GetCellValue(j, col.DataKey);
                    if (value == null || string.IsNullOrWhiteSpace(value.ToString()))
                    {
                        continue;
                    }
                    switch (col.Type)
                    {
                    case MyGridColumnType.Boolean:
                        SetCellStyle(CellStyleCustom.Bool, cell);
                        cell.SetCellValue(value + "");
                        break;

                    case MyGridColumnType.Date:
                        SetCellStyle(CellStyleCustom.Date, cell);
                        cell.SetCellValue(Convert.ToDateTime(value));
                        break;

                    case MyGridColumnType.DateTime:
                        SetCellStyle(CellStyleCustom.DateTime, cell);
                        cell.SetCellValue(Convert.ToDateTime(value));
                        break;

                    case MyGridColumnType.Money:
                        SetCellStyle(CellStyleCustom.DecimalMoney, cell);
                        cell.SetCellValue(Convert.ToDouble(value));
                        break;

                    case MyGridColumnType.IntMoney:
                        SetCellStyle(CellStyleCustom.IntMoney, cell);
                        cell.SetCellValue(Convert.ToInt64(value));
                        break;

                    case MyGridColumnType.Percent:
                        SetCellStyle(CellStyleCustom.Percent, cell);
                        cell.SetCellValue(Convert.ToDouble(value));
                        break;

                    case MyGridColumnType.Number:
                        SetCellStyle(CellStyleCustom.Integer, cell);
                        cell.SetCellValue(Convert.ToInt64(value));
                        break;

                    default:
                        SetCellStyle(CellStyleCustom.Text, cell);
                        cell.SetCellValue(Convert.ToString(value));
                        break;
                    }
                }

                e.UpdateProgress(j + 1);
            }

            if (e.Cannelled)
            {
                return;
            }

            SaveExcel(workBook, e.FileName, new Action(() => {
                e.UpdateProgress(total, "保存文件...");
            }));

            e.UpdateProgress(total, "已保存");
        } // end of Export.