添加ＳＥＭ数据入库功能

13693261870

2024-01-04 86f75cbd690003c913e25f41429c1212955d1445

添加ＳＥＭ数据入库功能

 JavaCode/DLL/NPOI.dll

Binary files differ

 JavaCode/DLL/SQLite.Interop.dll

Binary files differ

 JavaCode/DLL/System.Data.SQLite.dll

Binary files differ

 JavaCode/FrmMain.Designer.cs

@@ -32,6 +32,7 @@
            this.btnMybatis = new System.Windows.Forms.Button();
            this.btnQGis = new System.Windows.Forms.Button();
            this.btnTerrain = new System.Windows.Forms.Button();
            this.btnSEM = new System.Windows.Forms.Button();
            this.SuspendLayout();
            // 
            // btnSys
@@ -78,11 +79,23 @@
            this.btnTerrain.UseVisualStyleBackColor = true;
            this.btnTerrain.Click += new System.EventHandler(this.btnTerrain_Click);
            // 
            // btnSEM
            // 
            this.btnSEM.Font = new System.Drawing.Font("宋体", 12F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134)));
            this.btnSEM.Location = new System.Drawing.Point(143, 252);
            this.btnSEM.Name = "btnSEM";
            this.btnSEM.Size = new System.Drawing.Size(183, 25);
            this.btnSEM.TabIndex = 17;
            this.btnSEM.Text = "S E M æ•° æ® å¤„ ç†";
            this.btnSEM.UseVisualStyleBackColor = true;
            this.btnSEM.Click += new System.EventHandler(this.btnSEM_Click);
            // 
            // FrmMain
            // 
            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 12F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.ClientSize = new System.Drawing.Size(484, 261);
            this.ClientSize = new System.Drawing.Size(468, 319);
            this.Controls.Add(this.btnSEM);
            this.Controls.Add(this.btnTerrain);
            this.Controls.Add(this.btnQGis);
            this.Controls.Add(this.btnMybatis);
@@ -100,5 +113,6 @@
        private System.Windows.Forms.Button btnMybatis;
        private System.Windows.Forms.Button btnQGis;
        private System.Windows.Forms.Button btnTerrain;
        private System.Windows.Forms.Button btnSEM;
    }
}

 JavaCode/FrmMain.cs

@@ -39,5 +39,11 @@
        {
            TerrainHelper.RemoveTerrain();
        }

        private void btnSEM_Click(object sender, EventArgs e)
        {
            FrmSEM sem = new FrmSEM();
            sem.Show();
        }
    }
}

 JavaCode/FrmSEM.Designer.cs

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,72 @@
namespace JavaCode
{
    partial class FrmSEM
    {
        /// <summary>
        /// Required designer variable.
        /// </summary>
        private System.ComponentModel.IContainer components = null;

        /// <summary>
        /// Clean up any resources being used.
        /// </summary>
        /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
        protected override void Dispose(bool disposing)
        {
            if (disposing && (components != null))
            {
                components.Dispose();
            }
            base.Dispose(disposing);
        }

        #region Windows Form Designer generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
            this.txtExcel = new System.Windows.Forms.TextBox();
            this.btnCalc = new System.Windows.Forms.Button();
            this.SuspendLayout();
            // 
            // txtExcel
            // 
            this.txtExcel.Location = new System.Drawing.Point(18, 29);
            this.txtExcel.Name = "txtExcel";
            this.txtExcel.Size = new System.Drawing.Size(581, 21);
            this.txtExcel.TabIndex = 0;
            this.txtExcel.Text = "D:\\SEM\\撒点坐标.xls";
            // 
            // btnCalc
            // 
            this.btnCalc.Location = new System.Drawing.Point(618, 27);
            this.btnCalc.Name = "btnCalc";
            this.btnCalc.Size = new System.Drawing.Size(75, 23);
            this.btnCalc.TabIndex = 1;
            this.btnCalc.Text = "计 ç®—";
            this.btnCalc.UseVisualStyleBackColor = true;
            this.btnCalc.Click += new System.EventHandler(this.btnCalc_Click);
            // 
            // FrmSEM
            // 
            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 12F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.ClientSize = new System.Drawing.Size(712, 75);
            this.Controls.Add(this.btnCalc);
            this.Controls.Add(this.txtExcel);
            this.Name = "FrmSEM";
            this.Text = "FrmSEM";
            this.ResumeLayout(false);
            this.PerformLayout();

        }

