在C＃中删除excel单元格中的额外空格 [英] Remove an extra space in excel cell in C#

问题描述

我正在使用

 Microsoft.Office.Interop.Excel创建一个excel文件; 

并填写excel表使用数据库中的数据（SQLite）。



所有东西都运行正常，唯一的问题是单元格内的数据

就像是



1001 1002 1003 1004

1 6/15/2018 3:34:20 101< ---> < --->< --->< ---> - >在这里留出额外的空间。





任何人都可以找到造成这些问题的代码有什么问题？



我的尝试：

 public void ExportToExcel（）
 {
 DirectoryInfo di = System.IO.Directory.CreateDirectory（C：\\ProgramData \\\ththhSensorCalibrator）; 
 string logFilename = System.IO.Path.Combine（Environment.GetFolderPath（
 Environment.SpecialFolder.CommonApplicationData），di +\\databaseFile.db3）; 
 System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection（@Data source =+ logFilename）; 
 System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand（con）; 
 com.CommandText =Select * FROM Log; //从数据库表中选择所有行
 
 int i = 0; 
 int j = 0; 
 string data = null; 
 Excel.Application xlApp; 
 Excel.Workbook xlWorkBook; 
 Excel.Worksheet xlWorkSheet; 
 object misValue = System.Reflection.Missing.Value; 
 
 
 xlApp = new Excel.Application（）; 
 xlWorkBook = xlApp.Workbooks.Add（misValue）; 
 xlWorkSheet =（Excel.Worksheet）xlWorkBook.Worksheets.get_Item（1）; 
 
 
 
 SQLiteDataAdapter dscmd = new SQLiteDataAdapter（com.CommandText，con）; 
 DataSet ds = new DataSet（）; 
 dscmd.Fill（ds）; 
 
 
 
 for（i = 0; i< = ds.Tables [0] .Rows.Count  -  1; i ++）
 {
 for（j = 0; j< = ds.Tables [0] .Columns.Count  -  1; j ++）
 {
 data = ds.Tables [0] .Rows [i] .ItemArray [ J]的ToString（）; 
 
 xlWorkSheet.Cells [i + 1，j + 1] = data; 
 
} 
} 
 SaveFileDialog saveFileDialog1 = new SaveFileDialog（）; 
 
 saveFileDialog1.InitialDirectory = @Documents\; 
 
 saveFileDialog1.Title =保存文本文件; 
 
 // saveFileDialog1.CheckFileExists = true; 
 
 //saveFileDialog1.CheckPathExists = true; 
 
 saveFileDialog1.DefaultExt =txt; 
 
 saveFileDialog1.Filter =Excel文件（* .xls）| * .xls; 
 
 saveFileDialog1.FilterIndex = 2; 
 
 saveFileDialog1.RestoreDirectory = true; 
 string filename =; 
 if（saveFileDialog1.ShowDialog（）== DialogResult.OK）
 
 {
 
 filename = saveFileDialog1.FileName; 
 
} 
 xlWorkBook.SaveAs（filename，Excel.XlFileFormat.xlWorkbookNormal，misValue，misValue，misValue，misValue，Excel.XlSaveAsAccessMode.xlExclusive，misValue，misValue，misValue，misValue，misValue）; 
 
 //xlWorkBook.SaveCopyAs(filename）; 
 xlWorkBook.Close（true，misValue，misValue）; 
 xlApp.Quit（）; 
 
 releaseObject（xlWorkSheet）; 
 releaseObject（xlWorkBook）; 
 releaseObject（xlApp）; 
 
 MessageBox.Show（Excel file created）; 
 
 
 
 
 void releaseObject（object obj）
 {
 try 
 {
 System.Runtime.InteropServices .Marshal.ReleaseComObject（OBJ）; 
 obj = null; 
} 
 catch（Exception ex）
 {
 obj = null; 
 MessageBox.Show（Exception Occured while release object+ ex.ToString（））; 
} 
最后
 {
 GC.Collect（）; 
} 
} 
} 
 
} 
 

解决方案

< blockquote>试试：

 xlWorkSheet.Cells [i + 1，j + 1] = data.Trim（）; 

I am creating an excel file using

Microsoft.Office.Interop.Excel;

and filling up the excel sheet using the data from database(SQLite).

All things are working perfectly,only problem is that the data inside the cell
is like

1001 1002 1003 1004
1 6/15/2018 3:34:20 101 <---><---><---><---> -->Leaving the extra space here.


can anyone please found out what is the problem with code causing these?

What I have tried:

public void ExportToExcel()
      {
          DirectoryInfo di = System.IO.Directory.CreateDirectory("C:\\ProgramData\\tthhSensorCalibrator");
          string logFilename = System.IO.Path.Combine(Environment.GetFolderPath(
            Environment.SpecialFolder.CommonApplicationData), di + "\\databaseFile.db3");
          System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection(@"Data source="+ logFilename);
          System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con);
          com.CommandText = "Select * FROM Log";      // Select all rows from our database table

          int i = 0;
          int j = 0;
          string data = null;
          Excel.Application xlApp;
          Excel.Workbook xlWorkBook;
          Excel.Worksheet xlWorkSheet;
          object misValue = System.Reflection.Missing.Value;


          xlApp = new Excel.Application();
          xlWorkBook = xlApp.Workbooks.Add(misValue);
          xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);



          SQLiteDataAdapter dscmd = new SQLiteDataAdapter(com.CommandText, con);
          DataSet ds = new DataSet();
          dscmd.Fill(ds);



          for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
          {
              for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
              {
                  data = ds.Tables[0].Rows[i].ItemArray[j].ToString();

                  xlWorkSheet.Cells[i + 1, j + 1] = data;

              }
          }
          SaveFileDialog saveFileDialog1 = new SaveFileDialog();

          saveFileDialog1.InitialDirectory = @"Documents\";

          saveFileDialog1.Title = "Save text Files";

         // saveFileDialog1.CheckFileExists = true;

          //saveFileDialog1.CheckPathExists = true;

          saveFileDialog1.DefaultExt = "txt";

          saveFileDialog1.Filter = "Excel files (*.xls)|*.xls";

          saveFileDialog1.FilterIndex = 2;

          saveFileDialog1.RestoreDirectory = true;
          string filename="";
          if (saveFileDialog1.ShowDialog() == DialogResult.OK)

          {

              filename = saveFileDialog1.FileName;

          }
          xlWorkBook.SaveAs(filename, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);

          //xlWorkBook.SaveCopyAs(filename);
          xlWorkBook.Close(true, misValue, misValue);
          xlApp.Quit();

          releaseObject(xlWorkSheet);
          releaseObject(xlWorkBook);
          releaseObject(xlApp);

          MessageBox.Show("Excel file created");




          void releaseObject(object obj)
          {
              try
              {
                  System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                  obj = null;
              }
              catch (Exception ex)
              {
                  obj = null;
                  MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
              }
              finally
              {
                  GC.Collect();
              }
          }
      }

 }

解决方案

Try that:

xlWorkSheet.Cells[i + 1, j + 1] = data.Trim();

这篇关于在C＃中删除excel单元格中的额外空格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！