C# NPOI导出Excel横向纵向显示

复制代码

    /// <summary>
    /// DataTable导出Excel(纵向导出)
    /// </summary>
    /// <param name="dataTable">数据源</param>
    /// <param name="filePath">保存的路径</param>
    /// <param name="documentname">表名</param>
    public static void Excel2(this DataTable dataTable, string filePath, string documentname)
    {
        string sheetName = "Sheet1";
        if (dataTable == null || dataTable.Rows.Count == 0)
            throw new Exception("No data to export");

        ISheet sheet = null;
        IWorkbook workbook = null;

        try
        {
            if (Directory.Exists(filePath) == false)
            {
                Directory.CreateDirectory(filePath);
            }
            string filedocmentname = "\\File Card.xls";//文件名
            using (FileStream fs = new FileStream(filePath + filedocmentname, FileMode.Create, FileAccess.Write))
            {
                workbook = new HSSFWorkbook();
                if (string.IsNullOrEmpty(sheetName))
                    sheetName = "Sheet1";//工作簿
                sheet = workbook.CreateSheet(sheetName);
                IRow row = sheet.CreateRow(0);
                IFont font = workbook.CreateFont();
                font.FontName = "Arial";//字体样式
                font.Boldweight = (short)FontBoldWeight.Bold;
                ICellStyle headerStyle2 = workbook.CreateCellStyle();
                headerStyle2.VerticalAlignment = VerticalAlignment.Center; //垂直居中
                headerStyle2.WrapText = true;//自动换行
                font.FontHeightInPoints = 9;//字体

                for (int i = 0; i < dataTable.Columns.Count; i++)
                {
                    ICellStyle header= workbook.CreateCellStyle();
                    header.VerticalAlignment = VerticalAlignment.Center; //垂直居中
                    header.WrapText = true;//自动换行
                    var row1_ = sheet.CreateRow(i);//创建行
                    var cellName = row1_.CreateCell(0);//列名
                    cellName.SetCellValue(dataTable.Columns[i].ColumnName.ToString().Replace("<br />", "\n"));
                    IFont font2 = workbook.CreateFont();
                    font2.Boldweight = (short)FontBoldWeight.Bold;
                    header.SetFont(font);
                    cellName.CellStyle = header;
　　　　　　　　　　　　　　 //填充内容
                    for (int j = 0; j < dataTable.Rows.Count; j++)
                    {
                        var cell1_ = row1_.CreateCell(j + 1);
                        cell1_.SetCellValue(dataTable.Rows[j][i].ToString().Replace("<br />", "\n"));
                        cell1_.CellStyle = headerStyle2; //把样式赋给单元格
                    }
                }
                //设置列宽
                sheet.SetColumnWidth(0, 10 * 256+200);//列宽为10
                //设置行高
                //row.Height = 30 * 20;//行高为30
                workbook.Write(fs); //写入到excel
            }
        }
        finally
        {
            if (workbook != null)
                workbook.Clear();
        }
    }

