项目中要用到excel导入数据，用NPOI方式做了一个demo，记录如下：
Form1代码：
public Form1()
{
InitializeComponent();
} private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog f = new OpenFileDialog();
f.Multiselect = true;
f.ShowDialog();
string[] filenames = f.FileNames; if (filenames.Length > )
{
this.label1.Text = filenames[];
string strFile = System.IO.Path.GetFileName(filenames[]);
DataTable dt = GetDataBySQLNew("McsDW", "select 1 from [McsDW].[dbo].[UserGateWayNumber] where ExcelFileName='" + strFile + "'");
if (dt != null)
{
if (dt.Rows.Count > )
{
MessageBox.Show("数据库里已经导入名称为 " + strFile + " 的excel表，请核对！");
this.label1.Text = "";
}
}
}
} private void button2_Click(object sender, EventArgs e)
{
if (this.label1.Text.Length < )
{
MessageBox.Show("请先选择excel文件！");
return;
}
string sheetIndex = texBoxSheet.Text;
int sheetInt = ;
int.TryParse(sheetIndex, out sheetInt);
if (sheetInt < )
{
MessageBox.Show("sheet序号请填写整数！");
return;
} ReadFromExcelFile(this.label1.Text, sheetInt);
} private DataSet ExcelToDS(string Path)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel = "select * from [sheet1$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds, "table1");
return ds;
} private void TestExcelRead(string file)
{
try
{
using (ExcelHelper excelHelper = new ExcelHelper(file))
{
DataTable dt = excelHelper.ExcelToDataTable("MySheet", true);
int SE = dt.Rows.Count;
//PrintData(dt);
}
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
}
} private void ReadFromExcelFile(string filePath, int sheetInt)
{
IWorkbook wk = null;
string extension = System.IO.Path.GetExtension(filePath);
try
{
FileStream fs = File.OpenRead(filePath);
if (extension.Equals(".xls"))//xlsx xls
{
//把xls文件中的数据写入wk中
wk = new HSSFWorkbook(fs);
}
else
{
//把xlsx文件中的数据写入wk中
wk = new XSSFWorkbook(fs);
} fs.Close();
//读取当前表数据
string userCount = "";
Dictionary<string, List<string>> userDic = new Dictionary<string, List<string>>();
if (sheetInt > wk.NumberOfSheets)
{
MessageBox.Show("sheet序号填写超出范围！");
return;
}
//for (int k = 0; k < wk.NumberOfSheets; k++ )
//{ ISheet sheet = wk.GetSheetAt(sheetInt-); IRow row = sheet.GetRow(); //读取当前行数据
//LastRowNum 是当前表的总行数-1（注意）
int offset = ; for (int i = ; i <= sheet.LastRowNum; i++)
{
row = sheet.GetRow(i); //读取当前行数据
if (row != null)
{
string uxuhao = "", udanwei = "", ubumen = "", ubianhao = "", uname = "", uAka = "", uBka = "", ukai = "";
//LastCellNum 是当前行的总列数
for (int j = ; j < row.LastCellNum; j++)
{
//读取该行的第j列数据
string value = row.GetCell(j) == null ? "" : row.GetCell(j).ToString(); if (j == )
uxuhao = value;
else if (j == )
udanwei = value;
else if (j == )
ubumen = value;
else if (j == )
ubianhao = value;
else if (j == )
uname = value;
else if (j == )
uAka = value;
else if (j == )
uBka = value;
else if (j == )
{
ukai = value;
if(uxuhao != null)
{
if (uxuhao.Contains("计数"))
{
ukai = row.GetCell(j).NumericCellValue.ToString();
}
} } }
if (!(ubianhao.Equals("") || ubianhao.Equals("员工编号") || ubianhao.Contains("经办人")))
{
List<string> ulist = new List<string>();
ulist.Add(udanwei);
ulist.Add(ubumen);
ulist.Add(uname);
ulist.Add(ukai);
ulist.Add(uAka);
ulist.Add(uBka);
userDic.Add(ubianhao, ulist);
//Console.WriteLine("\n");
}
if (uxuhao != null)
{
if (uxuhao.Contains("计数"))
{
userCount = ukai;
break;
}
int uxuhaoInt = ;
int.TryParse(uxuhao, out uxuhaoInt);
if (userDic.Count > && uxuhaoInt == )
{
userCount = "没有计数";
break;
}
}
}
}
//} DoUser(userDic, filePath, userCount, sheetInt);
} catch (Exception e)
{
//只在Debug模式下才输出
Console.WriteLine(e.Message);
MessageBox.Show(e.Message);
}
} private void DoUser(Dictionary<string, List<string>> userDic, string filePath, string userCount, int sheetInt)
{
if (userDic.Count < )
return;
string namev = "";
string tip = "";
DataTable dtNew = new DataTable();
try
{
//
DataTable dt = GetDataBySQLNew("McsDW", "select * from UserViewAll_DW "); dtNew.Columns.Add("员工编号", Type.GetType("System.String"));
dtNew.Columns.Add("姓名", Type.GetType("System.String"));
dtNew.Columns.Add("A卡", Type.GetType("System.String"));
dtNew.Columns.Add("B卡", Type.GetType("System.String"));
dtNew.Columns.Add("开通餐卡", Type.GetType("System.String"));
dtNew.Columns.Add("备注", Type.GetType("System.String")); string sql = "", sqlabnull = "";
string remark = "";
int passCount = ;
DataTable dtNewABNull = dtNew.Clone(); foreach (var d in userDic)
{
//Console.Write(d.Key+"----"+d.Value);
//Console.WriteLine("\n");
//namev = d.Value;
remark = "";
bool isExcel = false;
DataRow[] rows = dt.Select("PostionLevel='" + d.Key + "'");
//if (d.Value[3].Trim().Equals("否"))
// continue;
//开通餐卡为空，默认为不开通
if (d.Value[].Trim().Equals(""))
{
//Console.WriteLine(d.Key + "----" + d.Value + "----开通餐卡为空");
//isExcel = true;
//remark = "开通餐卡为空!";
continue;
}
//开通餐卡，填的不是A,B
if (!(d.Value[].Trim().ToUpper().Equals("A") || d.Value[].Trim().ToUpper().Equals("B")))
{
isExcel = true;
remark += "开通餐卡项填写错误，填的不是A,B";
}
//如果开通A卡但是A卡为空,B卡不为空，也提示
else if (d.Value[].Trim().ToUpper().Equals("A") && d.Value[].Trim().Equals("") && !d.Value[].Trim().Equals(""))
{
isExcel = true;
remark += "需要开通A卡但是A卡为空，B卡不为空，请填B卡";
}
//如果开通B卡但是B卡为空，A卡不为空，也提示
else if (d.Value[].Trim().ToUpper().Equals("B") && d.Value[].Trim().Equals("") && !d.Value[].Trim().Equals(""))
{
isExcel = true;
remark += "需要开通B卡但是B卡为空，A卡不为空，请填A卡";
}
else if (rows == null)
{
isExcel = true;
remark += "数据库为空";
}
else if (rows.Length < )
{
isExcel = true;
remark += "数据库找不到该用户";
}
else
{
//判断用户名，中文名字
if (!(d.Value[].Trim().Equals(rows[]["UserDisplayName"].ToString().Trim())))
{
isExcel = true;
remark += "数据库里该用户名不匹配:" + rows[]["UserDisplayName"].ToString();
}
//如果开通A卡，判断A卡和数据库是否匹配
if (d.Value[].Trim().ToUpper().Equals("A") && !(d.Value[].Trim().Equals(rows[]["GatewayNumber"].ToString().Trim())))
{
isExcel = true;
remark += "开通A卡,但数据库里该用户A卡不匹配:" + rows[]["GatewayNumber"].ToString();
}
//如果开通B卡，判断B卡和数据库是否匹配
if (d.Value[].Trim().ToUpper().Equals("B") && !(d.Value[].Trim().Equals(rows[]["GatewayNumber2"].ToString().Trim())))
{
isExcel = true;
remark += "开通B卡,但数据库里该用户B卡不匹配:" + rows[]["GatewayNumber2"].ToString();
}
//string kastr = GetKa(rows[0]["GatewayNumber2"], rows[0]["GatewayNumber"]);
//if (kastr.Equals("") || kastr.Equals("没有卡号"))
//{
// isExcel = true;
// remark += "数据库里该用户卡号有误:" + kastr;
//}
} string Gender = GetGender(d.Key);
//如果A卡和B卡都为空，也提示
if (d.Value[].Trim().Equals("") && d.Value[].Trim().Equals(""))
{
string abnull = "A,B卡都为空，请尽快启动办卡流程";
dtNewABNull.Rows.Add(new object[] { d.Key, d.Value[], d.Value[], d.Value[], d.Value[], abnull }); sqlabnull += string.Format(" insert into UserGateWayNumberABNull(UnitName,DepartmentName,PostionLevel,UserName,GatewayNumber,ExcelFileName,AorBcard,Remark,Gender) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')",
d.Value[], d.Value[], d.Key, d.Value[], "", System.IO.Path.GetFileName(filePath), d.Value[], abnull, Gender);
passCount++;
continue;
} if (isExcel)
{
DataRow r = dtNew.NewRow();
r["员工编号"] = d.Key;
r["姓名"] = d.Value[];
r["A卡"] = d.Value[];
r["B卡"] = d.Value[];
r["开通餐卡"] = d.Value[];
r["备注"] = remark; dtNew.Rows.Add(r);
}
else
{
string gNum = "";
if (d.Value[].Trim().ToUpper().Equals("A"))
gNum = d.Value[];
else
gNum = d.Value[]; sql += string.Format(" insert into UserGateWayNumber(UnitName,DepartmentName,PostionLevel,UserName,GatewayNumber,ExcelFileName,AorBcard,Gender) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')",
d.Value[], d.Value[], d.Key, d.Value[], gNum, System.IO.Path.GetFileName(filePath), d.Value[], Gender);
passCount++;
}
}
//WriteToExcel(@"F:/web/设计院档案文档/用印不归档/员工卡号.xls");
int ucount =;
int.TryParse(userCount, out ucount);
if (dtNew.Rows.Count > )
{
//DataRow r = dtNew.NewRow();
//r["员工编号"] = filePath;
//r["姓名"] = "";
//r["A卡"] = "";
//r["B卡"] = "";
//r["开通餐卡"] = "";
//r["备注"] = ""; //dtNew.Rows.Add(r);
//加入AB都为空的用户
foreach(DataRow ro in dtNewABNull.Rows)
{
dtNew.Rows.Add(ro.ItemArray);
}
//加入excel名
dtNew.Rows.Add(new object[] { filePath, "", "", "", "", "" }); DataTableToExcel(dtNew);
tip = "总共处理了" + userDic.Count + "条数据，其中异常的有" + (dtNew.Rows.Count-).ToString() + "条。请查看D:/食堂餐卡统计.xls";
}
else if (ucount != passCount && !userCount.Equals("没有计数"))
{
tip = "总共处理了" + userDic.Count + "条数据，其中合格数据有" + passCount + "条， excel表中计数的有" + ucount + "条。数据不匹配，请核查！";
}
else
{
//GetDataBySQLNew("McsDW", sql + sqlabnull);
//tip = "总共处理了" + userDic.Count + "条数据，其中合格数据有" + (passCount - dtNewABNull.Rows.Count).ToString() + "条，数据已经录入到[McsDW].[dbo].[UserGateWayNumber]表中, AB卡都为空但是需要开通卡的用户数据有" + dtNewABNull.Rows.Count + "条，此数据录入到表UserGateWayNumberABNull";
}
}
catch(Exception e)
{
MessageBox.Show(e.Message + "--" + namev);
} Console.Write("总共"+userDic.Count);
MessageBox.Show(tip);
} private string GetGender(string pl)
{
string sql = "select Gender from [McsDW].[dbo].[UserViewAll_DW] where PostionLevel='" + pl + "'";
DataTable dt = GetDataBySQLNew("McsDW", sql);
if (dt == null || dt.Rows.Count < )
return "";
return dt.Rows[][] == null ? "" : dt.Rows[][].ToString();
} private string GetKa(object K2, object K1)
{
if (!(K2 == null || K2.Equals("")))
{
return K2.ToString();
}
else if (!(K1 == null || K1.Equals("")))
{
return K1.ToString();
}
else
return "没有卡号";
} private void WriteToExcel(string filePath)
{
//创建工作薄
IWorkbook wb;
string extension = System.IO.Path.GetExtension(filePath);
//根据指定的文件格式创建对应的类
if (extension.Equals(".xls"))
{
wb = new HSSFWorkbook();
}
else
{
wb = new XSSFWorkbook();
} ICellStyle style1 = wb.CreateCellStyle();//样式
style1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;//文字水平对齐方式
style1.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//文字垂直对齐方式
//设置边框
style1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style1.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style1.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
style1.WrapText = true;//自动换行 ICellStyle style2 = wb.CreateCellStyle();//样式
IFont font1 = wb.CreateFont();//字体
font1.FontName = "楷体";
font1.Color = HSSFColor.Red.Index;//字体颜色
font1.Boldweight = (short)FontBoldWeight.Normal;//字体加粗样式
style2.SetFont(font1);//样式里的字体设置具体的字体样式
//设置背景色
style2.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
style2.FillPattern = FillPattern.SolidForeground;
style2.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;//文字水平对齐方式
style2.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//文字垂直对齐方式 ICellStyle dateStyle = wb.CreateCellStyle();//样式
dateStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;//文字水平对齐方式
dateStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//文字垂直对齐方式
//设置数据显示格式
IDataFormat dataFormatCustom = wb.CreateDataFormat();
dateStyle.DataFormat = dataFormatCustom.GetFormat("yyyy-MM-dd HH:mm:ss"); //创建一个表单
ISheet sheet = wb.CreateSheet("Sheet0");
//设置列宽
int[] columnWidth = { , , , };
for (int i = ; i < columnWidth.Length; i++)
{
//设置列宽度，256*字符数，因为单位是1/256个字符
sheet.SetColumnWidth(i, * columnWidth[i]);
} //测试数据
int rowCount = , columnCount = ;
// object[,] data = {
// {"列0", "列1", "列2", "列3"},
// {"", 400, 5.2, 6.01},
// {"", true, "2014-07-02", DateTime.Now}
// //日期可以直接传字符串，NPOI会自动识别
// //如果是DateTime类型，则要设置CellStyle.DataFormat，否则会显示为数字
//};
object[,] data = {
{"列0", "列1", "列2", "列3"},
{"", , 5.2, 6.01}
}; IRow row;
ICell cell; for (int i = ; i < rowCount; i++)
{
row = sheet.CreateRow(i);//创建第i行
for (int j = ; j < columnCount; j++)
{
cell = row.CreateCell(j);//创建第j列
cell.CellStyle = j % == ? style1 : style2;
//根据数据类型设置不同类型的cell
object obj = data[i, j];
SetCellValue(cell, data[i, j]);
//如果是日期，则设置日期显示的格式
if (obj.GetType() == typeof(DateTime))
{
cell.CellStyle = dateStyle;
}
//如果要根据内容自动调整列宽，需要先setCellValue再调用
//sheet.AutoSizeColumn(j);
}
} //合并单元格，如果要合并的单元格中都有数据，只会保留左上角的
//CellRangeAddress(0, 2, 0, 0)，合并0-2行，0-0列的单元格
CellRangeAddress region = new CellRangeAddress(, , , );
sheet.AddMergedRegion(region); try
{
FileStream fs = File.OpenWrite(filePath);
wb.Write(fs);//向打开的这个Excel文件中写入表单并保存。
fs.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
private void SetCellValue(ICell cell, object obj)
{
if (obj.GetType() == typeof(int))
{
cell.SetCellValue((int)obj);
}
else if (obj.GetType() == typeof(double))
{
cell.SetCellValue((double)obj);
}
else if (obj.GetType() == typeof(IRichTextString))
{
cell.SetCellValue((IRichTextString)obj);
}
else if (obj.GetType() == typeof(string))
{
cell.SetCellValue(obj.ToString());
}
else if (obj.GetType() == typeof(DateTime))
{
cell.SetCellValue((DateTime)obj);
}
else if (obj.GetType() == typeof(bool))
{
cell.SetCellValue((bool)obj);
}
else
{
cell.SetCellValue(obj.ToString());
}
} private bool DataTableToExcel(DataTable dt)
{
bool result = false;
IWorkbook workbook = null;
FileStream fs = null;
IRow row = null;
ISheet sheet = null;
ICell cell = null;
try
{
if (dt != null && dt.Rows.Count > )
{
workbook = new HSSFWorkbook();
sheet = workbook.CreateSheet("Sheet0");//创建一个名称为Sheet0的表
int rowCount = dt.Rows.Count;//行数
int columnCount = dt.Columns.Count;//列数 //设置列头
row = sheet.CreateRow();//excel第一行设为列头
for (int c = ; c < columnCount; c++)
{
cell = row.CreateCell(c);
cell.SetCellValue(dt.Columns[c].ColumnName);
} //设置每行每列的单元格,
for (int i = ; i < rowCount; i++)
{
row = sheet.CreateRow(i + );
for (int j = ; j < columnCount; j++)
{
cell = row.CreateCell(j);//excel第二行开始写入数据
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
using (fs = File.OpenWrite(@"D:/食堂餐卡统计.xls"))
{
workbook.Write(fs);//向打开的这个xls文件中写入数据
result = true;
}
}
return result;
}
catch (Exception ex)
{
if (fs != null)
{
fs.Close();
}
return false;
}
} private DataTable GetDataBySQLNew(string database, string sql)
{
DataTable dt = new DataTable();
string strServer = "Server=127.0.0.0; DataBase=McsDW; user id=sqluser; password=Pass@word123;";
if (database.Equals("McsDW"))
{
strServer = "Server=127.0.0.0; DataBase=McsDW; user id=sqluser; password=Pass@word123;";
}
else if (database.Equals("McsSP"))
{
strServer = "Server=127.0.0.0; DataBase=McsSP; user id=sqluser; password=Pass@word123;";
}
using (System.Data.SqlClient.SqlConnection SqlCnn = new System.Data.SqlClient.SqlConnection(strServer))
{
SqlCnn.Open();
System.Data.SqlClient.SqlDataAdapter SqlDa = new System.Data.SqlClient.SqlDataAdapter(sql, SqlCnn);
DataSet DS = new DataSet();
SqlDa.Fill(DS);
SqlDa.Dispose();
SqlCnn.Close();
if (DS == null || DS.Tables == null || DS.Tables.Count < )
return null;
dt = DS.Tables[];
}
return dt;
}
需要下载npoi dll