Growing Hacker 2022-11-09 22:03 采纳率: 94.4%
浏览 17
已结题

Winform 读取excel,生成脚本文件

用for循环加StingBuilder 读取excel生成字符串后写入streamwrite,字符串没有写入完整
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Text;

namespace ScriptHelper
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
StringBuilder sb = new StringBuilder();
public void button1_Click(object sender, EventArgs e)
{
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.InitialDirectory = @"C:";
openFileDialog.Filter = "Excel文件|.xlsx|所有文件|.*";
openFileDialog.RestoreDirectory = false;
if(openFileDialog.ShowDialog() == DialogResult.OK)
{
textBox1.Text = Path.GetFullPath(openFileDialog.FileName);
}

    }

    private void button2_Click(object sender, EventArgs e)
    {
        FolderBrowserDialog dialog = new FolderBrowserDialog();
        dialog.Description = "请选择文件夹";
        if (dialog.ShowDialog()==DialogResult.OK|| dialog.ShowDialog()==DialogResult.Yes)
        {
            SavePosition.Text = dialog.SelectedPath;
        }
    }

    private void button3_Click(object sender, EventArgs e)
    {
        using (FileStream stream = File.OpenRead(this.textBox1.Text))
        {
            XSSFWorkbook wb = new XSSFWorkbook(stream);
            sb.Append(
                "USE [" + this.DBName.Text + "]\r\nGO\r\n\r\n/****** Object:  Table [" + this.TableName.Text + "]" +
                "Script Date: 2022/11/8 19:00:16 ******/\r\nSET ANSI_NULLS ON\r\nGO" +
                "\r\n\r\nSET QUOTED_IDENTIFIER ON\r\nGO" +
                "\r\n\r\nCREATE TABLE [dbo].[" + this.TableName.Text + "](" +
                "\r\n\t[INDX] [int] IDENTITY(1,1) NOT NULL,"
                );
            XSSFSheet sheet = (XSSFSheet)wb.GetSheet(this.Sheet.Text);
            int RowCount = sheet.LastRowNum;
            int StartRow = int.Parse(this.StartRow.Text);
            for (int i = StartRow-1; i <= RowCount; i++)
            {
                sb.Append(
                  sheet.GetRow(i).GetCell(1).StringCellValue.Replace(" ","") + " " +
                  sheet.GetRow(i).GetCell(4).StringCellValue.Replace(" ", "") + ",\r\n\t") ;
            }


            sb.Append("[ORG_ID] [varchar](50) NOT NULL,\r\n\t" +
                "[CREATE_USER_D] [varchar](50) NOT NULL,\r\n\t" +
                "[CREATE_DATE] [datetime] NOT NULL,\r\n\t" +
                "[LAST_UPDATE_USER_ID] [decimal](10, 0) NULL,\r\n\t" +
                "[LAST_UPDATE_DATE] [datetime] NULL,\r\n\t" +
                "[DELETED] [nvarchar](1) NULL,\r\n\t" +
                "[RESERVER0] [nvarchar](max) NULL,\r\n\t" +
                "[RESERVER1] [nvarchar](max) NULL,\r\n\t" +
                "[RESERVER2] [nvarchar](max) NULL,\r\n\t" +
                "[RESERVER3] [nvarchar](max) NULL,\r\n\t" +
                "[RESERVER4] [nvarchar](max) NULL,\r\n\t" +
                "[RESERVER5] [nvarchar](max) NULL,\r\n\t" +
                "[RESERVER6] [nvarchar](max) NULL,\r\n\t" +
                "[RESERVER7] [nvarchar](max) NULL,\r\n\t" +
                "[RESERVER8] [nvarchar](max) NULL,\r\n\t" +
                "[RESERVER9] [nvarchar](max) NULL,\r\n\t" +
                "[RESERVER10] [nvarchar](max) NULL,\r\n\t" +
                "[RESERVER11] [nvarchar](max) NULL,\r\n\t" +
                "[RESERVER12] [nvarchar](max) NULL,\r\n\t" +
                "[RESERVER13] [nvarchar](max) NULL,\r\n\t" +
                "[RESERVER14] [nvarchar](max) NULL,\r\n\t" +
                "[RESERVER15] [nvarchar](max) NULL,\r\n\t" +
                "[RESERVER16] [nvarchar](max) NULL,\r\n\t" +
                "[RESERVER17] [nvarchar](max) NULL,\r\n\t" +
                "[RESERVER18] [nvarchar](max) NULL,\r\n\t" +
                "[RESERVER19] [nvarchar](max) NULL,\r\n" +
                " CONSTRAINT [PK_BMS_VGM_HP] PRIMARY KEY CLUSTERED \r\n(\r\n\t" +
                "[INDX] ASC\r\n)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]\r\n)" +
                " ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]\r\nGO\r\n\r\n");

            for (int i = StartRow - 1; i <= RowCount; i++)
            {
                sb.Append("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'" + sheet.GetRow(i).GetCell(2).StringCellValue + "' , @level0type=N'SCHEMA'," +
                    "@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'" + this.TableName.Text + "', @level2type=N'COLUMN',@level2name=N'" + sheet.GetRow(i).GetCell(1).StringCellValue + "'" +
                    "\r\nGO\r\n");
            }

        }
        try
        {
            if (!File.Exists(this.SavePosition.Text + @"\" + this.TableName.Text + ".sql"))
            {
                FileStream file = new FileStream(this.SavePosition.Text + @"\" + this.TableName.Text + ".sql", FileMode.Create, FileAccess.Write);
                StreamWriter sw = new StreamWriter(file);
                sw.Write(sb);
                sw.Flush();
                sw.Close();
                file.Close();
                MessageBox.Show("创建成功");

            }
            else
            {
                FileStream file = new FileStream(this.SavePosition.Text + @"\" + this.TableName.Text + DateTime.Now.Second.ToString() + ".sql", FileMode.Create, FileAccess.Write);
                StreamWriter sw = new StreamWriter(file);
                sw.Write(sb);
                sw.Flush();
                sw.Close();
                file.Close();
                MessageBox.Show("创建成功");
            }
        }
        catch (Exception)
        {
            MessageBox.Show("创建失败");
            throw;
        }
    }
}

}

img

第一个for循环写入完整,第二个中断了,但没有任何报错

  • 写回答

1条回答 默认 最新

  • Growing Hacker 2022-11-10 11:08
    关注

    StreamWriter 写入问题

    using (var file = System.IO.File.Create(this.SavePosition.Text + @"" + this.TableName.Text + ".sql"))
    {

                        using (StreamWriter sw = new StreamWriter(file))
                        {
                            sw.Write(sb);
                            sw.Flush();
                        }
    
                    }
    

    替换掉
    FileStream file = new FileStream(this.SavePosition.Text + @"" + this.TableName.Text + ".sql", FileMode.Create, FileAccess.Write);
    StreamWriter sw = new StreamWriter(file);
    sw.Write(sb);
    sw.Flush();
    sw.Close();
    file.Close();

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

问题事件

  • 系统已结题 11月18日
  • 已采纳回答 11月10日
  • 修改了问题 11月9日
  • 修改了问题 11月9日
  • 展开全部

悬赏问题

  • ¥88 用uniapp写一个多端的程序,用到高德地图,用高德的JSAPI吗?
  • ¥20 关于#c++#的问题:水果店管理系统
  • ¥30 dbLinq最新版linq sqlite
  • ¥20 对D盘进行分盘之前没有将visual studio2022卸载掉,现在该如何下载回来
  • ¥15 完成虚拟机环境配置,还有安装kettle
  • ¥15 2024年全国大学生数据分析大赛A题:直播带货与电商产品的大数据分析 问题5. 请设计一份优惠券的投放策略,需要考虑优惠券的数量、优惠券的金额、投放时间段和投放商品种类等因素。求具体的python代码
  • ¥15 有人会搭建生鲜配送自营+平台的管理系统吗
  • ¥15 用matlab写代码
  • ¥30 motoradmin系统的多对多配置
  • ¥15 求组态王串口自定义通信配置方法或代码?