C#操作EXCEL文件方法

目前仅尝试过微软官方提供的COM组件以及第三方类库NPOI进行EXCEL文件的操作，COM组件明显效率较低，推荐NPOI或者新兴的MiniExcel。

COM组件读取

/// <summary>
/// 通过COM组件读取Excel文件内容
/// </summary>
/// <param name="excelFilePath">Excel文件所在路径</param>
/// <returns>数据集</returns>
private DataSet GetExcelData(string excelFilePath)
{
    DataSet dataSet = new DataSet();
    Excel.Application app = new Excel.Application();
    Sheets sheets;
    Workbook workbook = null;
    object oMissiong = Missing.Value;
    string cellContent;//Excel文件中单元格内容
    try
    {
        if (app == null)
        {
            return null;
        }
        //通过已存在的Execl文件来创建workbook工作簿，即整个excel文档
        workbook = app.Workbooks.Open(excelFilePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong,
            oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);
        // 获取excel内sheet表单个数
        sheets = workbook.Worksheets;
        System.Data.DataTable dt = new System.Data.DataTable();
        for (int p = 1; p <= sheets.Count; p++)
        {
            //workSheet存储读取的Excel文件内的第p张表
            Worksheet workSheet = (Worksheet)sheets.get_Item(p);
            //读取列标题，添加到dt表中，通过Cells中的行数坐标控制读取的列所在行
            for (int j = 1; j <= workSheet.UsedRange.Columns.Count; j++)
            {
                Range _range = (Range)workSheet.Cells[2, j];//第2行第j列
                dt.Columns.Add(_range.Text.ToString().Trim());//将读取到的列添加到dt表中
            }
            //读取Excel表中数据，将数据添加到dt表中
            for (int i = 3; i <= workSheet.UsedRange.Rows.Count; i++)
            {
                DataRow dr = dt.NewRow();//每读一行就新建一行存储下一行数据
                for (int j = 1; j <= workSheet.UsedRange.Columns.Count; j++)
                {
                    Range _range = (Range)workSheet.Cells[i, j];//第i行j列
                    cellContent = (_range.Value2 == null) ? "" : _range.Text.ToString().Trim();//正则表达式，分为空和不空两种情况
                    dr[j - 1] = cellContent;//j-1是因为dt表是从0开始的，但是读取excel文件时是从1开始的
                }
                dt.Rows.Add(dr);//将读取到的行内容添加到dt表中
            }
            dataSet.Tables.Add(dt);//添加dt表到数据集dataSet中
        }
    }
    finally
    {
        //workbook.Close(false, oMissiong, oMissiong);
        //System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
        //workbook = null;
        //app.Workbooks.Close();
        //app.Quit();
        //System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
        //app = null;
        //GC.Collect();
        //GC.WaitForPendingFinalizers();
        ClosePro(app, workbook);//释放资源
    }
    return dataSet;
}

读取过程中的资源释放问题

[DllImport("User32.dll", CharSet = CharSet.Auto)]
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);

/// <summary>
/// 关闭进程PLUS
/// </summary>
/// <param name="xlApp">应用对象</param>
/// <param name="xlsWorkBook">操作工作簿</param>
public void ClosePro(Excel.Application xlApp, Workbook xlsWorkBook)
{
    if (xlsWorkBook != null)
        xlsWorkBook.Close(true, Type.Missing, Type.Missing);
    xlApp.Quit();
    GC.GetGeneration(xlApp); // 安全回收进程
    IntPtr t = new IntPtr(xlApp.Hwnd);   //获取句柄
    GetWindowThreadProcessId(t, out int k);   //获取进程唯一标志
    System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
    p.Kill();     //关闭进程
}

COM组件打开显示Excel

