c# excel xls保存

  public HSSFWorkbook Excel_Export(DataTable query,string title,int[] rowweight,string[] rowtitle)
        {

            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.CreateSheet("Sheet1") as HSSFSheet;
            IRow row = sheet.CreateRow(0);
            int ii = 0;

            Color c = Color.FromArgb(215, 228, 188);
            HSSFPalette palette = workbook.GetCustomPalette();
            palette.SetColorAtIndex((short)63, c.R, c.G, c.B);
            HSSFColor cellColor = palette.FindColor(c.R, c.G, c.B);

            ICellStyle style = workbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.Center;
            style.WrapText = true;
            style.BorderLeft = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            style.BorderTop = BorderStyle.Thin;
            style.BorderBottom = BorderStyle.Thin;
            style.VerticalAlignment = VerticalAlignment.Center;
            style.FillPattern = FillPattern.SolidForeground;
            style.FillForegroundColor = cellColor.Indexed;
            IFont font = workbook.CreateFont();
            font.FontHeightInPoints = 11;
            font.FontName = "微软雅黑";
            font.IsBold = true;
            style.SetFont(font);

            ICell cell = row.CreateCell(ii);

            if(!string.IsNullOrEmpty(title))
            {
                row.Height = 50 * 20;
                cell.SetCellValue(title);
                cell.CellStyle = style;
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, query.Columns.Count-1));
                ii += 1;
            }

            style = workbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.Left;
            style.WrapText = true;

            style.VerticalAlignment = VerticalAlignment.Center;
            style.BorderLeft = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            style.BorderTop = BorderStyle.Thin;
            style.BorderBottom = BorderStyle.Thin;
            style.FillPattern = FillPattern.SolidForeground;
            style.FillForegroundColor = cellColor.Indexed;
            font = workbook.CreateFont();
            font.FontHeightInPoints = 11;
            font.FontName = "微软雅黑";
            style.SetFont(font);


            IRow row1 = sheet.CreateRow(ii);
            row1.Height = 60 * 20;

            int i = 0;
            if(rowtitle.Length>0)
                foreach (var item in rowtitle)
                {
                    cell = row1.CreateCell(i);
                    cell.SetCellValue(item.ToString());
                    cell.CellStyle = style;
                    i += 1;
                }
            else
                foreach (var item in  query.Columns)
                {
                    cell = row1.CreateCell(i);
                    cell.SetCellValue(item.ToString());
                    cell.CellStyle = style;
                    i += 1;
                }
      
            for ( i = 0; i < rowweight.Length; i++)
            {
                if (rowweight[i] > 0)
                {
                    row1.Cells[i].CellStyle = style; //把样式赋给单元格
                    sheet.SetColumnWidth(i, rowweight[i] * 256);//设置列宽
                }
            }
            i = 0;
            row.Height = 30 * 20;
            style = workbook.CreateCellStyle();
            style.WrapText = true;
            style.Alignment = HorizontalAlignment.Left;
            style.VerticalAlignment = VerticalAlignment.Center;
            style.BorderLeft = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            style.BorderTop = BorderStyle.Thin;
            style.BorderBottom = BorderStyle.Thin;
            font = workbook.CreateFont();
            font.FontHeightInPoints = 10;
            font.FontName = "微软雅黑";
            style.SetFont(font);
            for (int n = 0; n < query.Rows.Count; n++)
            {
                ii += 1;
                IRow rowtemp = sheet.CreateRow(ii);
                for (int j = 0; j < query.Columns.Count; j++)
                {
                    cell = rowtemp.CreateCell(j);
                    cell.CellStyle = style;
                    cell.SetCellValue(query.Rows[n][j]?.ToString() ?? "");  
              
                 }
            }
            return workbook;
        }

 

  //写文件
            MemoryStream ms = new MemoryStream();
            workbook.Write(ms);
            ms.Flush();
            ms.Seek(0, SeekOrigin.Begin); //ms.Position = 0;


            string fileName = string.Concat(string.Format("{0:yyyyMMddHHmmssffff}", DateTime.Now), ".xls");
            return File(ms, "application/vnd.ms-excel", fileName);

 

转载于:https://www.cnblogs.com/LiuFengH/p/10576955.html