C#Excel文件导入导出_c# excel导入-CSDN博客

本文链接：https://blog.csdn.net/weixin_44399428/article/details/143112035

1.导入Excel文件写入数据库

//文件上传功能按钮
<td style="width: 50%;">
   <asp:FileUpload ID="fuUpImg" runat="server" Font-Size="16px" Font-Bold="True" ForeColor="red" />
</td>

//点击导入按钮
<td>
     <asp:Button ID="btnUploadData" width="100px" runat="server" Text="导入" CssClass="button" BackColor="#9E9E9E" OnClick="btnUploadData_OnClick"/>
</td>

//后端文档校验
 protected void btnUploadData_OnClick(object sender, EventArgs e)
        {
            //读取文件名
            var sFileName = fuUpImg.FileName;
            //文件名不存在或为空
            if (string.IsNullOrEmpty(sFileName) || sFileName == "")
            {
                window.RadAlert("请选择要上传的文件！", 200, 200, "提示", null);
                return;
            }
            //拆分文件名，获取文件属性
            var fileExtension = Path.GetExtension(fuUpImg.FileName);
            //文件类型表单
            var exactName = new List<string> { ".xls", ".xlsx" };
            //判断是否为表单形式文件名，非此形式提示非Excel文件
            if (!exactName.Contains(fileExtension))
            {
                window.RadAlert("只能选择Excel格式的文件，请重新选择！", 200, 200, "提示", null);
                return;
            }
            //上传的暂存路径
            var filePath = HttpContext.Current.Server.MapPath("/ImportExport/Download/" + DateTime.Now.ToString("yyyyMMddHHmmss") + "/");
            if (!Directory.Exists(filePath))
            {
                Directory.CreateDirectory(filePath);
            }
            if (File.Exists(filePath + sFileName))
            {
                File.Delete(filePath + sFileName);
            }
            fuUpImg.SaveAs(filePath + sFileName);
            var result = getdata.SetPointData(filePath + sFileName);
            if (result.code == 0)
            {
                FirePointManagement.Rebind();
                window.RadAlert(result.msg, 200, 200, "提示", null);
            }
            if (result.code == 500)
            {
                FirePointManagement.Rebind();
                window.RadAlert(result.msg, 200, 200, "提示", null);
            }
            
        }

// 后端读取Excel文件方法
public List<FireFacilitiesModel> ReadExcelData(string strFileName)
        {
            List<FireFacilitiesModel> vitdata = new List<FireFacilitiesModel>();
            var wb = new Workbook(strFileName);
            //取得第一个工作薄
            Worksheet ws = wb.Worksheets[0];
            //取得总记录行数(包括标题列)
            int rowsint = ws.Cells.MaxDataRow + 1; //得到行数
            var cells = ws.Cells;
            for (int i = 1; i < rowsint; i++)
            {
                FireFacilitiesModel newPointData = new FireFacilitiesModel();
                var cellsA = cells[i, 0];
                if (cellsA.Value != null)
                {
                    newPointData.FactoryArea = cellsA.Value.ToString();
                }
                var cellsB = cells[i, 1];
                if (cellsB.Value != null)
                {
                    newPointData.Workshop = cellsB.Value.ToString();
                }
                var cellsC = cells[i, 2];
                if (cellsC.Value != null)
                {
                    newPointData.Point = cellsC.Value.ToString();
                }
                var cellsD = cells[i, 3];
                if (cellsD.Value != null)
                {
                    newPointData.DeviceType = cellsD.Value.ToString();
                }
                var cellsE = cells[i, 4];
                if (cellsE.Value != null)
                {
                    newPointData.Notes = cellsE.Value.ToString();
                }
                var cellsF = cells[i, 5];
                if (cellsF.Value != null)
                {
                    newPointData.ManagerUserID = cellsF.Value.ToString();
                }
                vitdata.Add(newPointData);
            }
            return vitdata;
        }

//将读取到的Excel表格数据写入数据库
public ResultMassage WriteExcelData(List<FireFacilitiesModel> item)
        {
            try
            {
                foreach (var itemdetail in item)
                {
                    var sql = string.Format(
                        "INSERT INTO FirePointManagement (FactoryArea, WorkShop, Point, DeviceType, Notes, ManagerUserID, CreateDate, Deleted)\r\nVALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')"
                        , itemdetail.FactoryArea, itemdetail.Workshop, itemdetail.Point, itemdetail.DeviceType, itemdetail.Notes, itemdetail.ManagerUserID, DateTime.Now, 0);
                    _erpData.ExecuteCommand(sql);
                    _erpData.SubmitChanges();
                }
                return new ResultMassage
                {
                    code = 0,
                    msg = "导入成功",
                    data = ""
                };
            }
            catch (Exception ex)
            {
                return new ResultMassage
                {
                    code = 500,
                    msg = ex.Message,
                    data = ""
                };
            }
          
        }

2.导出Excel文件