Excel.Application app = new Excel.Application();
                    app.Visible = true;
                    Excel.Workbook Book = app.Workbooks._Open(ExportDir,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

NPOI读取Excel文件

整个Excel表格叫做工作表：WorkBook（工作薄），包含的叫页（工作表）：Sheet；行：Row；列：Column；单元格Cell。

NPOI是POI的C#版本，NPOI的行和列的index都是从0开始

参考文档：使用NPOI进行Excel数据的导入导出(https://www.cnblogs.com/springsnow/p/13123912.html#_label1)

NPOI读写单张表

using System;
using System.Collections.Generic;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Data;
using System.IO;

namespace Vol_Changer_ShortCircuit.Operate
{
    class ExcelHelper
    {
        /// <summary>
        /// 从Excel读取数据，只支持单表
        /// </summary>
        /// <param name="FilePath">文件路径</param>
        public static DataTable ReadFromExcel(string FilePath)
        {
            IWorkbook wk = null;
            string extension = Path.GetExtension(FilePath); //获取扩展名
            try
            {
                using (FileStream fs = File.OpenRead(FilePath))
                {
                    if (extension.Equals(".xls")) //2003
                    {
                        wk = new HSSFWorkbook(fs);
                    }
                    else                         //2007以上
                    {
                        wk = new XSSFWorkbook(fs);
                    }
                }
                //读取当前表数据，Sheet总数量
                ISheet sheet = wk.GetSheetAt(0);

                //构建DataTable，该部分内容可通过实际需求自行调整DataTable的结构，可以直接传入一个带结构的DataTable，也可以每次都生成
                IRow row = sheet.GetRow(4);//获取行
                DataTable result = BuildDataTable(row);

                if (result != null)
                {
                    if (sheet.LastRowNum >= 1)
                    {
                        for (int i = 5; i < sheet.LastRowNum + 1; i++)
                        {
                            IRow temp_row = sheet.GetRow(i);
                            if (temp_row == null) { continue; }// 修复 行为空时会出错
                            List<object> itemArray = new List<object>();
                            for (int j = 0; j < result.Columns.Count; j++)//解决Excel超出DataTable列问题 
                            {
                                //itemArray.Add(temp_row.GetCell(j) == null ? string.Empty : temp_row.GetCell(j).ToString());
                                itemArray.Add(GetValueType(temp_row.GetCell(j)));//解决 导入Excel  时间格式问题 
                            }

                            result.Rows.Add(itemArray.ToArray());
                        }
                    }
                }
                return result;
            }
            catch (Exception ex)
            {
                //自行定义异常处理方式
            }
        }
       
        /// <summary>
        /// 将DataTable数据导入到excel中
        /// </summary>
        /// <param name="data">要导入的数据</param>
        /// <param name="isColumnWritten">DataTable的列名是否要导入</param>
        /// <param name="sheetName">要导入的excel的sheet的名称</param>
        /// <param name="fileName">导出的文件途径</param>
        /// <returns>导入数据行数(包含列名那一行)</returns>
        public static int DataTableToExcel(DataTable data, string sheetName, string fileName, bool isColumnWritten = true)
        {
            IWorkbook workbook = null;
            using (FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite))
            {
                if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                {
                    workbook = new XSSFWorkbook();
                }
                else if (fileName.IndexOf(".xls") > 0) // 2003版本
                {
                    workbook = new HSSFWorkbook();
                }
                if (workbook == null) { return -1; }

                try
                {
                    ISheet sheet = workbook.CreateSheet(sheetName);
                    int count = 0;
                    if (isColumnWritten) //写入DataTable的列名
                    {
                        IRow row = sheet.CreateRow(0);
                        for (int j = 0; j < data.Columns.Count; ++j)
                        {
                            row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
                        }
                        count = 1;
                    }

                    for (int i = 0; i < data.Rows.Count; ++i)
                    {
                        IRow row = sheet.CreateRow(count);
                        for (int j = 0; j < data.Columns.Count; ++j)
                        {
                            row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
                        }
                        count++;
                    }
                    workbook.Write(fs); //写入到excel

                    return count;
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Exception: " + ex.Message);
                    return -1;
                }
            }
        }
        
        private static DataTable BuildDataTable(IRow Row)
        {
            DataTable result = null;
            if (Row.Cells.Count > 0)
            {
                result = new DataTable();
                for (int i = 0; i < Row.LastCellNum; i++)
                {
                    if (Row.GetCell(i) != null)
                    {
                        result.Columns.Add(Row.GetCell(i).ToString());
                    }
                }
            }
            return result;
        }

        /// <summary>
        /// 获取单元格类型
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static object GetValueType(ICell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellType)
            {
                case CellType.Blank: //BLANK:  
                    return null;
                case CellType.Boolean: //BOOLEAN:  
                    return cell.BooleanCellValue;
                case CellType.Numeric: //NUMERIC:  
                    if (DateUtil.IsCellDateFormatted(cell))
                    {
                        return cell.DateCellValue;
                    }
                    return cell.NumericCellValue;
                case CellType.String: //STRING:  
                    return cell.StringCellValue;
                case CellType.Error: //ERROR:  
                    return cell.ErrorCellValue;
                case CellType.Formula: //FORMULA:  
                    cell.SetCellType(CellType.String);
                    return cell.StringCellValue;
                default:
                    return "=" + cell.CellFormula;
            }
        }
    }
}

