C# excel数据存储

记录一些常用方法

项目中需要先引用NPOI

 

 

 1. 新建一个表格，在可设置新建或者追加(只能在同一sheet上操作)

        /// <summary>
        /// 将DataTable数据导入到excel中
        /// </summary>
        /// <param name="fileName">文件名</param>
        /// <param name="data">要导入的数据</param>
        /// <param name="sheetName">要导入的excel的sheet的名称</param>
        /// <param name="blnAppled">是否是追加模式</param>
        /// <param name="isColumnWritten">DataTable的列名是否要导入</param>
        /// <returns></returns>
        public static bool DataTableToExcel(string fileName, DataTable data, bool blnAppled, string sheetName, bool isColumnWritten)
        {
            int i = 0;
            int j = 0;
            int count = 0;
            ISheet sheet = null;
            IWorkbook workbook = null;
            using (FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite))
            {
                if (!blnAppled)
                {
                    if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                        workbook = new XSSFWorkbook();
                    else if (fileName.IndexOf(".xls") > 0) // 2003版本
                        workbook = new HSSFWorkbook();
                }
                else
                {
                    if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                        workbook = new XSSFWorkbook(fs);
                    else if (fileName.IndexOf(".xls") > 0) // 2003版本
                        workbook = new HSSFWorkbook(fs);
                }

                try
                {
                    if (!blnAppled && !string.IsNullOrEmpty(sheetName))
                    {
                        if (workbook != null)
                        {
                            sheet = workbook.CreateSheet(sheetName);
                        }
                        else
                        {
                            return false;
                        }
                    }
                    else
                    {
                        sheet = workbook.GetSheetAt(0);
                    }

                    if (!blnAppled)
                    {
                        if (isColumnWritten == true) //写入DataTable的列名
                        {
                            IRow row = sheet.CreateRow(0);
                            for (j = 0; j < data.Columns.Count; ++j)
                            {
                                row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
                            }
                            count = 1;
                        }
                        else
                        {
                            count = 0;
                        }
                    }

                    count = sheet.LastRowNum + 1;

                    for (i = 0; i < data.Rows.Count; ++i)
                    {
                        IRow row = sheet.CreateRow(count);
                        for (j = 0; j < data.Columns.Count; ++j)
                        {
                            row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
                        }
                        ++count;
                    }

                }
                catch (Exception ex)
                {
                    Console.WriteLine("Exception: " + ex.Message);
                    return false;
                }
            }

            FileStream outFs = new FileStream(fileName, FileMode.Open);
            workbook.Write(outFs);
            outFs.Close();
            return true;
        }

 

2. 新建表格，在不同sheet中追加

        public static IWorkbook workbook2 ;
        static int[] countArr = new int[8];

        public static void NewWorkbook()
        {
            workbook2 = new XSSFWorkbook();
        }

        public static bool DataTableToExcel(DataTable dt, string path, string sheetName, int sheetIndex,bool blnAppled)
        {
            bool result = false;
            FileStream fs = null;
            IRow row = null;
            ISheet sheet = null;
            ICell cell = null;
            try
            {
                double doubV = 0;
                int rowCount = dt.Rows.Count;//行数
                int columnCount = dt.Columns.Count;//列数

                if (!blnAppled)
                {
                    countArr[sheetIndex] = 0;
                    sheet = workbook2.CreateSheet(sheetName);//创建一个Sheet的表
                    row = sheet.CreateRow(0);
                    for (int c = 0; c < columnCount; c++)
                    {
                        cell = row.CreateCell(c);
                        cell.SetCellValue(dt.Columns[c].ColumnName);
                        sheet.AutoSizeColumn(c);
                    }
                    countArr[sheetIndex] ++;
                }
                else
                {
                    sheet = workbook2.GetSheetAt(sheetIndex);
                }

                //设置每行每列的单元格,
                for (int i = 0; i < rowCount; i++)
                {
                    sheet = workbook2.GetSheetAt(sheetIndex);
                    row = sheet.CreateRow(countArr[sheetIndex]);//excel第二行开始写入数据
                    countArr[sheetIndex]++;
                    for (int j = 0; j < columnCount; j++)
                    {
                        //sheet.AutoSizeColumn(j);
                        cell = row.CreateCell(j);
                        if (double.TryParse(dt.Rows[i][j].ToString(), out doubV))
                        {
                            row.CreateCell(j).SetCellValue(doubV);
                            continue;
                        }
                        cell.SetCellValue(dt.Rows[i][j].ToString());
                    }
                }

                using (fs = File.OpenWrite(path))
                {
                    workbook2.Write(fs);//向打开的这个xls文件中写入数据
                    result = true;
                }
                fs.Close();
                return result;
            }
            catch (Exception ex)
            {
                if (fs != null)
                {
                    fs.Close();
                }
                Console.WriteLine(ex.ToString());
                return false;
            }
        }