1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

NPOI を使って、データをEXCELに出力する 不具合解決

Posted at

環境

win10 visual studio 2019 wpf
NPOI ver2.4.1

不具合内容

Excelを開く時、以下の内容が表示される。
一部の内容に問題が見つかりました。可能な限り内容を回復しますか?ブックの発行元が信頼できる場合は、[はい]をクリックしてください。

原因

FileStream fs = new FileStream("excel.xls");
から作ったファイルはテキストファイルであり、新規作成は問題なく、既存のファイルに上書き保存の時は、不具合が発生する。

解決

MemoryStream を使うと、不具合が解除できる。

元のコード

        /// DataTable を Excel に 出力する
        /// <param name="data">元のデータ</param>
        /// <param name="sheetName">シート名</param>
        /// <param name="isColumnWritten">列名も出力するか</param>
        /// <returns>出力したデータの行数</returns>
        public int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten)
        {
            int i = 0;
            int j = 0;
            int count = 0;
            ISheet sheet = null;

            fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);

            if (fileName.IndexOf(".xlsx") > 0) // 2007 Ver
            {
                workbook = new XSSFWorkbook();
            }
            else if (fileName.IndexOf(".xls") > 0) // 2003 Ver
            {
                workbook = new HSSFWorkbook();
            }

            try
            {
                if (workbook != null)
                {
                    sheet = workbook.CreateSheet(sheetName);
                }
                else
                {
                    return -1;
                }

                if (isColumnWritten)
                {
                    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;
                }

                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++;
                }

                workbook.Write(fs);
                
                fs.Close();
                workbook.Close();

                return count;
            }
            catch (Exception ex)
            {
                if (fs != null)
                {
                    fs.Close();
                }

                if (workbook != null)
                {
                    workbook.Close();
                }

                Console.WriteLine("Exception: " + ex.Message);
                return -1;
            }
        }

修正後

        /// DataTable を Excel に 出力する
        /// <param name="data">元のデータ</param>
        /// <param name="sheetName">シート名</param>
        /// <param name="isColumnWritten">列名も出力するか</param>
        /// <returns>出力したデータの行数</returns>
        public int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten)
        {
            int i = 0;
            int j = 0;
            int count = 0;
            ISheet sheet = null;          

            if (fileName.IndexOf(".xlsx") > 0) // 2007 Ver
            {
                workbook = new XSSFWorkbook();
            }
            else if (fileName.IndexOf(".xls") > 0) // 2003 Ver
            {
                workbook = new HSSFWorkbook();
            }

            try
            {
                if (workbook != null)
                {
                    sheet = workbook.CreateSheet(sheetName);
                }
                else
                {
                    return -1;
                }

                if (isColumnWritten)
                {
                    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;
                }

                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++;
                }

                using (MemoryStream ms = new MemoryStream())
                {
                    workbook.Write(ms);

                    using (fs = new FileStream(fileName, FileMode.Create, FileAccess.ReadWrite))
                    {
                        byte[] data1 = ms.ToArray();
                        fs.Write(data1, 0, data1.Length);
                        fs.Flush();
                    }

                    workbook = null; 
                }


                return count;
            }
            catch (Exception ex)
            {
                if (fs != null)
                {
                    fs.Close();
                }

                if (workbook != null)
                {
                    workbook.Close();
                }

                Console.WriteLine("Exception: " + ex.Message);
                return -1;
            }
        }
1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?