        #endregion

        private System.Windows.Forms.TextBox txtExcel;
        private System.Windows.Forms.Button btnCalc;
    }
}

 JavaCode/FrmSEM.cs

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,83 @@
using JavaCode.cs;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace JavaCode
{
    public partial class FrmSEM : Form
    {
        public FrmSEM()
        {
            InitializeComponent();
        }

        private void btnCalc_Click(object sender, EventArgs e)
        {
            try
            {
                string xls = this.txtExcel.Text.Trim();
                string path = Path.GetDirectoryName(xls);
                if (string.IsNullOrWhiteSpace(xls) || !File.Exists(xls))
                {
                    MessageBox.Show("Excel文件路径为空或文件不存在！");
                    return;
                }

                DataTable dt = ReadExcel.ReadXls(xls, 0);
                if (null == dt || dt.Rows.Count == 0)
                {
                    MessageBox.Show("Excel内容为空！");
                    return;
                }

                List<Coord> list = ModelHandler.FillModel<Coord>(dt);
                if (null == list || list.Count == 0)
                {
                    MessageBox.Show("读取Excel内容为空！");
                    return;
                }

                int count = 0;
                foreach (Coord c in list)
                {
                    string db = Path.Combine(path, c.Name + ".sem");
                    if (!File.Exists(db)) continue;

                    SQLiteHelper.ConnectionString = string.Format("Data Source={0};Integrated Security=True;Max Pool Size=64", db);

                    string sql = string.Format("insert into POSITION (id, x, y, height) values ((select count(*)+1 from POSITION), {0}, {1}, {2})", c.X, c.Y, c.Z);
                    int rows = SQLiteHelper.ExecuteNonQuery(sql, null);
                    if (rows > 0)
                    {
                        count++;
                    }
                }

                MessageBox.Show("共更新了 " + count + " ä¸ª.sem文件！");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace);
            }
        }
    }

    public class Coord
    {
        public string Name { set; get; }

        public string X { set; get; }

        public string Y { set; get; }

        public string Z { set; get; }
    }
}

 JavaCode/FrmSEM.resx

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,120 @@
<?xml version="1.0" encoding="utf-8"?>
<root>
  <!-- 
    Microsoft ResX Schema 
    
    Version 2.0
    
    The primary goals of this format is to allow a simple XML format 
    that is mostly human readable. The generation and parsing of the 
    various data types are done through the TypeConverter classes 
    associated with the data types.
    
    Example:
    
    ... ado.net/XML headers & schema ...
    <resheader name="resmimetype">text/microsoft-resx</resheader>
    <resheader name="version">2.0</resheader>
    <resheader name="reader">System.Resources.ResXResourceReader, System.Windows.Forms, ...</resheader>
    <resheader name="writer">System.Resources.ResXResourceWriter, System.Windows.Forms, ...</resheader>
    <data name="Name1"><value>this is my long string</value><comment>this is a comment</comment></data>
    <data name="Color1" type="System.Drawing.Color, System.Drawing">Blue</data>
    <data name="Bitmap1" mimetype="application/x-microsoft.net.object.binary.base64">
        <value>[base64 mime encoded serialized .NET Framework object]</value>
    </data>
    <data name="Icon1" type="System.Drawing.Icon, System.Drawing" mimetype="application/x-microsoft.net.object.bytearray.base64">
        <value>[base64 mime encoded string representing a byte array form of the .NET Framework object]</value>
        <comment>This is a comment</comment>
    </data>
                
    There are any number of "resheader" rows that contain simple 
    name/value pairs.
    
    Each data row contains a name, and value. The row also contains a 
    type or mimetype. Type corresponds to a .NET class that support 
    text/value conversion through the TypeConverter architecture. 
    Classes that don't support this are serialized and stored with the 
    mimetype set.
    
    The mimetype is used for serialized objects, and tells the 
    ResXResourceReader how to depersist the object. This is currently not 
    extensible. For a given mimetype the value must be set accordingly:
    
    Note - application/x-microsoft.net.object.binary.base64 is the format 
    that the ResXResourceWriter will generate, however the reader can 
    read any of the formats listed below.
    
    mimetype: application/x-microsoft.net.object.binary.base64
    value   : The object must be serialized with 
            : System.Runtime.Serialization.Formatters.Binary.BinaryFormatter
            : and then encoded with base64 encoding.
    
    mimetype: application/x-microsoft.net.object.soap.base64
    value   : The object must be serialized with 
            : System.Runtime.Serialization.Formatters.Soap.SoapFormatter
            : and then encoded with base64 encoding.

    mimetype: application/x-microsoft.net.object.bytearray.base64
    value   : The object must be serialized into a byte array 
            : using a System.ComponentModel.TypeConverter
            : and then encoded with base64 encoding.
    -->
  <xsd:schema id="root" xmlns="" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
    <xsd:import namespace="http://www.w3.org/XML/1998/namespace" />
    <xsd:element name="root" msdata:IsDataSet="true">
      <xsd:complexType>
        <xsd:choice maxOccurs="unbounded">
          <xsd:element name="metadata">
            <xsd:complexType>
              <xsd:sequence>
                <xsd:element name="value" type="xsd:string" minOccurs="0" />
              </xsd:sequence>
              <xsd:attribute name="name" use="required" type="xsd:string" />
              <xsd:attribute name="type" type="xsd:string" />
              <xsd:attribute name="mimetype" type="xsd:string" />
              <xsd:attribute ref="xml:space" />
            </xsd:complexType>
          </xsd:element>
          <xsd:element name="assembly">
            <xsd:complexType>
              <xsd:attribute name="alias" type="xsd:string" />
              <xsd:attribute name="name" type="xsd:string" />
            </xsd:complexType>
          </xsd:element>
          <xsd:element name="data">
            <xsd:complexType>
              <xsd:sequence>
                <xsd:element name="value" type="xsd:string" minOccurs="0" msdata:Ordinal="1" />
                <xsd:element name="comment" type="xsd:string" minOccurs="0" msdata:Ordinal="2" />
              </xsd:sequence>
              <xsd:attribute name="name" type="xsd:string" use="required" msdata:Ordinal="1" />
              <xsd:attribute name="type" type="xsd:string" msdata:Ordinal="3" />
              <xsd:attribute name="mimetype" type="xsd:string" msdata:Ordinal="4" />
              <xsd:attribute ref="xml:space" />
            </xsd:complexType>
          </xsd:element>
          <xsd:element name="resheader">
            <xsd:complexType>
              <xsd:sequence>
                <xsd:element name="value" type="xsd:string" minOccurs="0" msdata:Ordinal="1" />
              </xsd:sequence>
              <xsd:attribute name="name" type="xsd:string" use="required" />
            </xsd:complexType>
          </xsd:element>
        </xsd:choice>
      </xsd:complexType>
    </xsd:element>
  </xsd:schema>
  <resheader name="resmimetype">
    <value>text/microsoft-resx</value>
  </resheader>
  <resheader name="version">
    <value>2.0</value>
  </resheader>
  <resheader name="reader">
    <value>System.Resources.ResXResourceReader, System.Windows.Forms, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</value>
  </resheader>
  <resheader name="writer">
    <value>System.Resources.ResXResourceWriter, System.Windows.Forms, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</value>
  </resheader>
