This project is read-only.

Create xlsx file from Template, but the file has some errors.

Mar 29, 2013 at 10:39 AM
I generate files from xlsx template.In the process, I add a lot of data to the file.But some of the m are right,some of them have errors.The Excel shows the following message:Repair parts: XML error /xl/worksheets/sheet1.xml. Load error. Row 528, column 0. I have checked all the templates, there is no error in them.
_IWorkbook = new XSSFWorkbook(_ItemFileForExcel);
    XSSFSheet _XSSFSheet = null;
    string _SqlFileName = string.Empty;
    foreach (string _ItemPathForSql in _SqlList)
    {
        //取得当前Sheet的sql
        FileInfo _FileInfoForSql = new FileInfo(_ItemPathForSql);
        _SqlFileName = _FileInfoForSql.Name;

        //执行SQL
        textBox1.Text += "\r\n当前正在执行:" + _SqlFileName + "   " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff");
        sql = ReadSqlContext(_ItemPathForSql);
        objConnection.Open();
        SqlCommand cmd = new SqlCommand(sql, objConnection);
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = sql;
        SqlDataReader DataReader = cmd.ExecuteReader();
        System.Data.DataTable dt = ConvertDataReaderToDataTable(DataReader);
        cmd.Dispose();
        objConnection.Close();
        if (dt.Rows.Count == 0)
        {
            dt.Rows.Add(dt.NewRow());
        }
        
        _XSSFSheet = (XSSFSheet)_IWorkbook.GetSheet(_FileInfoForSql.Name.Replace(".sql", ""));
        if (_XSSFSheet == null)
        {
            _XSSFSheet = (XSSFSheet)_IWorkbook.CreateSheet(_FileInfoForSql.Name.Replace(".sql", ""));
        }
        //取得当前Sheet的数据验证
        List<XSSFDataValidation> _XSSFDataValidationList = _XSSFSheet.GetDataValidations();

        //写入数据
        FillDataFromTable(_XSSFSheet, "A9", dt);
        //设置Tab颜色
        _XSSFSheet.SetTabColor(NPOI.HSSF.Util.HSSFColor.RED.index);

        dt.Clear();
        dt.Dispose();
    }
    //写入文件
    FileStream _FileStream = File.Create(outputPath + "\\" + listProject.Text + "\\" + fileName);
    using (_FileStream)
    {
        _IWorkbook.Write(_FileStream);
        if (_FileStream.CanWrite)
        {
            _FileStream.Flush();
        }
    }