2 queenia Queenia 于 2015.05.26 14:46 提问

C#对Excel单元格写入数据,无论是Update、Insert,都只写入第一行,为什么?

string cell[5] = {"B","C","D","E","F"};

For( i=1;i<=40;i++)
For(j=1;j<=5;j++)
{cellname= cell[j]+string.format("%d",i+1);
string data = "22.5";
Excel.Write("sheet1",cellname,(i+1),data);}

Excel操作函数为:
write(string sheetname, string cellname, int lines, string writedata)
{
OleDbCommand command= null;
if(this,conOleDB != null)
{
try
{
this.conOleDB.Open();
command = new OleDbCommand(string.Format("UPDATE [{0}${1}:{2}] Set F{3}=\"{4}\"", new object[] { sheetName, cell Name, cellName, Lines, value2Write }), this.conOleDB);
command.ExecuteNonQuery();
}
catch(Exception exception)
{
.......
}
}

如果想实现在Excel表指定区域(2-42行,(B-F)列写入数据,应该如何更改)??

2个回答

qq_28521221
qq_28521221   2015.05.27 11:55

试试用NPOI来操作EXCEL吧,可能会好做一点.

doris_d
doris_d   2015.05.28 13:15
using (ExcelHelper excel = new ExcelHelper(@"F:\1.xlsx")) 
{
        excel.SetCurrentWorkSheet(1);
        for (int i = 2; i <= 42; i++)
        {
                excel.SetCellValue("B", i, "B" + i.ToString());
                excel.SetCellValue("C", i, "C" + i.ToString());
                excel.SetCellValue("D", i, "D" + i.ToString());
                excel.SetCellValue("E", i, "E" + i.ToString());
                excel.SetCellValue("F", i, "F" + i.ToString());
        }
        excel.Save();                
}

---

public class ExcelHelper : IDisposable
{
        #region 字段

        private object missing = Missing.Value;
        private Application application;
        private Workbook workbook;
        private Worksheet worksheet;

        #endregion

        #region 构造函数

        private ExcelHelper() { }

        public ExcelHelper(string excelFile)
        {
                this.application = new Application();
                this.application.Visible = false;
                this.application.DisplayAlerts = false;
                this.workbook = this.OpenExcel(excelFile);
        }

        #endregion

        #region 公共方法

        /// <summary>
        /// 保存文件
        /// </summary>
        public void Save()
        {
                this.workbook.Save();
        }

        /// <summary>
        /// 设置当前工作表
        /// </summary>
        /// <param name="index"></param>
        public void SetCurrentWorkSheet(int index)
        {
                this.worksheet = this.workbook.Sheets[index];
        }

        /// <summary>
        /// 向指定的单元格中填充值
        /// </summary>
        /// <param name="columnIndex">列索引,例如:"A"</param>
        /// <param name="rowIndex">行索引,从1开始</param>
        /// <param name="value">要填充的值</param>
        public void SetCellValue(string columnIndex, int rowIndex, string value)
        {
                this.worksheet.Cells[rowIndex, columnIndex] = value;
        }

        #endregion

        #region 私有方法

        /// <summary>
        /// 打开excel文档
        /// </summary>
        /// <param name="wordFile"></param>
        /// <returns></returns>
        private Workbook OpenExcel(string excelFile)
        {
                object readOnly = false;
                object addToMru = false;

                return this.application.Workbooks.Open(
                        excelFile, missing, readOnly, missing, missing, missing,
                        missing, missing, missing, missing, missing, missing, addToMru, missing, missing);
        }

        #endregion

        #region IDisposable成员

        public void Dispose()
        {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(this.worksheet);
                this.worksheet = null;
                System.Runtime.InteropServices.Marshal.ReleaseComObject(this.workbook);
                this.workbook = null;
                if (this.application != null)
                {
                        this.application.Quit();
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(this.application);
                        this.application = null;
                }
        }

        #endregion
}
Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!