</root>

 JavaCode/JavaCode.csproj

@@ -61,9 +61,17 @@
      <SpecificVersion>False</SpecificVersion>
      <HintPath>DLL\Npgsql.dll</HintPath>
    </Reference>
    <Reference Include="NPOI, Version=2.4.1.0, Culture=neutral, PublicKeyToken=0df73ec7942b34e1, processorArchitecture=MSIL">
      <SpecificVersion>False</SpecificVersion>
      <HintPath>DLL\NPOI.dll</HintPath>
    </Reference>
    <Reference Include="System" />
    <Reference Include="System.configuration" />
    <Reference Include="System.Core" />
    <Reference Include="System.Data.SQLite, Version=1.0.94.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139, processorArchitecture=MSIL">
      <SpecificVersion>False</SpecificVersion>
      <HintPath>DLL\System.Data.SQLite.dll</HintPath>
    </Reference>
    <Reference Include="System.Xml.Linq" />
    <Reference Include="System.Data.DataSetExtensions" />
    <Reference Include="Microsoft.CSharp" />
@@ -77,6 +85,8 @@
    <Compile Include="cs\ModelHandler.cs" />
    <Compile Include="cs\PostgreHelper.cs" />
    <Compile Include="cs\QGisHelper.cs" />
    <Compile Include="cs\ReadExcel.cs" />
    <Compile Include="cs\SQLiteHelper.cs" />
    <Compile Include="cs\TabInfo.cs" />
    <Compile Include="cs\TerrainHelper.cs" />
    <Compile Include="FrmMain.cs">
