示例#1
0
        /// <summary>
        /// DataTable导出到Excel的MemoryStream
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        public static MemoryStream ExportDt(DataTable dtSource, string strHeaderText)
        {
            var workbook = new HSSFWorkbook();
            var sheet    = workbook.CreateSheet() as HSSFSheet;

            var cellStyle = workbook.CreateBasicCellStyle();

            HSSFCellStyle dateStyle = workbook.CreateBasicCellStyle();
            var           format    = workbook.CreateDataFormat() as HSSFDataFormat;

            // dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd HH:mm:ss");
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
            //取得列宽
            int[] arrColWidth = new int[dtSource.Columns.Count];
            foreach (DataColumn item in dtSource.Columns)
            {
                arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName).Length + 6;
            }
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < dtSource.Columns.Count; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }
            int rowIndex = 0;

            foreach (DataRow row in dtSource.Rows)
            {
                #region 新建表,填充表头,填充列头,样式

                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet() as HSSFSheet;
                    }

                    #region 表头及样式

                    if (!string.IsNullOrEmpty(strHeaderText) && strHeaderText != "下载开票模板_Evan")
                    {
                        var headerRow = sheet.CreateRow(0) as HSSFRow;
                        headerRow.HeightInPoints = 25;
                        headerRow.CreateCell(0).SetCellValue(strHeaderText);

                        var headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
                        headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                        var font = workbook.CreateFont() as HSSFFont;
                        font.FontHeightInPoints = 16;
                        font.Boldweight         = 700;
                        headStyle.SetFont(font);

                        headerRow.GetCell(0).CellStyle = headStyle;

                        sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
                        //headerRow.Dispose();

                        if (dtSource.Columns[dtSource.Columns.Count - 1].ColumnName == "编号")
                        {
                            sheet.SetColumnHidden(dtSource.Columns.Count - 1, true);
                        }
                        //  sheet.SetColumnHidden();
                    }

                    #endregion

                    #region 列头及样式

                    {
                        var rownum = 0;
                        if (strHeaderText == "下载开票模板_Evan")
                        {
                            rownum = 0;
                        }
                        else
                        {
                            rownum = string.IsNullOrEmpty(strHeaderText) ? 0 : 1;
                        }
                        var headerRow = sheet.CreateRow(rownum) as HSSFRow;

                        headerRow.HeightInPoints = 20;

                        var headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
                        headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                        var font = workbook.CreateFont() as HSSFFont;
                        font.FontHeightInPoints = 11;
                        font.Boldweight         = 600;
                        headStyle.SetFont(font);

                        headStyle.BorderLeft   = BorderStyle.Thin;
                        headStyle.BorderRight  = BorderStyle.Thin;
                        headStyle.BorderTop    = BorderStyle.Thin;
                        headStyle.BorderBottom = BorderStyle.Thin;

                        foreach (DataColumn column in dtSource.Columns)
                        {
                            if (strHeaderText == "下载开票模板_Evan")
                            {
                                //headStyle.IsLocked = true;
                                headerRow.CreateCell(column.Ordinal).CellStyle = headStyle;
                                sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
                                headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                            }
                            else
                            {
                                headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                                headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
                                //设置列宽
                                //sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
                            }
                        }
                        //sheet.CreateFreezePane(0, 1, 0, 1);
                        //sheet.CreateFreezePane(1, 0, 1, 0);
                        //headerRow.Dispose();
                    }

                    #endregion

                    if (strHeaderText == "下载开票模板_Evan")
                    {
                        rowIndex = 1;
                    }
                    else
                    {
                        rowIndex = string.IsNullOrEmpty(strHeaderText) ? 1 : 2;
                    }
                }

                #endregion

                #region 填充内容

                var dataRow = sheet.CreateRow(rowIndex) as HSSFRow;

                foreach (DataColumn column in dtSource.Columns)
                {
                    var newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;

                    newCell.CellStyle = cellStyle;

                    string drValue = row[column].ToString();

                    #region 写单元格的值

                    switch (column.DataType.ToString())
                    {
                    case "System.String":     //字符串类型
                        double result;
                        if (IsNumeric(drValue, out result))
                        {
                            double.TryParse(drValue, out result);
                            newCell.SetCellValue(result);
                            break;
                        }
                        else
                        {
                            newCell.SetCellValue(drValue);
                            break;
                        }

                    case "System.DateTime":     //日期类型
                        DateTime dateV;
                        if (DateTime.TryParse(drValue, out dateV))
                        {
                            newCell.SetCellValue(dateV);
                            newCell.CellStyle = dateStyle;     //格式化显示
                        }
                        break;

                    case "System.Boolean":     //布尔型
                        bool boolV = false;
                        bool.TryParse(drValue, out boolV);
                        newCell.SetCellValue(boolV);
                        break;

                    case "System.Int16":     //整型
                    case "System.Int32":
                    case "System.Int64":
                    case "System.Byte":
                        int intV = 0;
                        int.TryParse(drValue, out intV);
                        newCell.SetCellValue(intV);
                        break;

                    case "System.Decimal":     //浮点型
                    case "System.Double":
                        double doubV = 0;
                        double.TryParse(drValue, out doubV);
                        newCell.SetCellValue(doubV);
                        break;

                    case "System.DBNull":     //空值处理
                        newCell.SetCellValue("");
                        break;

                    default:
                        newCell.SetCellValue(drValue);
                        break;
                    }

                    #endregion

                    if (strHeaderText == "下载开票模板_Evan")
                    {
                        if (column.ColumnName == "Booking Branch" || column.ColumnName == "Customer ID" || column.ColumnName == "EBBS relationship ID" || column.ColumnName == "Customer ID/counterparty ID" || column.ColumnName == "EBBS Master ID" || column.ColumnName == "分行代码" || column.ColumnName == "客户编码")
                        {
                            newCell.SetCellValue(drValue);
                        }
                    }
                }

                #endregion

                rowIndex++;
            }
            using (var ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;

                //sheet.Dispose();
                //workbook.Dispose();

                return(ms);
            }
        }