Question

ASP.NET c# 导入excel文件如何实现，求代码

我现在建了一个fileUpload控件，一个button控件通过fileUpload选择所需的EXCEL文件，点击button实现选择excel文件中的几列数据，导入到SQL中已经建立的表中,求实现的代码

Answer 1

第一步，将Excel文件保存到服务器端某个路径。
第二步，打开这个Excel文件，读取里面的单元格的内容。
第二步可以通过OLEDB连接到Excel，当成数据库来读写，也可以用ExcelApplication打开进行读写，用第三方的组件读写。
当成数据库读写的方式很简单，不需要调用.net以外的组件，节约资源。但是有些格式的无法读取，局限性很大。
用ExcelApplication读取的方式，不太适合web服务器端使用，对资源耗用很大，而且有可能导致Excel进程无法关闭的问题。
用第三方组件读写是最简单，最稳定的。当然有的需要购买，也有破解的。自己可以去找。
推荐一个我用过的：ComponentOne 里面的Excel组件。

Answer 2

if (FileUpload1.PostedFile != null)
{
string fileName;
fileName = FileUpload1.PostedFile.FileName;
fileName = Path.GetFileName(fileName);
FileUpload1.PostedFile.SaveAs(fileName);
FileStream fp = new FileStream(fileName, FileMode.Open);
StreamReader sr = new StreamReader(fp, Encoding.GetEncoding(932));
string buffer = sr.ReadToEnd();//文件读取到末尾
string[] fileLine = buffer.Split((char)0x0d);//把数据切割存放
//EXCEL文件的数据就存放在fileLine字符串数组里面了
......
}
这个只是我随便写的.你将就看看.

Answer 3

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data.Sql;
using System.Data.SqlClient;
using Microsoft.Office.Interop;
using Microsoft.Office.Interop.Excel;
using System.Reflection;

