上班第一天,经理要求做出Excel导入导出,以前做这方面的不多,所以记录下来,新手一枚,望高手指教。
Excel导出
方法众多,选择了直接IO操作。
代码如下:
/// <summary>
/// 导入excel
/// </summary>
/// <returns></returns>
public bool ExportExcel()
{
try
{
DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection(connectString))
{
conn.Open();
using (SqlCommand command = conn.CreateCommand())
{
command.CommandText = "select * from tb_user;";
using (SqlDataAdapter adp = new SqlDataAdapter(command))
{
adp.Fill(dt);
}
}
}
//设置导出文件路径
string path = HttpContext.Server.MapPath("/Excel/");
//设置新建文件路径及名称
string savePath = path + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss") + ".xlsx";
//创建文件
FileStream file = new FileStream(savePath, FileMode.CreateNew, FileAccess.Write);
//以指定的字符编码向指定的流写入字符
StreamWriter sw = new StreamWriter(file, Encoding.GetEncoding("GB2312"));
StringBuilder strbu = new StringBuilder();
//写入标题
for (int i = 0; i < dt.Columns.Count; i++)
{
strbu.Append(dt.Columns[i].ColumnName.ToString() + "\t");
}
//加入换行字符串
strbu.Append(Environment.NewLine);
//写入内容
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
strbu.Append(dt.Rows[i][j].ToString() + "\t");
}
strbu.Append(Environment.NewLine);
}
sw.Write(strbu.ToString());
sw.Flush();//清理当前编写器的所有缓冲区,并使所有缓存数据写入基础流
file.Flush();
sw.Close();
sw.Dispose();
file.Close();
file.Dispose();
return true;
}
catch (Exception ex)
{
return false;
}
}
Excel导入
用的是OLEDB.注意安装驱动。
/// <summary>
/// 导出excel
/// </summary>
/// <returns></returns>
public bool ImportExcel()
{
string strConn;
OleDbConnection conn;
string sheetName;
DataSet ds;
//获取后缀名
string fileExt = System.IO.Path.GetExtension(file.FullName);
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + file.FullName + ";" + "Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
conn = new OleDbConnection(strConn);
//获取所有的 sheet 表
DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
ds = new DataSet();
for (int i = 0; i < dtSheetName.Rows.Count; i++)
{
DataTable dt = new DataTable();
dt.TableName = "table" + i.ToString();
//获取表名
sheetName = dtSheetName.Rows[i]["TABLE_NAME"].ToString();
OleDbDataAdapter oleda = new OleDbDataAdapter("select * from [" + sheetName + "]", conn);
oleda.Fill(dt);
SqlConnection sqlConn = new SqlConnection(connectString);
sqlConn.Open();
string sql="";
for (int j = 0; j < dt.Rows.Count; j++)
{
var dr=dt.Rows[j];
//导入数据
string userName = dr["userName"].ToString();
string Sex = dr["Sex"].ToString();
string userPassword = dr["userPassword"].ToString();
int Age = Convert.ToInt32(dr["Age"].ToString());
sql+= "insert into TB_User values('" + userName + "'," + Age + ",'" + Sex + "','" + userPassword + "');";
}
SqlCommand cmd = new SqlCommand(sql, sqlConn);
if (cmd.ExecuteNonQuery() > 0)
{
return true;
}
sqlConn.Close();
}
//关闭连接,释放资源
conn.Close();
conn.Dispose();
return false;
}