Step 6 Import the data in excel to database

a.       Create excel app object and open the uploaded file

            Microsoft.Office.Interop.Excel.Application xlsApp 
=
 
new
 ApplicationClass();

            Workbook wb 
=
 xlsApp.Workbooks.Open(_filePath,Type.Missing,Type.Missing, Type.Missing,Type.Missing,Type.Missing,Type.Missing, Type.Missing,Type.Missing,Type.Missing,Type.Missing, Type.Missing,Type.Missing,Type.Missing,Type.Missing);

            wb.Unprotect(TemplatePassword);

 

b.      Save the upload file as a temporary file. Then close uploaded file and open the temporary file

           
string
 tempFileName 
=
 _filePath.ToLower().Replace(
"
.xls
"
,
"
_Temp.xls
"
);      

            wb.Unprotect(TemplatePassword);

            wb.SaveCopyAs(tempFileName);

 

c.       Verify the data in template again in web application

            ReadDataSource((Worksheet)wb.Worksheets[DataSourceSheet], 
out
 branchID, 
out
 planDate, 
out
 startDate, 
out
 endDate, 
out
 iChecked, 
out
 templatetype);

           
if
(
!
(iChecked 
==
 
1
))

            

{

                returnVal = "Please verify the data before upload to the server！";

                  throw new Exception(returnVal);

            }


            
if
(templatetype.ToUpper() 
!=
 _templatetype.ToUpper())

            

{

                  returnVal = "The version is not corrected, please verify the document and uploaded again";

                  throw new Exception(returnVal);

            }


 ……


private
 
void
 ReadDataSource(Worksheet ws, 
out
 
string
 branchID, 
out
 
string
 planDate, 
out
 DateTime startDate,
out
 DateTime endDate, 
out
 
int
 iChecked, 
out
 
string
 templatetype)

      

{

            string check = ws.get_Range("A1", System.Type.Missing).Text.ToString();

            branchID = ws.get_Range("A2", System.Type.Missing).Text.ToString();

            string sDate = ws.get_Range("A4", System.Type.Missing).Text.ToString();

            string eDate = ws.get_Range("A5", System.Type.Missing).Text.ToString();

            planDate = ws.get_Range("A6", System.Type.Missing).Text.ToString();

            templatetype = ws.get_Range("A7", System.Type.Missing).Text.ToString();

            startDate = DateTime.Parse(sDate);

            endDate = DateTime.Parse(eDate);

            try

            {

                  iChecked = Convert.ToInt16(check);

            }

            catch

            {

                  iChecked = 0;

            }

      }
    

            

d.      Read the data in the worksheet

            
string
 territoryList 
=
 ws.get_Range(
"
B
"
+
 i.ToString(), System.Type.Missing).Text.ToString();

            
string
 territoryIDList 
=
 ws.get_Range(
"
AB
"
 
+
 i.ToString(), System.Type.Missing).Text.ToString();

            
string
 category 
=
 ws.get_Range(
"
E
"
 
+
 i.ToString(), System.Type.Missing).Text.ToString();

            
string
 categoryID 
=
 ws.get_Range(
"
AE
"
 
+
 i.ToString(), System.Type.Missing).Text.ToString();

e.      Change the database based on data in excel sheet

            SqlHelper.ExecuteNonQuery(connnectionString, CommandType.Text, "insert into … ");                 

Summary: Objects used in this sample

a.       Excel.Application

                                                               i.      Application.Workbooks.Open

                                                             ii.      Quit

b.      Excel.Workbook

                                                               i.      SaveCopyAs

                                                             ii.      Unprotect

                                                            iii.      Worksheets

                                                           iv.      Protect

                                                             v.      .Names.Add

                                                           vi.      Close

c.       Excel.WorkSheet

                                                               i.      Unprotect

                                                             ii.      Protect

                                                            iii.      .Hyperlinks.Add

d.      Range

                                                               i.      Value2

                                                             ii.      Text

e.      Cell, Cells

转载于:https://www.cnblogs.com/lyrix/archive/2007/12/05/976015.html

原文链接：https://blog.csdn.net/weixin_30342827/article/details/95260500

本站声明:网站内容来源于网络,如有侵权,请联系我们,我们将及时处理。