namespace ExcelConvertor
{
public partial class Form1 : Form
{

public Form1()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
this.openFileDialog1.Multiselect = true;
this.openFileDialog1.Filter = "EXCEL文件(*.xls)|*.xls";
if (this.openFileDialog1.ShowDialog() == DialogResult.OK)
{
this.textBox1.Text = openFileDialog1.FileName;
filename = openFileDialog1.FileName;
}
}

private void button2_Click(object sender, EventArgs e)
{
System.Data.DataTable dt = new System.Data.DataTable();
ExcelToDT(this.textBox1.Text, ref dt);
ExcelConvertor(dt);

}
public static string filename;
public static bool ExcelConvertor(System.Data.DataTable dt)
{

string s = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() +DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString();
string savefilepath = filename.Substring(0, filename.LastIndexOf('\\') + 1) +s+ ".dat";
string djbh = dt.Rows[2][0].ToString(); //单据编号
string lybm = dt.Rows[2][2].ToString(); //领用部门
string flbm = dt.Rows[2][3].ToString(); //发料部门
string lyrq = "";//领用日期
if (dt.Rows[2][4].ToString() != "")
{
if (dt.Rows[2][4].ToString().Contains("-"))
{
lyrq = dt.Rows[2][4].ToString();//领用日期
}
else
{
lyrq = DateTime.FromOADate(Convert.ToInt32(dt.Rows[2][4].ToString())).ToString("d"); //领用日期

}
}
else
{

lyrq = DateTime.Now.ToLongDateString();
}

string year = DateTime.Parse(lyrq).Year.ToString();
string month = DateTime.Parse(lyrq).Month.ToString();
string xmgd = dt.Rows[4][6].ToString();//项目/工段
string cpdm = dt.Rows[4][0].ToString();//产品代码
string cpmc = dt.Rows[4][2].ToString();//产品名称
string pcs = dt.Rows[4][4].ToString();//排产计划数

// int rowcount = dt.Rows.Count;

//生成Excel文件
Microsoft.Office.Interop.Excel.Application app =
new Microsoft.Office.Interop.Excel.ApplicationClass();
try
{
app.Visible = false;
Workbook wBook = app.Workbooks.Add(true);
Worksheet wSheet = wBook.Worksheets[1] as Worksheet;
if (dt.Rows.Count > 0)
{
int row = 0;
row = dt.Rows.Count; //总行数
int col = dt.Columns.Count; //总列数，可能没用

for (int i = 1; i <= dt.Rows.Count; i++) //
{
if (dt.Rows[i + 5][0].ToString() == "")
{
break;
}
wSheet.Cells[i, 1] = "21";
wSheet.Cells[i, 2] = "19";
wSheet.Cells[i, 3] = year;
wSheet.Cells[i, 4] = month;
wSheet.Cells[i, 5] = djbh;
wSheet.Cells[i, 6] = "";
wSheet.Cells[i, 7] = "吉林瑞宝生产领用";
wSheet.Cells[i, 8] = lyrq;
wSheet.Cells[i, 9] = "R10";//制单人
wSheet.Cells[i, 10] = "";
wSheet.Cells[i, 11] = "";
wSheet.Cells[i, 12] = "5001-97R";
wSheet.Cells[i, 13] = "1";
wSheet.Cells[i, 14] = "RMB";
wSheet.Cells[i, 15] = xmgd;//项目工段
wSheet.Cells[i, 16] = cpdm;
wSheet.Cells[i, 17] = "2008-04-07";
wSheet.Cells[i, 18] = "2008-04-07";
wSheet.Cells[i, 19] = "";
wSheet.Cells[i, 20] = "";
wSheet.Cells[i, 21] = "0";
wSheet.Cells[i, 22] = "0";
wSheet.Cells[i, 23] = "";
wSheet.Cells[i, 24] = ",";
wSheet.Cells[i, 25] = "";
wSheet.Cells[i, 26] = ",";
wSheet.Cells[i, 27] = "";
wSheet.Cells[i, 28] = "";

wSheet.Cells[i, 29] = i.ToString();//行号
wSheet.Cells[i, 30] = dt.Rows[i+ 5][0].ToString();
wSheet.Cells[i, 31] = "";
wSheet.Cells[i, 32] = "'"+dt.Rows[i + 5][11].ToString(); //货位
wSheet.Cells[i, 33] = dt.Rows[i + 5][2].ToString();//计量单位
wSheet.Cells[i, 34] = dt.Rows[i + 5][8].ToString();//实发数量
wSheet.Cells[i, 35] = dt.Rows[i + 5][9].ToString();//计划单价
wSheet.Cells[i, 36] = "";
wSheet.Cells[i, 37] = "100";
wSheet.Cells[i, 38] = double.Parse(dt.Rows[i + 5][8].ToString()) * double.Parse(dt.Rows[i + 5][9].ToString());
wSheet.Cells[i, 39] = double.Parse(dt.Rows[i + 5][8].ToString()) * double.Parse(dt.Rows[i + 5][9].ToString());
wSheet.Cells[i, 40] = "";
wSheet.Cells[i, 41] = "0";
wSheet.Cells[i, 42] = "0";
wSheet.Cells[i, 43] = "";
wSheet.Cells[i, 44] = "";
wSheet.Cells[i, 45] = "";
wSheet.Cells[i, 46] = "0";
wSheet.Cells[i, 47] = "";
wSheet.Cells[i, 48] = "";
wSheet.Cells[i, 49] = "";
wSheet.Cells[i, 50] = "";
wSheet.Cells[i, 51] = "";
wSheet.Cells[i, 52] = "";
wSheet.Cells[i, 53] = "";
wSheet.Cells[i, 54] = "";
wSheet.Cells[i, 55] = "0";
wSheet.Cells[i, 56] = double.Parse(dt.Rows[i + 5][8].ToString()) * double.Parse(dt.Rows[i + 5][9].ToString());
wSheet.Cells[i, 57] = "0";
wSheet.Cells[i, 58] = "0";
wSheet.Cells[i, 59] = "0";
wSheet.Cells[i, 60] = double.Parse(dt.Rows[i + 5][8].ToString()) * double.Parse(dt.Rows[i + 5][9].ToString());
wSheet.Cells[i, 61] = "";
wSheet.Cells[i, 62] = "";
wSheet.Cells[i, 63] = "1003";
wSheet.Cells[i, 64] = "R027";
wSheet.Cells[i, 65] = "";
wSheet.Cells[i, 66] = "";
wSheet.Cells[i, 67] = "";
wSheet.Cells[i, 68] = "0";
wSheet.Cells[i, 69] = "0";
wSheet.Cells[i, 70] = "";
wSheet.Cells[i, 71] = "";
wSheet.Cells[i, 72] = "0";
wSheet.Cells[i, 73] = "0";
wSheet.Cells[i, 74] = "";
wSheet.Cells[i, 75] = "";
wSheet.Cells[i, 76] = "0001-05";
wSheet.Cells[i, 77] = "2";
wSheet.Cells[i, 78] = "";
wSheet.Cells[i, 79] = "1";
wSheet.Cells[i, 80] = "";
wSheet.Cells[i, 81] = "";
wSheet.Cells[i, 82] = "0";
wSheet.Cells[i, 83] = "";
wSheet.Cells[i, 84] = "";
wSheet.Cells[i, 85] = "";
wSheet.Cells[i, 86] = "";
wSheet.Cells[i, 87] = "";
wSheet.Cells[i, 88] = "";
wSheet.Cells[i, 89] = "";
wSheet.Cells[i, 90] = "";
wSheet.Cells[i, 91] = "";
wSheet.Cells[i, 92] = "";
wSheet.Cells[i, 93] = "";
wSheet.Cells[i, 94] = "";
wSheet.Cells[i, 95] = "";
wSheet.Cells[i, 96] = "";
wSheet.Cells[i, 97] = "";
wSheet.Cells[i, 98] = "";
wSheet.Cells[i, 99] = "";
wSheet.Cells[i, 100] = "";
wSheet.Cells[i, 101] = "";
wSheet.Cells[i, 102] = "";
wSheet.Cells[i, 103] = "";
wSheet.Cells[i, 104] = "";
wSheet.Cells[i, 105] = "";
wSheet.Cells[i, 106] = "";
wSheet.Cells[i, 107] = "";
wSheet.Cells[i, 108] = "";
wSheet.Cells[i, 109] = "";
wSheet.Cells[i, 110] = "";
wSheet.Cells[i, 111] = "";
wSheet.Cells[i, 112] = "";
wSheet.Cells[i, 113] = "";
wSheet.Cells[i, 114] = "";
wSheet.Cells[i, 115] = "";
wSheet.Cells[i, 116] = "";
wSheet.Cells[i, 117] = "";

}
}
wSheet.SaveAs(savefilepath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

wBook.Close(false, Missing.Value, Missing.Value);//关闭
wSheet = null;
wBook = null;
app.Quit();
app = null;

//int size = excelTable.Columns.Count;
//for (int i = 0; i < size; i++)
//{
// wSheet.Cells[1, 1 + i] = excelTable.Columns[i].ColumnName;
//}
//设置禁止弹出保存和覆盖的询问提示框
//app.DisplayAlerts = false;
//app.AlertBeforeOverwriting = false;
////保存工作簿
//wBook.Save();
////保存excel文件

//app.Save("C:\a.xls");
//app.SaveWorkspace("C:\a.xls");
MessageBox.Show("转换成功！");
return true;
}
catch (Exception err)
{
MessageBox.Show("导出Excel出错！错误原因：" + err.Message, "提示信息",
MessageBoxButtons.OK, MessageBoxIcon.Information);
return false;
}
finally
{
}

}

//public static bool SaveDataTableToExcel(System.Data.DataTable excelTable, string filePath)
//{
// Microsoft.Office.Interop.Excel.Application app =
// new Microsoft.Office.Interop.Excel.ApplicationClass();

//}

public static System.Data.DataTable ExcelToDT(string filename, ref System.Data.DataTable dt)
{ //先将EXCEL导入到数据库,一:先把EXCEL导入dateView,二:然后将dateView里的数据导入到数据库里面
//EXCEL 的连接串
string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + filename + ";" +
"Extended Properties='Excel 8.0;IMEX=1'";
OleDbConnection objConn = new OleDbConnection(sConnectionString);
objConn.Open();
System.Data.DataTable resultdt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[4] { null, null, null, "TABLE" });
objConn.Close();
try
{

foreach (DataRow dr in resultdt.Rows)
{
//这里的删掉一些找不到的sheet,类似sheet1_
if (dr["Table_Name"].ToString().IndexOf('_') < 0)
{
OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + dr["Table_Name"].ToString() + "]", objConn);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
objAdapter1.SelectCommand = objCmdSelect;
DataSet objDataset1 = new DataSet();

//建立EXCEL的连接
objConn.Open();
objAdapter1.Fill(objDataset1);
objConn.Close();

// DataGrid1.DataSource = objDataset1.Tables[0].DefaultView; //测试代码,用来测试是否能读出EXCEL上面的数据
// DataGrid1.DataBind();
//这里的tmpdt就是读到的当前sheet的dt了
System.Data.DataTable tmpdt = objDataset1.Tables[0];
dt = tmpdt;
string fileName = "";
fileName = filename.Substring(filename.LastIndexOf('\\') + 1);

}
break;

}
return dt;
}
catch (Exception e)
{
if (objConn.State == ConnectionState.Open)
{
objConn.Close();
}
string s = e.Message;
Console.Write("转换操作出错");
return null;

}
}

}
}

Answer 4

详细讲解，在低代码平台上如何快速进行Excel数据的导出导入？