C# excel导入导出

来源：互联网发布：初一英语点读软件编辑：程序博客网时间：2024/05/16 14:35

注意：服务器需要安装office软件

1、Excel导入
提示：未在本地计算机上注册“Microsoft.Jet.OLEDB.4.0”提供程序。
未在本地计算机上注册“Microsoft.ACE.OLEDB.12.0”提供程序。
解决方法如下：
打开IIS管理器 -> 应用程序管理池 -> 选择相应程序的应用池 ->
右键 -> 应用程序池默认设置 -> 常规选项卡 ->
启用32位应用程序设置成 True

上传excel文件代码

public ActionResult EditAction(FormCollection collection){    try    {        string filePath = string.Empty;        string fileType = string.Empty;        HttpPostedFileBase postedFile = Request.Files["fileImage"];        #region 上传文件        fileType = Path.GetExtension(postedFile.FileName).ToLower();//文件的后缀名(小写)        FileUploadResult result = FileHelper.CreatePostedFileToImage(postedFile, @"upload\excel\");        if (result != null && result.Code == (int)CodeEnum.Success)        {            filePath = result.Path;        }        filePath = ConstantParamInfo.PhysicalApplicationPath + filePath;        ImportSql(filePath, fileType);        #endregion    }    catch (Exception ex)    {    }    return RedirectToAction("Index", "CityManage");}

读取excel文件数据，插入数据库数据代码

        public bool ImportSql(string excelPath, string fileType)        {            string strCon = string.Empty;            if (fileType == ".xls")                strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelPath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";            else                strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelPath + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";            System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon);            string strCom = "SELECT * FROM [Sheet1$]";            string strComNew = "SELECT * FROM [Sheet2$]";            DataTable dt;            DataTable dtNew;            try            {                #region city table                System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, strCon);                DataSet ds = new DataSet();                myCommand.Fill(ds, "[Sheet1$]");                dt = ds.Tables[0];                if (dt != null)                {                    if (cityBiz.DeleteAll())                    {                        for (int i = 0; i < dt.Rows.Count; i++)                        {                            var model = new City                            {                                ID = Convert.ToInt32(dt.Rows[i][0].ToString()),                                ProvincialID = 0,                                Title = dt.Rows[i][1].ToString(),                                State = "A"                            };                            cityBiz.Add(model);                        }                    }                }                #endregion                #region Hospital table                System.Data.OleDb.OleDbDataAdapter myCommandNew = new System.Data.OleDb.OleDbDataAdapter(strComNew, strCon);                DataSet dsNew = new DataSet();                myCommandNew.Fill(dsNew, "[Sheet2$]");                dtNew = dsNew.Tables[0];                if (dtNew != null)                {                    if (hospitalBiz.DeleteAll())                    {                        for (int i = 0; i < dtNew.Rows.Count; i++)                        {                            var model = new Hospital                            {                                CityId = Convert.ToInt32(dtNew.Rows[i][0].ToString()),                                Title = dtNew.Rows[i][1].ToString(),                                CreateBy = 0                            };                            hospitalBiz.Add(model);                        }                    }                }                #endregion                return true;            }            catch (Exception ex)            {                logger.Error("ImportSql()" + ex.Message);            }            return false;        }

2、Excel导出

        public ActionResult ExportCSV()        {            try            {                string Transactions = Request.Form["item"];                List<long> arr = new List<long>();                string[] strtemp = Transactions.Split(',');                foreach (string strs in strtemp)                {                    arr.Add(long.Parse(strs));                }                //List<PaymentMaster> PaymentList = PaymentBusi.GetTransactionDetails(this.CurrentAccount.SiteID, arr.ToArray());                List<PaymentMaster> PaymentList = new List<PaymentMaster>();                MemoryStream stream = new MemoryStream();                StreamWriter writer = new StreamWriter(stream);                writer.WriteLine("AccountName,Date,TransactionID,ItemDesc,Amount,TransactionTotal");                writer.Flush();                for (int i = 0; i < PaymentList.Count; i++)                {                    string Summary = string.Empty;                    string PaymentIDF = string.Empty;                    string PaymentID = string.Empty;                    if (PaymentList[i].PaymentItems.Count > 0)                        Summary = DelQuota(PaymentList[i].PaymentItems[0].Summary);                    if (PaymentList[i].PaymentID > 0)                        PaymentIDF = PaymentList[i].PaymentID.ToString();                    writer.WriteLine(string.Format("{0},{1},{2},{3},{4},{5}",                       DelQuota(PaymentList[i].AccountName),                       Convert.ToDateTime(PaymentList[i].CreateDate, GetMDDYYFormat()).ToShortDateString(),                       PaymentList[i].PaymentID,                       Summary,                       "$" + PaymentList[i].PaymentItems[0].Amount.ToString(),                       ""                      ));                    if (PaymentList[i].PaymentItems.Count > 0)                    {                        for (int j = 0; j < PaymentList[i].PaymentItems.Count; j++)                        {                            if (PaymentList[i].PaymentItems[j].PaymentID > 0)                                PaymentID = PaymentList[i].PaymentItems[j].PaymentID.ToString();                            if (j != 0)                            {                                writer.WriteLine(string.Format("{0},{1},{2},{3},{4},{5}",                                                              "",                                                              "",                                                              PaymentID,                                                              DelQuota(PaymentList[i].PaymentItems[j].Summary),                                                              "$" + PaymentList[i].PaymentItems[j].Amount.ToString(),                                                              ""                                                          ));                            }                        }                        writer.WriteLine(string.Format("{0},{1},{2},{3},{4},{5}",                               "",                               "",                               "",                               "",                               "",                               "$" + PaymentList[i].Amount.ToString()                        ));                    }                    writer.Flush();                }                stream.Position = 0;                byte[] buffer = new byte[stream.Length];                stream.Read(buffer, 0, buffer.Length);                stream.Close();                return File(buffer, "text/plain", "PaymentInvoice.csv");            }            catch (Exception)            {                return View();            }        }        public string DelQuota(string str)        {            string result = str;            string[] strQuota = { "~", "!", "@", "#", "$", "%", "^", "&", "*", "(", ")", "`", ";", "'", ",", ".", "/", ":", "/,", "<", ">", "?" };            for (int i = 0; i < strQuota.Length; i++)            {                if (result.IndexOf(strQuota[i]) > -1)                    result = result.Replace(strQuota[i], "");            }            return result;        }        public System.Globalization.DateTimeFormatInfo GetMDDYYFormat()        {            System.Globalization.DateTimeFormatInfo dtFormat = new System.Globalization.DateTimeFormatInfo();            dtFormat.ShortDatePattern = "M/dd/yy";            return dtFormat;        }    public class PaymentMaster    {        public string PaymentMethod { get; set; }        public long PaymentID { get; set; }        public long AccountID { get; set; }        public long ContactID { get; set; }        public int Status { get; set; }        public long SiteID { get; set; }        public long ClientID { get; set; }        public string LoginName { get; set; }        public string FirstName { get; set; }        public string LastName { get; set; }        public string Amount { get; set; }        public int DetailCount { get; set; }        public string Description { get; set; }        public System.DateTime ModifyDate { get; set; }        public string CreateDate { get; set; }        public List<long> RentalIDs { get; set; }        public List<PaymentDetail> PaymentItems { get; set; }        public List<string> UnitNumbers { get; set; }        public string AccountName { get; set; }    }    public class PaymentDetail    {        public long DetailID { get; set; }        public int DetailType { get; set; }        public long PaymentID { get; set; }        public long AccountID { get; set; }        public long ContactID { get; set; }        public long RentalID { get; set; }        public int Status { get; set; }        public long SiteID { get; set; }        public string Summary { get; set; }        public string UnitNumber { get; set; }        public string UnitSize { get; set; }        public int UnitCount { get; set; }        public string PaidThruDate { get; set; }        //Public Property Dimensions As String        public string Amount { get; set; }        public string TransactionNumber { get; set; }        public string Description { get; set; }        public System.DateTime ModifyDate { get; set; }        public System.DateTime CreateDate { get; set; }    }

0 0