@@ -84,6 +94,12 @@
    </Compile>
    <Compile Include="FrmMain.Designer.cs">
      <DependentUpon>FrmMain.cs</DependentUpon>
    </Compile>
    <Compile Include="FrmSEM.cs">
      <SubType>Form</SubType>
    </Compile>
    <Compile Include="FrmSEM.Designer.cs">
      <DependentUpon>FrmSEM.cs</DependentUpon>
    </Compile>
    <Compile Include="FrmSys.cs">
      <SubType>Form</SubType>
@@ -102,6 +118,9 @@
    <EmbeddedResource Include="FrmMain.resx">
      <DependentUpon>FrmMain.cs</DependentUpon>
      <SubType>Designer</SubType>
    </EmbeddedResource>
    <EmbeddedResource Include="FrmSEM.resx">
      <DependentUpon>FrmSEM.cs</DependentUpon>
    </EmbeddedResource>
    <EmbeddedResource Include="FrmSys.resx">
      <DependentUpon>FrmSys.cs</DependentUpon>
@@ -150,6 +169,9 @@
    </EmbeddedResource>
    <Content Include="DLL\Mono.Security.dll" />
    <Content Include="DLL\Npgsql.dll" />
    <Content Include="DLL\NPOI.dll" />
    <Content Include="DLL\SQLite.Interop.dll" />
    <Content Include="DLL\System.Data.SQLite.dll" />
    <Content Include="SysTemplate\pg.sql" />
    <EmbeddedResource Include="SysTemplate\Controller.java">
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>

 JavaCode/cs/ReadExcel.cs

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,113 @@
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;

namespace JavaCode.cs
{
    /// <summary>
    /// è¯»å–Excelç±»
    /// </summary>
    public class ReadExcel
    {
        private string intName = typeof(int).Name;

        private string doubleName = typeof(double).Name;

        private string stringName = typeof(string).Name;

        /// <summary>
        /// è¯»å–Excel
        /// </summary>
        public static DataTable ReadXls(string file, int index = 0)
        {
            if (!File.Exists(file)) return null;

            string ext = Path.GetExtension(file).ToLower();
            using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
            {
                //IWorkbook wk = ext == ".xls" ? new HSSFWorkbook(fs) : new XSSFWorkbook();
                IWorkbook wk = new HSSFWorkbook(fs);
                ISheet sheet = wk.GetSheetAt(index);
                if (sheet == null)
                {
                    return null;
                }

                return SheetToDataTable(sheet);
            }
        }

        /// <summary>
        /// Sheet转换为DataTable
        /// </summary>
        public static DataTable SheetToDataTable(ISheet sheet)
        {
            IRow firstRow = sheet.GetRow(0);
            int cellCount = firstRow.LastCellNum;
            int rowCount = sheet.LastRowNum;

            DataTable dt = CreateDataTable(cellCount, sheet.GetRow(sheet.FirstRowNum));
            //for (int i = sheet.FirstRowNum; i <= rowCount; i++)
            for (int i = sheet.FirstRowNum + 1; i <= rowCount; i++)
            {
                IRow row = sheet.GetRow(i);
                if (row == null)
                {
                    continue;
                }

                DataRow dtRow = dt.NewRow();
                for (int j = 0; j < cellCount; j++)
                {
                    ICell cell = row.GetCell(j);
                    dtRow[j] = cell == null ? string.Empty : cell.ToString();
                }

                dt.Rows.Add(dtRow);
            }

            return dt;
        }

        /// <summary>
        /// åˆ›å»ºDataTable
        /// </summary>
        public static DataTable CreateDataTable(int columns, IRow row)
        {
            DataTable dt = new DataTable();
            for (int i = 0; i < columns; i++)
            {
                //DataColumn dc = new DataColumn("Col_" + i, typeof(string));
                string colName = null == row || null == row.GetCell(i) ? "Col_" + i : row.GetCell(i).ToString();
                DataColumn dc = new DataColumn(colName, typeof(string));
                dt.Columns.Add(dc);
            }

            return dt;
        }

        // æŸ¥æ‰¾åˆ—位置
        private int FindColumn(DataRow firstRow, int columns, string name)
        {
            for (int i = 0, c = columns; i < c; i++)
            {
                string colName = firstRow[i] == null ? null : firstRow[i].ToString().Trim();
                if (string.IsNullOrEmpty(colName))
                {
                    break;
                }

                if (name == colName)
                {
                    return i;
                }
            }

            return -1;
        }
    }
}

 JavaCode/cs/SQLiteHelper.cs

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,201 @@
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Web;