vb.net教程
c#教程

 　　　　 /// <summary>
    /// DataTable导出Excel(横向导出)
    /// </summary>
    /// <param name="dataTable">数据源</param>
    /// <param name="filePath">保存的路径</param>
    /// <param name="documentname">表名</param>
    public static void Excel(this DataTable dataTable, string filePath, string documentname)
    {
　　　　　　　//datatable第一列增加序号
        dataTable= AddSeriNumToDataTable(dataTable);
        string sheetName = "Sheet1";
        if (dataTable == null || dataTable.Rows.Count == 0)
            throw new Exception("No data to export");

        ISheet sheet = null;
        IWorkbook workbook = null;

        try
        {
            if (Directory.Exists(filePath) == false)
            {
                Directory.CreateDirectory(filePath);
            }
            string filedocmentname = "\\List.xls";//文件名
            using (FileStream fs = new FileStream(filePath + filedocmentname, FileMode.Create, FileAccess.Write))
            {
                workbook = new HSSFWorkbook();
                if (string.IsNullOrEmpty(sheetName))
                    sheetName = "Sheet1";
                sheet = workbook.CreateSheet(sheetName);
                IRow row = sheet.CreateRow(0);
                IFont font = workbook.CreateFont();
                font.FontName = "Arial";//字体样式
                ICellStyle headerStyle2 = workbook.CreateCellStyle();
                headerStyle2.VerticalAlignment = VerticalAlignment.Center; //垂直居中
                headerStyle2.WrapText = true;//自动换行
                font.FontHeightInPoints = 9;//字体
                //填充表头
                for (int columnIndex = 0; columnIndex < dataTable.Columns.Count; columnIndex++)
                {
                    ICellStyle headerStyle = workbook.CreateCellStyle(); 
                    headerStyle.FillForegroundColor = IndexedColors.PaleBlue.Index;//首行填充颜色
                    headerStyle.FillPattern = FillPattern.SolidForeground;
                    headerStyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中 
                    headerStyle.WrapText = true;//自动换行
                    headerStyle.BorderTop = BorderStyle.Thin;//上边框
                    headerStyle.BorderBottom = BorderStyle.Thin;//下边框
                    headerStyle.BorderLeft = BorderStyle.Thin;//左边框
                    headerStyle.BorderRight = BorderStyle.Thin;//右边框
                    var cell = row.CreateCell(columnIndex);
                    cell.CellStyle = headerStyle;
                    cell.SetCellValue(dataTable.Columns[columnIndex].ColumnName.Replace("<br />", "\n")); //设置列名
                    row.HeightInPoints= 35;
                }
                //填充内容
                for (int rowIndex = 0; rowIndex < dataTable.Rows.Count; rowIndex++)
                {
                    row = sheet.CreateRow(rowIndex + 1);
                    for (int columnIndex = 0; columnIndex < dataTable.Columns.Count; columnIndex++)
                    {
                        row.CreateCell(columnIndex).SetCellValue(Convert.ToString(dataTable.Rows[rowIndex][columnIndex]));
                        ICell cellData = row.CreateCell(columnIndex);
                        cellData.SetCellValue(dataTable.Rows[rowIndex][columnIndex].ToString().Replace("<br />", "\n"));
                        cellData.CellStyle = headerStyle2; //把样式赋给单元格
                    }
                }
                sheet.CreateFreezePane(0, 1, 0, 1); //首行冻结
                workbook.Write(fs); //写入到excel
            }
        }
        finally
        {
            if (workbook != null)
                workbook.Clear();
        }
    }

    /// <summary>
    /// 在DataTable中添加一序号列，编号从1依次递增
    /// </summary>
    /// <param >DataTable</param>
    /// <returns></returns>
    public static DataTable AddSeriNumToDataTable(DataTable dt)
    {
        //需要返回的值
        DataTable dtNew;
        if (dt.Columns.IndexOf("序号") >= 0)
        {
            dtNew = dt;
        }
        else //添加一序号列,并且在第一列
        {
            int rowLength = dt.Rows.Count;
            int colLength = dt.Columns.Count;
            DataRow[] newRows = new DataRow[rowLength];
            dtNew = new DataTable();
            //在第一列添加“序号”列
            dtNew.Columns.Add("序号");
            for (int i = 0; i < colLength; i++)
            {
                dtNew.Columns.Add(dt.Columns[i].ColumnName);
                //复制dt中的数据
                for (int j = 0; j < rowLength; j++)
                {
                    if (newRows[j] == null)
                        newRows[j] = dtNew.NewRow();
                    //将其他数据填充到第二列之后，因为第一列为新增的序号列
                    newRows[j][i + 1] = dt.Rows[j][i];
                }
            }
            foreach (DataRow row in newRows)
            {
                dtNew.Rows.Add(row);
            }
        }
        //对序号列填充，从1递增
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            dtNew.Rows[i]["序号"] = i + 1;
        }
        return dtNew;
    }

　　　　　/// <summary>
    /// Datatable导出Excel(无任何样式)
    /// </summary>
    /// <param >DataTable</param>
    /// <returns></returns>
    public static void ExportExcel(DataTable dataTable, string filePath, string documentname)
    {
        if (dataTable != null && dataTable.Rows.Count > 0)
        {
            HSSFWorkbook excelBook = new HSSFWorkbook();
            ISheet sheet1 = excelBook.CreateSheet("Export Data");
            IRow row1 = sheet1.CreateRow(0);

            //填充表头
            for (int i = 0; i < dataTable.Columns.Count; i++)
            {
                row1.CreateCell(i).SetCellValue(dataTable.Columns[i].ColumnName);
            }

            //填充内容
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                IRow rowTemp = sheet1.CreateRow(i + 1);
                for (int j = 0; j < dataTable.Columns.Count; j++)
                {
                    rowTemp.CreateCell(j).SetCellValue(string.Format("{0}", dataTable.Rows[i][j]));
                }
            }
            //保存
            MemoryStream ms = new MemoryStream();
            excelBook.Write(ms);
            if (Directory.Exists(filePath) == false)
            {
                Directory.CreateDirectory(filePath);
            }
            string filedocmentname = "\\List.xls";
            using (FileStream fs = new FileStream(filePath + filedocmentname, FileMode.Create, FileAccess.Write))
            {
                byte[] data = ms.ToArray();
                fs.Write(data, 0, data.Length);
                fs.Flush();
            }
            ms.Close();
            ms.Dispose();
        }
    }

纵向导出Excel效果图

vb.net教程
c#教程

横向导出Excel效果图