云智博客-blog

在云智系统中,有许多的数据需要导入到excel,使用微软的office对于web来说要求对服务器的权限过高,所以我使用的第三方的开源软件NPOI.

下面的代码示例就是直接将DataTable转为Excel.表头通过titleArr进行定义.

/// <summary>  
/// 导出Excel  
/// </summary>  
/// <param name="Dt">数据源</param>  
/// <param name="ExcleName">导入文件名称</param>  
/// <param name="SheetName">工作薄名称</param>  
/// <param name="titleArr">标题栏</param>  
/// <param name="clumnArr">栏位名</param>  
public static void ResponseDataSetToExcel(DataTable Dt, string ExcleName, string SheetName, string[] titleArr, string[] clumnArr)
{
    HSSFWorkbook hssfworkbook = new HSSFWorkbook();

    DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
    dsi.Company = "Ciemis";
    hssfworkbook.DocumentSummaryInformation = dsi;

    //create a entry of SummaryInformation  
    SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
    si.Subject = " Export  Excel";
    hssfworkbook.SummaryInformation = si;

    HSSFSheet excelSheet = (HSSFSheet)hssfworkbook.CreateSheet(SheetName);
    int rowCount = 0;
    HSSFRow newRow = (HSSFRow)excelSheet.CreateRow(0);
    rowCount++;
    //循环写出列头           
    for (int i = 0; i < titleArr.Length; i++)
    {
        HSSFCell newCell = (HSSFCell)newRow.CreateCell(i);
        newCell.SetCellValue(titleArr[i]);
    }
    for (int i = 0; i < Dt.Rows.Count; i++)
    {
        rowCount++;
        HSSFRow newRowData = (HSSFRow)excelSheet.CreateRow(rowCount);
        DataRow dr = Dt.Rows[i];
        for (int j = 0; j < clumnArr.Length; j++)
        {
            HSSFCell newCell = (HSSFCell)newRow.CreateCell(rowCount);
            newCell.SetCellValue(dr[titleArr[j]].ToString());
        }
    }

    HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
    //避免乱码System.Text.Encoding.UTF8
    HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(ExcleName, System.Text.Encoding.UTF8));
    HttpContext.Current.Response.Clear();

    MemoryStream file = new MemoryStream();
    hssfworkbook.Write(file);

    HttpContext.Current.Response.BinaryWrite(file.GetBuffer());
    HttpContext.Current.Response.Flush();
    HttpContext.Current.Response.End();
}

要导出一个DataSet,里面的多个DataTable对应Excel的多个Sheet;

且自动根据数据类型设置Excel的单元格格式.

/// <summary>
/// 导出Excel  
/// </summary>
/// <param name="sourceDs"></param>
/// <param name="fileName">导出的文件名</param>
/// <param name="sheetName">导出的表名,多个表请以逗号(,)分割</param>
public static void ResponseDataSetToExcel(DataSet sourceDs, string fileName, string sheetName = null)
{
    fileName = HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8).ToString();

    HSSFWorkbook workbook = new HSSFWorkbook();
    MemoryStream ms = new MemoryStream();
    string[] sheetNames = (sheetName ?? string.Empty).Split(',');
    int length = sourceDs.Tables.Count;
    for (int i = 0; i < length; i++)
    {
        var _sheetName = sheetNames.Length > i ? sheetNames[i] : string.Format("Sheet{0}", i);
        var sheet = workbook.CreateSheet(_sheetName);
        sheet.CreateFreezePane(0, 1, 0, 1);
        //headStyle
        var headStyle = workbook.CreateCellStyle();
        IFont font = workbook.CreateFont();
        font.Boldweight = (short)FontBoldWeight.Bold;
        font.FontHeightInPoints = (short)11;
        font.Color = HSSFColor.Black.Index;
        headStyle.FillBackgroundColor = HSSFColor.Black.Index;
        //headStyle.FillPattern = FillPattern.SolidForeground;
        headStyle.SetFont(font);
        //cellStyleDet
        var cellStyleDet = workbook.CreateCellStyle();
        IFont font1 = workbook.CreateFont();
        font.FontHeightInPoints = (short)10;
        cellStyleDet.SetFont(font1);

        var headerRow = sheet.CreateRow(0);
        // handling header.
        foreach (DataColumn column in sourceDs.Tables[i].Columns)
        {
            var headCell = headerRow.CreateCell(column.Ordinal);
            headCell.SetCellValue(column.ColumnName);
            headCell.CellStyle = headStyle;
        }
        // handling value.
        int rowIndex = 1;
        foreach (DataRow row in sourceDs.Tables[i].Rows)
        {
            var dataRow = sheet.CreateRow(rowIndex);
            foreach (DataColumn column in sourceDs.Tables[i].Columns)
            {
                var detCell = dataRow.CreateCell(column.Ordinal);
                var cellValue = row[column].ToString();
                var type = column.DataType.ToString().ToLower();
                if (row[column] != DBNull.Value)
                {
                    if (type.Contains("decimal") || type.Contains("double") || type.Contains("int"))
                    {
                        detCell.SetCellValue(Convert.ToDouble(row[column]));
                    }
                    else if (type.Contains("datetime"))
                    {
                        detCell.SetCellValue(((DateTime)row[column]).ToString("yyyy-MM-dd HH:mm:ss"));
                    }
                    else
                    {
                        detCell.SetCellValue(row[column].ToString());
                    }
                }
                detCell.CellStyle = cellStyleDet;
            }
            rowIndex++;
        }
    }
    workbook.Write(ms);

    HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
    HttpContext.Current.Response.BinaryWrite(ms.GetBuffer());
    HttpContext.Current.Response.Flush();
    HttpContext.Current.Response.End();

    ms.Flush();
    ms.Position = 0;
    ms.Close();
    workbook = null;
    ms = null;
}

如果想对excel进行只读处理,不许对导出的数据进行修改,只需要加入一行代码:

sheet.ProtectSheet("password");