多张表处理方案

using System;
using System.Collections.Generic;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Data;
using System.IO;

namespace Vol_Changer_ShortCircuit.Operate
{
    class ExcelHelper
    {
        /// <summary>
        /// 从Excel读取数据，支持多表
        /// </summary>
        /// <param name="FilePath">文件路径</param>
        public static DataSet ReadFromExcels(string FilePath)
        {
            DataSet ds = new DataSet();
            IWorkbook wk = null;
            string extension = Path.GetExtension(FilePath); //获取扩展名
            try
            {
                using (FileStream fs = File.OpenRead(FilePath))
                {
                    if (extension.Equals(".xls")) //2003
                    {
                        wk = new HSSFWorkbook(fs);
                    }
                    else                         //2007以上
                    {
                        wk = new XSSFWorkbook(fs);
                    }
                }

                int SheetCount = wk.NumberOfSheets;//获取表的数量
                if (SheetCount < 1)
                {
                    return ds;
                }
                for (int s = 0; s < SheetCount; s++)
                {
                    //读取当前表数据
                    ISheet sheet = wk.GetSheetAt(s);
                    //构建DataTable
                    IRow row = sheet.GetRow(0);
                    if (row == null) { continue; }
                    DataTable tempDT = BuildDataTable(row);
                    tempDT.TableName = wk.GetSheetName(s);
                    if (tempDT != null)
                    {
                        if (sheet.LastRowNum >= 1)
                        {
                            for (int i = 1; i < sheet.LastRowNum + 1; i++)
                            {
                                IRow temp_row = sheet.GetRow(i);
                                if (temp_row == null) { continue; }// 修复 行为空时会出错
                                List<object> itemArray = new List<object>();
                                for (int j = 0; j < tempDT.Columns.Count; j++)//解决Excel超出DataTable列问题   
                                {
                                    itemArray.Add(GetValueType(temp_row.GetCell(j)));//解决 导入Excel  时间格式问题 
                                }
                                tempDT.Rows.Add(itemArray.ToArray());
                            }
                        }
                        ds.Tables.Add(tempDT);
                    }
                }
                return ds;
            }
            catch (Exception ex)
            {
                return null;
            }
        }
       
