Question

c#中如何把Excel数据存入数组？

Excel.Application exl = new Excel.Application();
Excel.Workbooks workbooks = exl.Workbooks;

sfile = Application.StartupPath + "\\DataBase" + "\\"+filename+".xls";
try
{
Excel.Workbook eWorkBook = exl.Workbooks.Open(sfile, true, "Zquick2017");//这里会报错 哎 真的不知道怎么写这个open函数 Excel打开要密码 密码为Zquick2017
}
catch (Exception a)
{

MessageBox.Show(a.Message);
}
Excel.Worksheet eWorkSheet (Excel.Worksheet)exl.Workbooks.Application.ActiveSheet;
for (int i = 0; i < 286; i++)
{
mod1.a0mdb[i]=(int)eWorkSheet.Cells[i+2,1];
//eWorkSheet.Cells(i + 2, 1) = mod1.a0mdb[i];
//eWorkSheet.Cells[i+2,1] = mod1.a0mdb[i];
}
哎 我写的代码错误一大堆 谁能帮我解答一下怎么实现吗 就是把已知的Excel里的数据存入一个数组
上面少了一个等号 看起来怪怪的 就是for循环上一句

Excel.Worksheet eWorkSheet = (Excel.Worksheet)exl.Workbooks.Application.ActiveSheet;

Answer 1

本方案需要引用免费版的spire.xls.dll

using Spire.Xls;
using System.Data;

namespace ReadExcel
{
    class Program
    {
        static void Main(string[] args)
        {
            //创建Workbook对象
            Workbook wb = new Workbook();

            //输入Excel的密码
            wb.OpenPassword = "密码";

            //加载Excel文档
            wb.LoadFromFile("Encrypted.xlsx");

            //获取第一个工作表
            Worksheet sheet = wb.Worksheets[0];

            //将工作表第一行第一列至第三行第四列区域内的数据导出为DataTable
            DataTable datatable = sheet.ExportDataTable(1, 1, 3, 4, false);

            //将DataTable数据转换为二维数组
            string[,] array = DataTableToArray(datatable);
        }

        //将DataTable转为二维数组
        public static string[,] DataTableToArray(DataTable dt)
        {
            int i = 0;
            int rowsCount = dt.Rows.Count;
            int colsCount = dt.Columns.Count;
            string[,] arrReturn = new string[rowsCount, colsCount];
            foreach (System.Data.DataRow row in dt.Rows)
            {
                int j = 0;
                foreach (System.Data.DataColumn column in dt.Columns)
                {
                    arrReturn[i, j] = row[column.ColumnName].ToString();
                    j = j + 1;
                }
                i = i + 1;
            }
            return arrReturn;
        }
    }
}

Answer 2

先把Excel文件读到Dataset里 然后去一个一个去读取！
以下是我的代码！
private void ImportData()
{
string name = "test.xls";
string type = name.Substring(name.LastIndexOf(".") + 1);

if (type == "xls" || type == "XLS")
FileUpload1.SaveAs(File_Url + name);
else
Response.Write(" <script language=javascript> alert( '打开的不是Excel格式文件! '); </script> ");

string mystring = "Provider=Microsoft.Jet.OleDb.4.0;data Source='" + File_Url + "test.xls';Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";

OleDbConnection cnnxls = new OleDbConnection(mystring);
cnnxls.Open();

//获取Excel文件的sheet 表名

DataTable schemaTable = cnnxls.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
string tableName = schemaTable.Rows[0]["Table_Name"].ToString(); // 获取第一个表名

OleDbDataAdapter myDa = new OleDbDataAdapter("select * from [" + tableName + "$] ", cnnxls);
DataSet myDs = new DataSet();
myDa.Fill(myDs, "[" + tableName + "$]");
string addStr = null;
if (myDs.Tables[0].Rows.Count > 0)
{
try
{
for (int i = 0; i < myDs.Tables[0].Rows.Count; i++)
{
#region 用三个名称判断术语是否存在，要是存在就UPDATE下，不存在就插入数据（必须三种名字都一样）

DataSet countDs = CountTerm(myDs.Tables[0].Rows[i].ItemArray[0].ToString(),
myDs.Tables[0].Rows[i].ItemArray[1].ToString(),
myDs.Tables[0].Rows[i].ItemArray[2].ToString());

int l = countDs.Tables[0].Rows.Count;
if (countDs.Tables[0].Rows.Count == 0)
{
// 数据插入
addStr = AddTerm(ModuleID,
myDs.Tables[0].Rows[i].ItemArray[0].ToString(),
myDs.Tables[0].Rows[i].ItemArray[1].ToString(),
myDs.Tables[0].Rows[i].ItemArray[2].ToString(),
myDs.Tables[0].Rows[i].ItemArray[3].ToString(),
DateTime.Now,
PortalSettings.CurrentUser.Identity.Email);
}

#endregion
}
}
catch
{
}
finally
{
if (addStr == null)
addStr = "术语导入成功！";
cnnxls.Close();
deleteFiles(File_Url);
Response.Write(" <script language=javascript> alert( '" + addStr + "'); </script> ");
}
}
}