excel向mysql写入数据_数据库向Excel写入数据

///

/// 打开目标数据录入信息Excel文件

///

/// 函数处理失败的错误信息

/// 如果函数处理成功返回true，否则返回false

private bool OpenDesSjlrExcel(string excelFile, ErrInfo pErrInfo)

{

bool pFlagOK = true;  //处理标志，表示是否处理正确

short pErrPos = 0;

bool pOccurredException = false;//是否发生了异常错误

try

{

string templateFile = AppDomain.CurrentDomain.BaseDirectory + "转化生成的信息录入数据.xlsx";

if (File.Exists(templateFile) == false)

{

pFlagOK = false;

pErrInfo.Content.Append("模板文件丢失: " + templateFile);

MessageBox.Show(pErrInfo.toShortString(), "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);

}

else

{

File.Copy(templateFile, excelFile);

File.SetAttributes(excelFile, FileAttributes.Normal);

}

if (pFlagOK)

{

if (xlApp1 == null)

{

pFlagOK = false;

pErrInfo.Content.Append("Excel对象非法为空");

MessageBox.Show(pErrInfo.toShortString(), "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);

}

else

{

xlApp1.DefaultFilePath = "";

xlApp1.DisplayAlerts = true;

xlApp1.SheetsInNewWorkbook = 1;

xlBook1 = xlApp1.Workbooks.Open(excelFile, Missing.Value, Missing.Value, Missing.Value, Missing.Value

, Missing.Value, Missing.Value, Missing.Value, Missing.Value

, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

ws1 = (Microsoft.Office.Interop.Excel.Worksheet)xlBook1.Worksheets[1];

ws1.Name = "一级";

//创建列标题

ws1.Cells[1, 1] = "档案编号";

ws1.Cells[1, 2] = "姓名";

ws1.Cells[1, 3] = "单位名称";

ws1.Cells[1, 4] = "单位简称";

ws1.Cells[1, 5] = "旧身份证号";

ws1.Cells[1, 6] = "身份证号";

ws2 = (Microsoft.Office.Interop.Excel.Worksheet)xlBook1.Worksheets[2];

ws2.Name = "二级";

//创建列标题

ws2.Cells[1, 1] = "档案编号";

ws2.Cells[1, 2] = "编号";

ws2.Cells[1, 3] = "材料名称";

ws2.Cells[1, 4] = "材料制成时间";

ws2.Cells[1, 5] = "页数";

ws2.Cells[1, 6] = "备注";

}

}

}

catch (Exception ex)

{

pFlagOK = false;

pErrInfo.OccurredException = ex;

pErrInfo.Content.Append(ex.Message);

pOccurredException = true;//发生了异常错误

}

finally

{

//如果处理失败，并且有错误信息那么就把错误信息拼接上该函数的名字标识，便于主程序查找分析异常错误的位置

if (!pFlagOK && pErrInfo.Content.Length > 0)

{

//获取当前函数名称

string className = System.Reflection.MethodBase.GetCurrentMethod().ReflectedType.FullName;

string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;

StringBuilder tempBuilder = new StringBuilder();

//用当前命名空间名.类名.函数名-->·错误信息的格式返回错误描述

tempBuilder.Append(className + "." + methodName + "-->");

//如果属于异常错误，则需要在错误信息中加入错误位置标记信息

if (pOccurredException)

{

tempBuilder.Append(" ErrPos: ");

tempBuilder.Append(pErrPos);

tempBuilder.Append(", ");

}

pErrInfo.Content.Insert(0, tempBuilder.ToString());

tempBuilder = null;

}

}

//返回函数处理结果给外部的函