        /// <summary>
        /// 将DataSet数据导入到excel中   每个datatable一个sheet,sheet名为datatable名
        /// </summary>
        /// <param name="ds">要导入的数据</param>
        /// <param name="isColumnWritten">DataTable的列名是否要导入</param>
        /// <param name="fileName">导出的文件途径</param>
        public static bool DataTableToExcel(DataSet ds, string fileName, bool isColumnWritten = true)
        {
            if (ds == null || ds.Tables.Count < 1)
            {
                return false;
            }
            IWorkbook workbook = null;
            using (FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite))
            {
                if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                {
                    workbook = new XSSFWorkbook();
                }
                else if (fileName.IndexOf(".xls") > 0) // 2003版本
                {
                    workbook = new HSSFWorkbook();
                }
                if (workbook == null) { return false; }
                try
                {
                    foreach (DataTable dt in ds.Tables)
                    {
                        ISheet sheet = workbook.CreateSheet(dt.TableName);
                        if (isColumnWritten) //写入DataTable的列名
                        {
                            IRow row = sheet.CreateRow(0);
                            for (int j = 0; j < dt.Columns.Count; ++j)
                            {
                                row.CreateCell(j).SetCellValue(dt.Columns[j].ColumnName);
                            }
                        }

                        for (int i = 0; i < dt.Rows.Count; ++i)
                        {
                            IRow row = sheet.CreateRow(isColumnWritten ? i + 1 : i);
                            for (int j = 0; j < dt.Columns.Count; ++j)
                            {
                                row.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
                            }
                        }
                    }
                    workbook.Write(fs); //写入到excel
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Exception: " + ex.Message);
                    return false;
                }
            }
            return true;
        }

        private static DataTable BuildDataTable(IRow Row)
        {
            DataTable result = null;
            if (Row.Cells.Count > 0)
            {
                result = new DataTable();
                for (int i = 0; i < Row.LastCellNum; i++)
                {
                    if (Row.GetCell(i) != null)
                    {
                        result.Columns.Add(Row.GetCell(i).ToString());
                    }
                }
            }
            return result;
        }
    }
}

获取单元格类型

/// <summary>
/// 获取单元格类型
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetValueType(ICell cell)
{
    if (cell == null)
        return null;
    switch (cell.CellType)
    {
        case CellType.Blank: //BLANK:  
            return null;
        case CellType.Boolean: //BOOLEAN:  
            return cell.BooleanCellValue;
        case CellType.Numeric: //NUMERIC:  
            if (DateUtil.IsCellDateFormatted(cell))
            {
                return cell.DateCellValue;
            }
            return cell.NumericCellValue;
        case CellType.String: //STRING:  
            return cell.StringCellValue;
        case CellType.Error: //ERROR:  
            return cell.ErrorCellValue;
        case CellType.Formula: //FORMULA:  
            cell.SetCellType(CellType.String);
            return cell.StringCellValue;
        default:
            return "=" + cell.CellFormula;
    }
}

NPOI读写数据、设置单元格样式

