Excel读取成sql插入语句

2016-12-17 09:02:12来源:作者:人点击

最近做一个安卓app,需要将别人提供的excel表导入到本地,由于数据是一次性定好的,不会有变化,用app直接读取excel太麻烦,就写了个winform小程序,读取Excel文件生成导入的sql语句,代码比较简单,但是比较使用,分享出来有需要的可以看看

using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Windows.Forms;using System.IO;using NPOI.HSSF.UserModel;using NPOI.SS.UserModel;using NPOI.HPSF;namespace ExcelToSqlTool{    public partial class Form1 : Form    {        public Form1()        {            InitializeComponent();        }        HSSFWorkbook hssfworkbook;                private void button1_Click(object sender, EventArgs e)        {            if (textBox2.Text.Trim() == "") {                MessageBox.Show("请输入生成sql的表名称!");                textBox2.Focus();                return;            }             openFileDialog1.ShowDialog();            string filePath =  openFileDialog1.FileName;            if (filePath.Trim() == "") {                return;            }            if (!File.Exists(filePath)) {                MessageBox.Show("文件不存在!");                return;            }             label1.Text = "选中文件:" + filePath;            InitializeWorkbook(filePath);            DataTable dt = ConvertToDataTable();            if (dt.Rows.Count < 2) {                MessageBox.Show("Excel表中至少需要有两条数据!");                return;            }            if (dt.Select(dt.Columns[dt.Columns.Count - 1].ColumnName + " is null ").Length == dt.Rows.Count) {                dt.Columns.RemoveAt(dt.Columns.Count-1);            }            textBox1.Text = dataTableToSql(dt);        }        private string dataTableToSql(DataTable dt) {            StringBuilder sb = new StringBuilder();              for (int i =1;i < dt.Rows.Count;i++) {                DataRow row = dt.Rows[i];                sb.Append("insert into ");                sb.Append(textBox2.Text);                sb.Append("(");                 string columns = "";                string values = "";                foreach (DataColumn column in dt.Columns)                {                    columns += column.ColumnName;                    columns += ",";                    values += "'";                    values += row[column.ColumnName];                    values += "'";                    values += ",";                 }                columns = columns.TrimEnd(',');                values = values.TrimEnd(',');                sb.Append(columns);                sb.Append(")");                sb.Append(" values (");                sb.Append(values);                sb.Append(");");                sb.Append("/r/n");             }            return sb.ToString();        }                 void InitializeWorkbook(string path)        {            //read the template via FileStream, it is suggested to use FileAccess.Read to prevent file lock.            //book1.xls is an Excel-2007-generated file, so some new unknown BIFF records are added.             using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))            {                hssfworkbook = new HSSFWorkbook(file);                           }        }        DataTable ConvertToDataTable()        {             ISheet sheet = hssfworkbook.GetSheetAt(0);            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();            DataTable dt = new DataTable();            for (int j = 0; j < 5; j++)            {                dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());            }            while (rows.MoveNext())            {                IRow row = (HSSFRow)rows.Current;                DataRow dr = dt.NewRow();                for (int i = 0; i < row.LastCellNum; i++)                {                    ICell cell = row.GetCell(i);                    if (cell == null)                    {                        dr[i] = null;                    }                    else                    {                        dr[i] = cell.ToString();                    }                }                dt.Rows.Add(dr);            }            return dt;        }    }}

附上源码下载地址:

源码下载地址

注:读取Excel用的NPOI的库,好像只能用Excel2003

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台