C# 关于导出的Excel设置批注 设置格式为int类型 设置字体

将数据导出，导出Excel有时候需要一些条件，比如：1.需要给某一列的表头加批注；2：需要设置某一列为int格式

1.给某一列的表头加批注

　　　　　　　　　 List<string> texts = new List<string>();
                List<int> ids = new List<int>();
                texts.Add("这是一个批注");
                ids.Add(19);

上述为给第19列的表头设置批注：“这是一个批注”

 

2.给某列的格式设置为int类型

               table.Columns[16].DataType = typeof(int);
               table.Columns[18].DataType = typeof(double);             

 

3.导出上述指定格式的数据

 ExcelHelper.ExportToExcel(table, "会议调研统计导出-" + DateTime.Now.ToString("yyyyMMddHHmmss"), texts, ids); 

public static void ExportToExcel(DataTable dt, string fileName, List<string> IComments, List<int> Cells)
        {
            try
            {
                var ds = new DataSet();
                ds.Tables.Add(dt);
                ExportToExcel(ds, fileName, IComments,Cells);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

public static void ExportToExcel(DataSet ds, string fileName,List<string> IComments,List<int> Cells)
        {
            IWorkbook wookBook = new XSSFWorkbook();

            ICellStyle headerStyle = wookBook.CreateCellStyle();
            var headerFont = wookBook.CreateFont();
            headerFont.FontHeightInPoints = 12;
            headerFont.FontName = "黑体";
            headerFont.Color = HSSFColor.BlueGrey.Index;
            headerStyle.SetFont(headerFont);

            ICellStyle cellStyle = wookBook.CreateCellStyle();
            //cellStyle.WrapText = true;//自动换行
            cellStyle.VerticalAlignment = VerticalAlignment.Center;//垂直对齐  

            foreach (DataTable dt in ds.Tables)
            {
                ISheet sheet = wookBook.CreateSheet(dt.TableName);
                IRow sheetColumn = sheet.CreateRow(0);

                //写入列头
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    var cell = sheetColumn.CreateCell(j);
                    cell.SetCellValue(dt.Columns[j].ColumnName);
                    cell.CellStyle = headerStyle;

                    sheet.AutoSizeColumn(j);//表格列宽自动调整,只能支持英文和数字
                }
                //写入行
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    IRow row = sheet.CreateRow(i + 1);
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        string gbStr = Encoding.GetEncoding("UTF-8").GetString(Encoding.UTF8.GetBytes(dt.Rows[i][j].ToString()));
                        var cell = row.CreateCell(j);
                        cell.CellStyle = cellStyle;

                        try
                        {
                            var dataType = dt.Columns[j].DataType;
                            var tc = Type.GetTypeCode(dataType);
                            if (tc == TypeCode.Int16 || tc == TypeCode.UInt16 || tc == TypeCode.Int32 || tc == TypeCode.UInt32 ||
                                tc == TypeCode.Int64 || tc == TypeCode.UInt64 || tc == TypeCode.Single || tc == TypeCode.Double || tc == TypeCode.Decimal)
                            {
                                cell.SetCellType(CellType.Numeric);
                                double d;
                                double.TryParse(gbStr, out d);
                                cell.SetCellValue(d);
                            }
                            else
                            {
                                cell.SetCellValue(gbStr);
                            }
                        }
                        catch (Exception)
                        {
                        }
                    }
                }
            }
            NPOI.SS.UserModel.ISheet newsheet = wookBook.GetSheetAt(0);
            NPOI.SS.UserModel.IDrawing drawing = newsheet.CreateDrawingPatriarch();
            int a = 0;
            foreach (var item in IComments)
            {
                NPOI.SS.UserModel.IComment comment1 = drawing.CreateCellComment(new NPOI.XSSF.UserModel.XSSFClientAnchor(0,0,0,0,0,0,0,0));
                comment1.Author = "";
                comment1.String = new NPOI.XSSF.UserModel.XSSFRichTextString(item);
                NPOI.SS.UserModel.IRow row = newsheet.GetRow(0);
                row.GetCell(Cells[0]).CellComment = comment1;
                a++;
            }
            var ms = new MemoryStream();
            wookBook.Write(ms);

            //这里判断使用的浏览器是否为Firefox，Firefox导出文件时不需要对文件名显示编码，编码后文件名会乱码
            //但是IE和Google需要编码才能保持文件名正常
            if (HttpContext.Current.Request.ServerVariables["http_user_agent"].ToString().IndexOf("Firefox") != -1 ||
                HttpContext.Current.Request.ServerVariables["http_user_agent"].ToString().IndexOf("Safari") != -1)
            {
                HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
            }
            else
            {
                HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8) + ".xlsx");
            }

            HttpContext.Current.Response.ContentType = "application/OCTET-STREAM;charset=UTF-8";
            HttpContext.Current.Response.BinaryWrite(ms.ToArray());
            HttpContext.Current.Response.End();
            ms.Flush();
            ms.Position = 0;
            ms.Close();
            ms = null;
        }