/// <summary>
       /// 将DataTable数据导入到excel中
       /// </summary>
       /// <param name="data">要导入的数据</param>
       /// <param name="isColumnWritten">DataTable的列名是否要导入</param>
       /// <param name="sheetName">要导入的excel的sheet的名称</param>
       /// <param name="fileName">导出的文件途径</param>
       /// <returns>导入数据行数(包含列名那一行)</returns>
       public static int DataTableToExcel(DataTable data, string sheetName, string fileName, bool isColumnWritten = true)
       {
           IWorkbook workbook = null;
           using (FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite))
           {
               if (fileName.IndexOf(".xlsx") > 0) // 2007版本
               {
                   workbook = new XSSFWorkbook();
               }
               else if (fileName.IndexOf(".xls") > 0) // 2003版本
               {
                   workbook = new HSSFWorkbook();
               }
               if (workbook == null) { return -1; }

               try
               {
                   ISheet sheet = workbook.CreateSheet(sheetName);

                   int count = 0;
                   if (isColumnWritten) //写入DataTable的列名
                   {
                       IRow row = sheet.CreateRow(0);
                       ICellStyle columnStyle = workbook.CreateCellStyle();
                       //设置边框
                       columnStyle.BorderTop = BorderStyle.Thin;
                       columnStyle.BorderBottom = BorderStyle.Thin;
                       columnStyle.BorderLeft = BorderStyle.Thin;
                       columnStyle.BorderRight = BorderStyle.Thin;
                       //设置居中
                       columnStyle.Alignment = HorizontalAlignment.Center;
                       columnStyle.VerticalAlignment = VerticalAlignment.Center;
                       //设置字体样式和单元格背景
                       columnStyle.FillPattern = FillPattern.SolidForeground;
                       columnStyle.FillForegroundColor = 40;
                       for (int j = 0; j < data.Columns.Count; ++j)
                       {
                           row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
                           ICell cell = row.GetCell(j);
                           cell.CellStyle = columnStyle;
                           //设置列宽
                           sheet.SetColumnWidth(j, 20 * 256);
                       }
                       count = 1;
                   }

                   for (int i = 0; i < data.Rows.Count; ++i)
                   {
                       IRow row = sheet.CreateRow(count);
                       ICellStyle cellStyle = workbook.CreateCellStyle();
                       cellStyle.BorderBottom = BorderStyle.Thin;
                       cellStyle.BorderLeft = BorderStyle.Thin;
                       cellStyle.BorderRight = BorderStyle.Thin;
                       cellStyle.BorderTop = BorderStyle.Thin;
                       for (int j = 0; j < data.Columns.Count; ++j)
                       {
                           row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
                           ICell cell = row.GetCell(j);
                           cell.CellStyle = cellStyle;
                       }
                       count++;
                   }
                   workbook.Write(fs); //写入到excel

                   return count;
               }
               catch (Exception ex)
               {
                   MessageBoxInfor.ShowMessageBox("Exception：" + ex.Message);
                   return -1;
               }
           }
 				}

C#-IO-文件Copy

Copy()

string sourceFile = @"c:\temp\New Text Document.txt"; 
string destinationFile = @"c:\temp\test.txt"; 
bool isrewrite=true; // true=覆盖已存在的同名文件,false则反之
System.IO.File.Copy(sourcePath, targetPath, isrewrite); 

CopyTo()

string sourceFile = @"c:\temp\New Text Document.txt"; 
string destinationFile = @"c:\temp\test.txt"; 
FileInfo file = new FileInfo(sourceFile); 
if (file.Exists) 
{ 
    // true is overwrite 
    file.CopyTo(destinationFile, true); 
} 

文件流实现Copy

#region 拷贝操作
private void button3_Click(object sender, EventArgs e)
{
    OpenFileDialog ofd = new OpenFileDialog();
    DialogResult res = ofd.ShowDialog();
    if (res == DialogResult.OK) 
    {
        if (!string.IsNullOrEmpty(ofd.FileName))
        {
            //1.创建读入文件流对象
            FileStream streamRead = new FileStream(ofd.FileName, FileMode.Open);
            //2.创建1个字节数组，用于接收文件流对象读操作文件值
            byte[] data = new byte[1024 * 1024];//1M
            int length = 0;
            SaveFileDialog sfd = new SaveFileDialog();
            DialogResult sres = sfd.ShowDialog();
            if (sres == DialogResult.OK)
            {
                if (!string.IsNullOrEmpty(ofd.FileName))
                {
                    FileStream streamWrite = new FileStream(sfd.FileName, FileMode.Create);
                    do
                    {
                        //3.文件流读方法的参数1.data-文件流读出数据要存的地方，2. 0--从什么位置读，3. data.Length--1次读多少字节数据
                        //3.1 Read方法的返回值是一个int类型的，代表他真实读取 字节数据的长度，
                        length = streamRead.Read(data, 0, data.Length);//大文件读入时候，我们定义字节长度的可能会有限，如果文件超大，要接收文件流对象的Read()方法，会返回读入的实际长度
                        //加密 和解密
                        for (int i = 0; i < length; i++) 
                        {
                             data[i] = (byte)(255 - data[i]);
                        }
                            streamWrite.Write(data, 0, length);
                    } while (length == data.Length); //如果实际写入长度等于我们设定的长度，有两种情况1.文件正好是我们设定的长度2.文件超大只上传了截取的一部分
                }
            }
        }
    }
}
#endregion   

