运行代码会报错:
如何判断单元格内容为空时返回“”空字符串?
using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using Org.BouncyCastle.Math.EC.Multiplier;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Security.Cryptography.X509Certificates;
using System.Windows.Forms;
namespace ExcelToSQL
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void btnSelectFile_Click(object sender, EventArgs e)
{
//选择文件
openFileDialog1.Filter = "XLS文件|*.xls|XLSX文件|*.xlsx";
openFileDialog1.FileName = "";
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
InputWorkbook(openFileDialog1.FileName);//执行导入
}
openFileDialog1.Dispose();
}
#region 导入工作簿
private void InputWorkbook(string filePath)
{
if (filePath != "")
{
try
{
string fileType = filePath.Substring(filePath.LastIndexOf(".") + 1);//获取文件后缀
FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
//判断文件类型
bool isXls = true;
if (fileType == "xlsx")
{
isXls = false;
}
IWorkbook workbook = CreateWorkbook(isXls, fs);
ISheet sheet = workbook.GetSheetAt(0);
int rowCount = sheet.LastRowNum + 1;
int colCount = sheet.GetRow(0).LastCellNum;
dataGridView1.Rows.Clear();
dataGridView1.Columns.Clear();
for (int c = 0; c < colCount; c++)
{
ICell cell = sheet.GetRow(0).GetCell(c);
dataGridView1.Columns.Add(c.ToString() + cell.ToString(), cell.ToString());
}
for (int r = 1; r < rowCount; r++)
{
IRow row = sheet.GetRow(r);
int index = dataGridView1.Rows.Add();
colCount = row.LastCellNum;
for (int c = 0; c < colCount; c++)
{
ICell cell = row.GetCell(c);
if (cell == null)
{
continue;
}
dataGridView1.Rows[index].Cells[c].Value = cell.ToString();
}
}
}
catch (Exception ex)
{
MessageBox.Show("导入失败" + ex.Message);
}
}
else
{
MessageBox.Show("请选择Excel文件");
}
}
#endregion
private static IWorkbook CreateWorkbook(bool isXls, FileStream fs)
{
if (isXls)
{
return new HSSFWorkbook(fs);
}
else
{
return new XSSFWorkbook(fs);
}
}
private void btnToSQL_Click(object sender, EventArgs e)
{
//链接字符串
string constring = @"Data Source=.;Initial Catalog=test;User ID=sa;Password=Sql123";
//链接数据库
SqlConnection con = new SqlConnection(constring);
try
{
con.Open();
for (int i = 0; i < this.dataGridView1.Rows.Count - 1; i++)
{
SqlCommand comm = new SqlCommand();
string N1 = this.dataGridView1.Rows[i].Cells[0].Value.ToString();
string N2 = this.dataGridView1.Rows[i].Cells[1].Value.ToString();
string N3 = this.dataGridView1.Rows[i].Cells[2].Value.ToString();
string N4 = this.dataGridView1.Rows[i].Cells[3].Value.ToString();
string N5 = this.dataGridView1.Rows[i].Cells[4].Value.ToString();
string N6 = this.dataGridView1.Rows[i].Cells[5].Value.ToString();
string N7 = this.dataGridView1.Rows[i].Cells[6].Value.ToString();
string N8 = this.dataGridView1.Rows[i].Cells[7].Value.ToString();
string N9 = this.dataGridView1.Rows[i].Cells[8].Value.ToString();
string N10 = this.dataGridView1.Rows[i].Cells[9].Value.ToString();
string N11 = this.dataGridView1.Rows[i].Cells[10].Value.ToString();
string N12 = this.dataGridView1.Rows[i].Cells[11].Value.ToString();
string N13 = this.dataGridView1.Rows[i].Cells[12].Value.ToString();
string N14 = this.dataGridView1.Rows[i].Cells[13].Value.ToString();
string N15 = this.dataGridView1.Rows[i].Cells[14].Value.ToString();
string N16 = this.dataGridView1.Rows[i].Cells[15].Value.ToString();
string N17 = this.dataGridView1.Rows[i].Cells[16].Value.ToString();
string N18 = this.dataGridView1.Rows[i].Cells[17].Value.ToString();
string N19 = this.dataGridView1.Rows[i].Cells[18].Value.ToString();
string SqlStr = "INSERT tb_test(N1,N2,N3,N4,N5,N6,N7,N8,N9,N10,N11,N12,N13,N14,N15,N16,N17,N18,N19) VALUES ('" + N1 + "','" + N2 + "','" + N3 + "','" + N4 + "','" + N5 + "','" + N6 + "','" + N7 + "','" + N8 + "','" + N9 + "','" + N10 + "','" + N11 + "','" + N12 + "','" + N13 + "','" + N14 + "','" + N15 + "','" + N16 + "','" + N17 + "','" + N18 + "','" + N19 + "')";
comm.CommandText = SqlStr;
comm.Connection = con;
comm.ExecuteNonQuery();
}
MessageBox.Show("数据插入成功");
}
catch (Exception ex)
{
MessageBox.Show("更新失败,失败原因:" + ex.Message);
}
finally
{
con.Close();
}
}
}
}
需要上传的测试数据如下:
https://blog.csdn.net/headnull/article/details/90899972
将excel整理的表结构生成insert建表sql:https://download.csdn.net/download/gongjin28_csdn/85486975