namespace JavaCode.cs
{
    public class SQLiteHelper
    {
        private static string _connStr = null;

        private static bool _isLoadDriver = false;

        /// <summary>
        /// ConnectionString样例：Datasource=Test.db3;Pooling=true;FailIfMissing=false
        /// </summary>
        public static string ConnectionString
        {
            get
            {
                if (string.IsNullOrEmpty(_connStr))
                {
                    //_connStr = string.Format("Data Source={0};Version={1};", Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Resources\\XiAn.sqlite"), 3);
                    _connStr = string.Format("Data Source={0};Integrated Security=True;Max Pool Size=64", Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "App_Data\\js.db"));
                }

                return _connStr;
            }
            set
            {
                _connStr = value;
            }
        }

        private static void LoadDrivers(SQLiteConnection conn)
        {
            if (!_isLoadDriver)
            {
                string spatialitePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Resources\\sqlite");
                Environment.SetEnvironmentVariable("PATH", spatialitePath + ";" + Environment.GetEnvironmentVariable("PATH"));

                _isLoadDriver = true;
            }

            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }

            conn.LoadExtension("mod_spatialite.dll");
        }

        /// <summary> 
        /// æŸ¥è¯¢æ•°æ®åº“中的所有数据类型信息。
        /// </summary> 
        public DataTable GetSchema()
        {
            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                return conn.GetSchema("TABLES");
            }
        }

        private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, params SQLiteParameter[] parameters)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            if (parameters != null && parameters.Length > 0)
            {
                cmd.Parameters.AddRange(parameters);
            }
        }

        public static DataSet ExecuteQuery(string cmdText, params SQLiteParameter[] parameters)
        {
            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand(cmdText, conn))
                {
                    PrepareCommand(cmd, conn, parameters);

                    DataSet ds = new DataSet();
                    using (SQLiteDataAdapter da = new SQLiteDataAdapter(cmd))
                    {
                        da.Fill(ds);
                    }

                    return ds;
                }
            }
        }

        public static DataTable GetDataTable(string cmdText, params SQLiteParameter[] parameters)
        {
            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
            {
                LoadDrivers(conn);

                using (SQLiteCommand cmd = new SQLiteCommand(cmdText, conn))
                {
                    PrepareCommand(cmd, conn, parameters);

                    DataTable dt = new DataTable();
                    using (SQLiteDataAdapter da = new SQLiteDataAdapter(cmd))
                    {
                        da.Fill(dt);
                    }

                    return dt;
                }
            }
        }

        public static int ExecuteNonQuery(string cmdText, params SQLiteParameter[] parameters)
        {
            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand(cmdText, conn))
                {
                    PrepareCommand(cmd, conn, parameters);

                    return cmd.ExecuteNonQuery();
                }
            }
        }

        public static SQLiteDataReader ExecuteReader(string cmdText, params SQLiteParameter[] parameters)
        {
            SQLiteConnection conn = new SQLiteConnection(ConnectionString);
            using (SQLiteCommand cmd = new SQLiteCommand(cmdText, conn))
            {
                PrepareCommand(cmd, conn, parameters);

                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
        }

        public static object ExecuteScalar(string cmdText, params SQLiteParameter[] parameters)
        {
            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand(cmdText, conn))
                {
                    PrepareCommand(cmd, conn, parameters);

                    return cmd.ExecuteScalar();
                }
            }
        }

        /// <summary>
        /// æ‰¹é‡å¤„理数据操作语句。
        /// </summary>
        public void ExecuteNonQueryBatch(List<KeyValuePair<string, SQLiteParameter[]>> list)
        {
            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                using (SQLiteTransaction tran = conn.BeginTransaction())
                {
                    using (SQLiteCommand cmd = new SQLiteCommand(conn))
                    {
                        try
                        {
                            foreach (var item in list)
                            {
                                cmd.CommandText = item.Key;
                                if (item.Value != null)
                                {
                                    cmd.Parameters.AddRange(item.Value);
                                }
                                cmd.ExecuteNonQuery();
                            }
                            tran.Commit();
                        }
                        catch (Exception ex)
                        {
                            tran.Rollback();
                            throw ex;
                        }
                    }
                }
            }
        }
    }
}