C#NPOI实现Excel文件复制

//需要读取的源文件
string filepath = @"C:\Users\Administrator\Desktop\测试.xlsx";
FileStream fs = new FileStream(filepath,FileMode.Open,FileAccess.Read);
IWorkbook wb = new XSSFWorkbook(fs);
fs.Close();
//创建新的文件
string outFilePath = @"C:\Users\Administrator\Desktop\输出测试.xlsx";          
FileStream outFs = new FileStream(outFilePath,FileMode.Create,FileAccess.Write);
wb.Write(outFs);         
outFs.Close();
wb.Close();

复制模板问价并写入内容

//报告生成
        private void uiSyCreateExcel_Click(object sender, EventArgs e)
        {
            try
            {
                FolderBrowserDialog folder = new FolderBrowserDialog();
                if (folder.ShowDialog() == DialogResult.OK)
                {
                    string ExportDir = folder.SelectedPath + "\\数据导出" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
                    //需要读取的源文件
                    string filepath = @"DataBase\Report.xlsx";
                    FileStream fs = new FileStream(filepath, FileMode.Open, FileAccess.Read);
                    IWorkbook wb = new XSSFWorkbook(fs);
                    fs.Close();
                    //创建新的文件
                    FileStream outFs = new FileStream(ExportDir, FileMode.Create, FileAccess.Write);
                    wb.Write(outFs);
                    outFs.Close();
                    wb.Close();
                    //写入文件
                    bool WriteSymbol = ExcelHelper.WriteDataTableToExcel(dt, ExportDir, false,dt.Rows.Count);
                    if (WriteSymbol==true)
                    {
                        MessageBox.Show("导出成功，文件存储在" + ExportDir + "。");
                    }
                    else
                    {
                        MessageBox.Show("导出失败，请检查导出文件是否处于打开状态或查看日志文件");
                    }
                }
            }
            catch (Exception ex)
            {
                FileOperate.WriteLog(ex);
            }
        }

输出带列名的DataTable数据表并调整Excel内容格式

/// <summary>
      /// 将DataTable数据导入到excel中
      /// </summary>
      /// <param name="data">要导入的数据</param>
      /// <param name="isColumnWritten">DataTable的列名是否要导入</param>
      /// <param name="sheetName">要导入的excel的sheet的名称</param>
      /// <param name="fileName">导出的文件途径</param>
      /// <returns>导入数据行数(包含列名那一行)</returns>
      public static int DataTableToExcel(DataTable data, string sheetName, string fileName, bool isColumnWritten = true)
      {
          IWorkbook workbook = null;
          using (FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite))
          {
              if (fileName.IndexOf(".xlsx") > 0) // 2007版本
              {
                  workbook = new XSSFWorkbook();
              }
              else if (fileName.IndexOf(".xls") > 0) // 2003版本
              {
                  workbook = new HSSFWorkbook();
              }
              if (workbook == null) { return -1; }

              try
              {
                  ISheet sheet = workbook.CreateSheet(sheetName);

                  int count = 0;
                  if (isColumnWritten) //写入DataTable的列名
                  {
                      IRow row = sheet.CreateRow(0);
                      ICellStyle columnStyle = workbook.CreateCellStyle();
                      //设置边框
                      columnStyle.BorderTop = BorderStyle.Thin;
                      columnStyle.BorderBottom = BorderStyle.Thin;
                      columnStyle.BorderLeft = BorderStyle.Thin;
                      columnStyle.BorderRight = BorderStyle.Thin;
                      //设置居中
                      columnStyle.Alignment = HorizontalAlignment.Center;
                      columnStyle.VerticalAlignment = VerticalAlignment.Center;
                      //设置字体样式和单元格背景
                      columnStyle.FillPattern = FillPattern.SolidForeground;
                      columnStyle.FillForegroundColor = 40;
                      for (int j = 0; j < data.Columns.Count; ++j)
                      {
                          row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
                          ICell cell = row.GetCell(j);
                          cell.CellStyle = columnStyle;
                          //设置列宽
                          sheet.SetColumnWidth(j, 20 * 256);
                      }
                      count = 1;
                  }

                  for (int i = 0; i < data.Rows.Count; ++i)
                  {
                      IRow row = sheet.CreateRow(count);
                      ICellStyle cellStyle = workbook.CreateCellStyle();
                      cellStyle.BorderBottom = BorderStyle.Thin;
                      cellStyle.BorderLeft = BorderStyle.Thin;
                      cellStyle.BorderRight = BorderStyle.Thin;
                      cellStyle.BorderTop = BorderStyle.Thin;
                      for (int j = 0; j < data.Columns.Count; ++j)
                      {
                          row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
                          ICell cell = row.GetCell(j);
                          cell.CellStyle = cellStyle;
                      }
                      count++;
                  }
                  workbook.Write(fs); //写入到excel

                  return count;
              }
              catch (Exception ex)
              {
                  MessageBoxInfor.ShowMessageBox("Exception：" + ex.Message);
                  return -1;
              }
          }
      }