//导出常用格式
//C#后端 定义一个button
<asp:Button ID="btnExportYear" runat="server" Text=" 导 出 " Width="80px" CssClass="button" OnClick="btnExportYear_OnClick" />

//后端方法
   protected void btnExportYear_OnClick(object sender, EventArgs e)
        {
            if (txtStartMonthsChoose.SelectedDate != null && txtEndMonthsChoose.SelectedDate != null &&
                txtStartMonthsChoose.SelectedDate.Value > txtEndMonthsChoose.SelectedDate.Value)
            {
                RadAjaxManager1.Alert("导出结果开始时间不能晚于结束时间！");
            }
            var timestart = txtStartMonthsChoose.SelectedDate == null ? "0" : (txtStartMonthsChoose.SelectedDate.Value.Year * 100 + txtStartMonthsChoose.SelectedDate.Value.Month).ToString();
            var timeend = txtEndMonthsChoose.SelectedDate == null ? "0" : (txtEndMonthsChoose.SelectedDate.Value.Year * 100 + txtEndMonthsChoose.SelectedDate.Value.Month).ToString();
            string error = vietnamese.ExportMonthHoursData(Convert.ToInt32(timestart), Convert.ToInt32(timeend), txtName.Text.Trim(), txtUserId.Text.Trim(), txtselDepartment.SelectedItem == null ? "" : txtselDepartment.SelectedItem.Text.Trim());
            Response.Write("<script language=javascript>alert('" + error + "');</script>");
        }

//.BLL层
string error = "";
            if (data.Count > 0)
            {
            //获取文件路径
            var path = HttpContext.Current.Server.MapPath("../HomeMadeSchedule/Reports/CapacityData.xls");
            var workbook = new Workbook(path);
            int sheeti = 0;

            var worksheet = workbook.Worksheets[sheeti];
            var cells = worksheet.Cells;
            int row = 2;
            foreach
            {
              数据写入
              row++
            }
            
            System.IO.MemoryStream ms = workbook.SaveToStream();//生成数据流 
            byte[] bt = ms.ToArray();

            string fileName = "名称" + DateTime.Now.ToString("yyyyMMdd") + ".xls";//客户端保存的文件名 

            //以字符流的形式下载文件
            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";

            //通知浏览器下载文件而不是打开
            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
            HttpContext.Current.Response.BinaryWrite(bt);

            HttpContext.Current.Response.Flush();
            HttpContext.Current.Response.End();
            }
            else
            {
                error = "无可下载数据！";
            }
            return error;

3.生成二维码

//生成二维码
 public string CreateQRCode(int sid,string factoryarea, string workshop,string point, string devicetypes,string username,string userid)
        {
            var qrCodeEncoder = new QRCodeEncoder
            {
                QRCodeEncodeMode = QRCodeEncoder.ENCODE_MODE.BYTE,
                QRCodeScale = 3,
                QRCodeVersion = 0,
                QRCodeErrorCorrect = QRCodeEncoder.ERROR_CORRECTION.M
            };
            // 确保所有参数都有值
            factoryarea = string.Empty;
            workshop = string.Empty;
            point = string.Empty;
            devicetypes = string.Empty;
            username = username ?? string.Empty;
            // 将 username 转换为 UTF-8 编码的 URL 格式
            string encodedUsername = HttpUtility.UrlEncode(username);
            userid = string.Empty;

            // 构造包含多个参数的 URL
            var qrContent = $"http://wechatapp.intco.com.cn:8090/hrs/firepoint/FirePoint?" +
                            $"sid={sid}" +
                            $"&username={encodedUsername}";
            //var qrContent = $"http://localhost:3100/hrs/firepoint/FirePoint?" +
            //                $"sid={sid}" +
            //                $"&username={encodedUsername}";
            // 生成二维码
            var image = qrCodeEncoder.Encode(qrContent);
            var md5 = MD5.Create();
            var hashBytes = md5.ComputeHash(Encoding.UTF8.GetBytes(qrContent));
            var hash = BitConverter.ToString(hashBytes).Replace("-", "").Substring(0, 8); // 获取前8位哈希值
            var filename = DateTime.Now.ToString("yyyyMMddHHmmssfff") + "_" + hash + ".jpg";
            var filepath = HttpContext.Current.Server.MapPath(@"~\Upload");
            if (!Directory.Exists(filepath))
            {
                Directory.CreateDirectory(filepath);
            }
            var fullFilePath = filepath + "\\" + filename;
            var fs = new FileStream(fullFilePath, FileMode.OpenOrCreate, FileAccess.Write);
            image.Save(fs, System.Drawing.Imaging.ImageFormat.Jpeg);
            fs.Close();
            image.Dispose();
            var rootDir = HttpContext.Current.Server.MapPath(HttpContext.Current.Request.ApplicationPath).Trim('\\');
            var url = fullFilePath.Replace(rootDir, ""); //转换成相对路径
            url = url.Replace(@"\", @"/");
            return url;
        }