C#导入Exel
int result = 0; try { string fileType = this.upLoadExel.FileName.Split('.')[this.upLoadExel.FileName.Split('.').Length - 1]; string[] NoExPrentFile = new string[] { "xls", "xlsx" }; if (fileType.ToLower() == NoExPrentFile[0] || fileType.ToLower() == NoExPrentFile[1]) { string fname = DateTime.Now.Ticks.ToString(); string phyFileName = Request.PhysicalApplicationPath + "\\Exel\\" + fname + "." + fileType; this.upLoadExel.PostedFile.SaveAs(phyFileName); string userid = this.hidParentID.Value; if (userid == "" || userid == null) { HTMLHelper.Alert("请选择右边的用户后在添加设备!"); return; } result = DownloadQueueLogic.GetInstance().GetDevicesExel(phyFileName, userid); if (result > 0) { HTMLHelper.Alert("导入Exel成功!"); } else if (result == -2) { HTMLHelper.Alert("Exel IMEI列不存在 或者IMEI 列有重复数据!"); } else { HTMLHelper.Alert("导入Exel失败!"); } } else { HTMLHelper.Alert("请上传正确的Exel文件!"); return; } } catch (Exception ex) { Logging.WriteLog(ex.Message); HTMLHelper.Alert("导入Exel失败,或者Exel组件没安装!!"); }
public static string ConnectionString
{
get
{
string ConStringEncrypt = ConfigurationManager.ConnectionStrings["Connection String"].ToString();
return ConStringEncrypt;
}
}
public static string conString
{
get
{
return ConnectionString;
}
}
public static int executeTransaction(Dictionary<string, object> parameter)
{
int result = 0;
SqlTransaction myTransaction = null;
using (SqlConnection con = new SqlConnection(conString))
{
using (SqlCommand cmd = new SqlCommand())
{
try
{
cmd.Connection = con;
con.Open();
myTransaction = con.BeginTransaction();
cmd.Transaction = myTransaction;
foreach (var item in parameter)
{
cmd.CommandText = item.Key;
if (item.Value != null)
{
foreach (var value in (SqlParameter[])item.Value)
{
cmd.Parameters.Add(value);
}
}
result = int.Parse(cmd.ExecuteNonQuery().ToString());
cmd.Parameters.Clear();
}
myTransaction.Commit();
}
catch (Exception ex)
{
Logging.WriteLog(ex);
myTransaction.Rollback();
}
finally
{
con.Dispose();
con.Close();
}
return result;
}
}
}
public int GetDevicesExel(string fileName, string users)
{
int res = 0;
#region exel导入到Datatable
DataTable dt = new DataTable();
string strCon = "Provider= Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
OleDbConnection conn = new OleDbConnection(strCon);
OleDbDataAdapter myCommand;
conn.Open();
//返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等
DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
//包含excel中表名的字符串数组
string[] strTableNames = new string[dtSheetName.Rows.Count];
for (int k = 0; k < dtSheetName.Rows.Count; k++)
{
strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
}
for (int j = 0; j < strTableNames.Length; j++)
{
//从指定的表明查询数据,可先把所有表明列出来供用户选择
string strExcel = "select * from [" + strTableNames[j] + "]";
myCommand = new OleDbDataAdapter(strExcel, strCon);
myCommand.Fill(dt);
}
#endregion
//复制表结构
DataTable dataTable = GetTableSchema();
#region 填充虚拟表数据(列要和数据库的一致包括主键 主键可以随便写)
Devices de = new Devices();
for (int i = 0; i < dt.Rows.Count; i++)
{
int count = Regex.Matches(dt.Rows[i][5].ToString(), @"\d").Count;
if (count>0)
{
continue;
}
if (dt.Rows[i][0].ToString()==""||dt.Rows[i][0].ToString()==null)
{
continue;
}
DataRow dataRow = dataTable.NewRow();
dataRow[0] = 5555; //SerialNumber
dataRow[1] = dt.Rows[i][0].ToString(); //SerialNumber
dataRow[2] = ""; //DeviceName
dataRow[3] = "123456"; //DevicePassword
dataRow[4] = "";//CarUserName
dataRow[5] = "";//CarNum
dataRow[6] = "";//CellPhone
dataRow[7] = 1; //Status
dataRow[8] = dt.Rows[i][2].ToString(); //PhoneNum
dataRow[9] = Utility.SafeInt(dt.Rows[i][1].ToString()); //Model
dataRow[10] = "";//Description
dataRow[11] = DateTime.UtcNow; //Created
dataRow[12] = false; //Deleted
dataRow[13] = de.ExpireByUser;//ActiveDate
dataRow[14] = de.ExpireByUser;//HireStartDate
dataRow[15] = de.ExpireByUser;//HireExpireDate
dataRow[16] = 0.00; //SpeedLimit
dataRow[17] = users; //UserID
dataRow[18] = -1; //GroupID
dataRow[19] = 1; //Icon
dataRow[20] = 0; //OILCoefficient
dataRow[21] = 6.90;//OilPrice
dataRow[22] = DateTime.UtcNow; //CreatedByUser
dataRow[23] = de.ExpireByUser; //ExpireByUser
dataRow[24] = -1.00; //OilVolume
dataRow[25] = 0.00;//OilLow
dataRow[26] = 0.00;//OilHigh
dataRow[27] = 0;//AddHireDay
dataRow[28] = dt.Rows[i][4].ToString(); //SearchPhone
dataRow[29] = dt.Rows[i][3].ToString(); //SearchContent
dataRow[30] = dt.Rows[i][5].ToString(); //Keyword
dataTable.Rows.Add(dataRow);
}
#endregion
DataView dv = new DataView(dataTable);
if (dv.Count != dv.ToTable(true, "IMEI").Rows.Count)
{
res = -2;
return res;
}
#region 判断exel里面是否和数据库有重复的imei 有就删除在插入
for (int i = 0; i < dataTable.Rows.Count; i++)
{
string istrue = DevicesLogic.GetInstance().isExistImeiExtend(dataTable.Rows[i][1].ToString());
if ( Utility.SafeString(istrue) != "")
{
int result = DevicesLogic.GetInstance().UpdateDelDevicesByID(int.Parse(istrue));
if (result <= 0)
{
res = 0;
return res;
}
}
}
#endregion
SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(conString);
sqlBulkCopy.DestinationTableName = "Devices";
if (dataTable != null && dataTable.Rows.Count != 0)
{
sqlBulkCopy.WriteToServer(dataTable);
res = 1;
}
sqlBulkCopy.Close();
return res;
}
#region 导入exel操作数据库方法
private static DataTable GetTableSchema()
{
return ExecuteDataset(conString, CommandType.Text, @"select DeviceID, [SerialNumber] ,[DeviceName],[DevicePassword],[CarUserName],[CarNum] ,[CellPhone],[Status] ,[PhoneNum],[Model] ,[Description]
,[Created]
,[Deleted]
,[ActiveDate]
,[HireStartDate]
,[HireExpireDate]
,[SpeedLimit]
,[UserID]
,[GroupID]
,[Icon]
,[OILCoefficient]
,[OilPrice]
,[CreatedByUser]
,[ExpireByUser]
,[OilVolume]
,[OilLow]
,[OilHigh]
,[AddHireDay]
,[SearchPhone]
,[SearchContent]
,[Keyword] from Devices where 1=2").Tables[0];
}
public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)
{
// Pass through the call providing null for the set of SqlParameters
return ExecuteDataset(connectionString, commandType, commandText, (SqlParameter[])null);
}
public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
// Create & open a SqlConnection, and dispose of it after we are done
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// Call the overload that takes a connection in place of the connection string
return ExecuteDataset(connection, commandType, commandText, commandParameters);
}
}
public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (connection == null) throw new ArgumentNullException("connection");
// Create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
// Create the DataAdapter & DataSet
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
// Fill the DataSet using default values for DataTable names, etc
da.Fill(ds);
// Detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();
if (mustCloseConnection)
connection.Close();
// Return the dataset
return ds;
}
}
private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, out bool mustCloseConnection)
{
if (command == null) throw new ArgumentNullException("command");
if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
// If the provided connection is not open, we will open it
if (connection.State != ConnectionState.Open)
{
mustCloseConnection = true;
connection.Open();
}
else
{
mustCloseConnection = false;
}
// Associate the connection with the command
command.Connection = connection;
// Set the command text (stored procedure name or SQL statement)
command.CommandText = commandText;
// If we were provided a transaction, assign it
if (transaction != null)
{
if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
command.Transaction = transaction;
}
// Set the command type
command.CommandType = commandType;
// Attach the command parameters if they are provided
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
return;
}
private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
{
if (command == null) throw new ArgumentNullException("command");
if (commandParameters != null)
{
foreach (SqlParameter p in commandParameters)
{
if (p != null)
{
// Check for derived output value with no value assigned
if ((p.Direction == ParameterDirection.InputOutput ||
p.Direction == ParameterDirection.Input) &&
(p.Value == null))
{
p.Value = DBNull.Value;
}
command.Parameters.Add(p);
}
}
}
}
#endregion
这里用的用的是
sqlBulkCopy
注意安装：AccessDatabaseEngine.exe ，IIS 应用池32 改成true
NPOI导出exel
public static IWorkbook DtToExel(DataTable data, string fileName, string sheetName, bool isColumnWritten) { IWorkbook workbooks = null; int i = 0; int j = 0; ISheet sheet = null; int count = 0; if (fileName.IndexOf(".xlsx") > 0) // 2007版本 workbooks = new XSSFWorkbook(); else if (fileName.IndexOf(".xls") > 0) // 2003版本 workbooks = new HSSFWorkbook(); try { if (workbooks != null) { sheet = workbooks.CreateSheet(sheetName); } else { return null; } if (isColumnWritten == true) //写入DataTable的列名 { IRow row = sheet.CreateRow(0); for (j = 0; j < data.Columns.Count; ++j) { row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName); } } else { count = 0; } for (i = 0; i < data.Rows.Count; ++i) { IRow row = sheet.CreateRow(count); for (j = 0; j < data.Columns.Count; ++j) { row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString()); } count++; } return workbooks; } catch (Exception ex) { Console.WriteLine("Exception: " + ex.Message); return null; } }
调用
DataTable data = new DataTable(); for (int i = 0; i < 5; ++i) { data.Columns.Add("Columns_" + i.ToString(), typeof(string)); } for (int i = 0; i < 10; ++i) { DataRow row = data.NewRow(); row["Columns_0"] = "item0_" + i.ToString(); row["Columns_1"] = "item1_" + i.ToString(); row["Columns_2"] = "item2_" + i.ToString(); row["Columns_3"] = "item3_" + i.ToString(); row["Columns_4"] = "item4_" + i.ToString(); data.Rows.Add(row); } var book = ExcelHelper.DtToExel(data, "xxxx.xlsx", "ssss", true); ; MemoryStream ms = new MemoryStream(); book.Write(ms); Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode("报表" + "_" + DateTime.Now.ToString("yyyy-MM-dd"), System.Text.Encoding.UTF8))); Response.BinaryWrite(ms.ToArray()); Response.End(); book = null; ms.Close(); ms.Dispose();
或者
MemoryStream ms = new MemoryStream(); try { wookBook.Write(ms); Response.Clear(); Response.ClearContent(); Response.ClearHeaders(); Response.AddHeader("Content-Disposition", "attachment;filename=" + "Excel.xlsx"); //Response.AddHeader("Content-Length", "10000"); Response.AddHeader("Content-Transfer-Encoding", "binary"); Response.ContentType = "application/octet-stream"; Response.ContentEncoding = System.Text.Encoding.UTF8; Response.BinaryWrite(ms.GetBuffer()); Response.Flush(); Response.End(); } catch (Exception) { } finally { ms.Dispose(); ms.Close(); }
附加NPOI帮助类
using System; using System.Collections.Generic; using System.Linq; using System.Text; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; using NPOI.HSSF.UserModel; using System.IO; using System.Data; using System.Web; using System.Web.Script.Serialization; using System.Collections; using NPOI.SS.Util; namespace SMS.Common { public class ExcelHelper : IDisposable { private string fileName = null; //文件名 private IWorkbook workbook = null; private FileStream fs = null; private bool disposed; public ExcelHelper(string fileName) { this.fileName = fileName; disposed = false; } /// <summary> /// 将DataTable数据导入到excel中 /// </summary> /// <param name="data">要导入的数据</param> /// <param name="isColumnWritten">DataTable的列名是否要导入</param> /// <param name="sheetName">要导入的excel的sheet的名称</param> /// <returns>导入数据行数(包含列名那一行)</returns> public int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten) { int i = 0; int j = 0; int count = 0; ISheet sheet = null; fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite); if (fileName.IndexOf(".xlsx") > 0) // 2007版本 workbook = new XSSFWorkbook(); else if (fileName.IndexOf(".xls") > 0) // 2003版本 workbook = new HSSFWorkbook(); try { if (workbook != null) { sheet = workbook.CreateSheet(sheetName); } else { return -1; } if (isColumnWritten == true) //写入DataTable的列名 { IRow row = sheet.CreateRow(0); for (j = 0; j < data.Columns.Count; ++j) { row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName); } count = 1; } else { count = 0; } for (i = 0; i < data.Rows.Count; ++i) { IRow row = sheet.CreateRow(count); for (j = 0; j < data.Columns.Count; ++j) { row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString()); } ++count; } workbook.Write(fs); //写入到excel return count; } catch (Exception ex) { Console.WriteLine("Exception: " + ex.Message); return -1; } } /// <summary> /// 将excel中的数据导入到DataTable中 /// </summary> /// <param name="sheetName">excel工作薄sheet的名称</param> /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param> /// <returns>返回的DataTable</returns> public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn) { ISheet sheet = null; DataTable data = new DataTable(); int startRow = 0; try { fs = new FileStream(fileName, FileMode.Open, FileAccess.Read); if (fileName.IndexOf(".xlsx") > 0) // 2007版本 workbook = new XSSFWorkbook(fs); else if (fileName.IndexOf(".xls") > 0) // 2003版本 workbook = new HSSFWorkbook(fs); if (sheetName != null) { sheet = workbook.GetSheet(sheetName); if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet { sheet = workbook.GetSheetAt(0); } } else { sheet = workbook.GetSheetAt(0); } if (sheet != null) { IRow firstRow = sheet.GetRow(0); int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数 if (isFirstRowColumn) { for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { ICell cell = firstRow.GetCell(i); if (cell != null) { string cellValue = cell.StringCellValue; if (cellValue != null) { DataColumn column = new DataColumn(cellValue); data.Columns.Add(column); } } } startRow = sheet.FirstRowNum + 1; } else { startRow = sheet.FirstRowNum; } //最后一列的标号 int rowCount = sheet.LastRowNum; for (int i = startRow; i <= rowCount; ++i) { IRow row = sheet.GetRow(i); if (row == null) continue; //没有数据的行默认是null DataRow dataRow = data.NewRow(); for (int j = row.FirstCellNum; j < cellCount; ++j) { if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null dataRow[j] = row.GetCell(j).ToString(); } data.Rows.Add(dataRow); } } return data; } catch (Exception ex) { Console.WriteLine("Exception: " + ex.Message); return null; } } public static IWorkbook DtToExel(DataTable data, string fileName, string sheetName, bool isColumnWritten) { IWorkbook workbooks = null; int i = 0; int j = 0; ISheet sheet = null; if (fileName.IndexOf(".xlsx") > 0) // 2007版本 workbooks = new XSSFWorkbook(); else if (fileName.IndexOf(".xls") > 0) // 2003版本 workbooks = new HSSFWorkbook(); try { if (workbooks != null) { sheet = workbooks.CreateSheet(sheetName); } else { return null; } if (isColumnWritten == true) //写入DataTable的列名 { IRow row = sheet.CreateRow(0); for (j = 0; j < data.Columns.Count; ++j) { row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName); } } for (i = 0; i < data.Rows.Count; ++i) { IRow row = sheet.CreateRow(i); for (j = 0; j < data.Columns.Count; ++j) { row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString()); } } return workbooks; } catch (Exception ex) { Console.WriteLine("Exception: " + ex.Message); return null; } } public void Dispose() { Dispose(true); GC.SuppressFinalize(this); } protected virtual void Dispose(bool disposing) { if (!this.disposed) { if (disposing) { if (fs != null) fs.Close(); } fs = null; disposed = true; } } public static DataTable JsonToDataTable(string json) { JavaScriptSerializer javaScriptSerializer = new JavaScriptSerializer(); javaScriptSerializer.MaxJsonLength = int.MaxValue; ArrayList arrayList = javaScriptSerializer.Deserialize<ArrayList>(json); DataTable dataTable = new DataTable(); var kyes = (arrayList[0] as Dictionary<string, object>).Keys; foreach (string current in kyes) { dataTable.Columns.Add(current, typeof(string)); } if (arrayList.Count > 0) { foreach (Dictionary<string, object> dictionary in arrayList) { DataRow dataRow = dataTable.NewRow(); foreach (string key in dictionary.Keys) { dataRow[key] = dictionary[key]; } dataTable.Rows.Add(dataRow); } } return dataTable; } /// <summary> /// </summary> /// <param name="dataTable">数据源</param> /// <param name="sheetName">Sheet名称</param> /// <param name="widths">宽度数组</param> /// <param name="title">标题</param> /// <param name="isColumnWritten">是否写入列名</param> /// <returns></returns> public static IWorkbook TableToWorkBook(DataTable dataTable, string sheetName,int[] widths,string title,bool isColumnWritten) { IWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(sheetName); int rowIndex = 0;//每写入一行 RowIndex + 1 sheet.DisplayGridlines = false;//关闭网格线 try { #region 设置标题 if (title != null) { IRow titleRow = sheet.CreateRow(0);//创建标题行 titleRow.CreateCell(0).SetCellValue(title); /*合并单元格 四个参数为：起始行，结束行，起始列，结束列*/ sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dataTable.Columns.Count - 1)); ICellStyle style = workbook.CreateCellStyle(); /*设置单元格的样式：水平对齐居中*/ style.Alignment = HorizontalAlignment.Center; /*新建一个字体样式对象*/ IFont font = workbook.CreateFont(); /*设置字体大小*/ font.FontHeight = 20 * 20; /*使用SetFont方法将字体样式添加到单元格样式中 */ style.SetFont(font); /*将新的样式赋给单元格*/ titleRow.Cells[0].CellStyle = style; rowIndex++; } #endregion if (isColumnWritten) //写入DataTable的列名 { IRow row = sheet.CreateRow(1); ICellStyle colnumStyle = workbook.CreateCellStyle();//列标题样式 colnumStyle.Alignment = HorizontalAlignment.Center;//水平居中 IFont font = workbook.CreateFont();//新建一个字体样式对象 font.Boldweight = short.MaxValue;//设置字体加粗样式 colnumStyle.SetFont(font);//添加字体样式 /*设置边框样式*/ colnumStyle.BorderLeft = BorderStyle.Thin; colnumStyle.BorderRight = BorderStyle.Thin; colnumStyle.BorderTop = BorderStyle.Thin; colnumStyle.BorderBottom = BorderStyle.Thin; for (int i = 0; i < dataTable.Columns.Count; i++) { var cell = row.CreateCell(i); cell.SetCellValue(dataTable.Columns[i].ColumnName); cell.CellStyle = colnumStyle; } rowIndex++; } ICellStyle style1 = workbook.CreateCellStyle(); style1.BorderLeft = BorderStyle.Thin; style1.BorderRight = BorderStyle.Thin; style1.BorderTop = BorderStyle.Thin; style1.BorderBottom = BorderStyle.Thin; for (int i = 0; i < dataTable.Rows.Count; i++) { IRow row = sheet.CreateRow(rowIndex + i); for (int j = 0; j < dataTable.Columns.Count; j++) { var cell = row.CreateCell(j); cell.SetCellValue(dataTable.Rows[i][j].ToString()); cell.CellStyle = style1; } } /*设置列宽*/ if (widths != null) { for (int i = 0; i < widths.Length; i++) { sheet.SetColumnWidth(i, 256 * widths[i]); } } return workbook; } catch (Exception) { return null; } } /// <summary> /// 下载Excel /// </summary> /// <param name="request"></param> /// <param name="response"></param> /// <param name="wookBook"></param> public static void DownExcel(HttpRequest request, HttpResponse response, IWorkbook wookBook,string name) { MemoryStream ms = new MemoryStream(); try { wookBook.Write(ms); response.Clear(); response.ClearContent(); response.ClearHeaders(); response.AddHeader("Content-Disposition", "attachment;filename=" + name + DateTime.Now.ToString("yyyy-MM-dd hh-mm-ss") + ".xls"); response.AddHeader("Content-Transfer-Encoding", "binary"); response.ContentType = "application/octet-stream"; response.ContentEncoding = System.Text.Encoding.UTF8; response.BinaryWrite(ms.GetBuffer()); response.Flush(); response.End(); } catch (Exception) { } finally { ms.Dispose(); ms.Close(); } } } } Demo调用： DataTable dataTable =xxxx int[] widths = new[] {12,13,22,13,24,9,9,9,21,12,21}; var work = Common.ExcelHelper.TableToWorkBook(dataTable, "Sheet1", widths, "服务费统计", true); if (work == null) return; Common.ExcelHelper.DownExcel(Request, Response, work,"服务费统计");