Excel单元格中写入图片

private void ExcelHelper()
       {
           string ExportPath = "D:\\QuickTest\\test.xlsx";
           IWorkbook workbook = null;
           using (FileStream fs = new FileStream(ExportPath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
           {

               if (ExportPath.IndexOf(".xlsx") > 0) // 2007版本
               {
                   workbook = new XSSFWorkbook();
               }
               else if (ExportPath.IndexOf(".xls") > 0) // 2003版本
               {
                   workbook = new HSSFWorkbook();
               }
               if (workbook == null) { return; }

               try
               {
                   ISheet sheet = workbook.CreateSheet("PictureTest");//创建一个Sheet
                   sheet.SetColumnWidth(0, 18 * 256);//设置列宽
                   sheet.SetColumnWidth(1, 18 * 256);
                   IRow rowHeader = sheet.CreateRow(0);//创建表头行
                   rowHeader.CreateCell(0, CellType.String).SetCellValue("测试表头_1");
                   rowHeader.CreateCell(1, CellType.String).SetCellValue("测试表头_2");
                   IRow rowFirstRow = sheet.CreateRow(1);//创建内容第一行
                   rowFirstRow.Height = 80 * 40;//设置行高
                   rowFirstRow.CreateCell(0, CellType.String).SetCellValue("测试内容");
                   //将图片文件读入一个字符串
                   byte[] picByte = File.ReadAllBytes("D:\\BoiledYakult\\01壁纸图片\\backiee-246720.jpg");
                   int PictureIndex = workbook.AddPicture(picByte, PictureType.JPEG);

                   if (ExportPath.IndexOf(".xlsx") > 0) // 2007版本
                   {
                       IDrawing drawing = sheet.CreateDrawingPatriarch();
                       IClientAnchor anchor = drawing.CreateAnchor(0, 0, 0, 0, 1, 1, 2, 2);
                       IPicture pict = drawing.CreatePicture(anchor, PictureIndex);
                   }
                   else if (ExportPath.IndexOf(".xls") > 0) // 2003版本
                   {
                       HSSFPatriarch hSSFShapes = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
                       HSSFClientAnchor anchor = new HSSFClientAnchor(70, 10, 0, 0, 1, 1, 2, 2);//插入图片位置
                       HSSFPicture picture = (HSSFPicture)hSSFShapes.CreatePicture(anchor, PictureIndex);
                   }
                   workbook.Write(fs);
               }
               catch (Exception ex)
               {
                   MessageBox.Show(ex.ToString());
                   throw ex;
               }
           }
       }

C#将文件读取字符串