C#导入、导出EXCEL方法整理
方法一、使用using System.Data.OleDb（该方法将EXCEL视同数据库，采用命令+参数的操作方式，使用时需要安装office或EXCEL驱动）
1、使用的命名空间为：using System.Data.OleDb;
* 2 连接数据库的字符串为：string myConn="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\目录.xls;Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\"";
* *.xsl可以认为是数据库了,HDR表示是否隐藏excel的第一行(因为第一行一般表示字段名称)
* IMEX 参数，因为不同的模式代表著不同的读写行为：当IMEX=0 时为“汇出模式”，这个模式开启的 Excel 档案只能用来做“写入”用途。
* 当 IMEX=1 时为“汇入模式”，这个模式开启的 Excel 档案只能用来做“读取”用途。当 IMEX=2 时为“连結模式”，这个模式开启的 Excel 档案可同时支援“读取”与“写入”用途
#region elxe数据导入数据库
/// <summary>
/// Excel数据导入Datable
/// </summary>
/// <param name="fileUrl">Excel文件完全路径</param>
/// <param name="Sheet">要查找的表单名，为空返回第一个表单</param>
/// <returns></returns>
static public DataTable GetExcelDatatable(string fileUrl, string Sheet)
{
OpenFileDialog dialog = new OpenFileDialog();
string file = "";
if (fileUrl != "")
file = fileUrl;
else
{
dialog.Multiselect = true; //该值确定是否可以选择多个文件 dialog.Title = "请选择文件夹";
dialog.Filter = "所有文件(*.*)|*.*";
if (dialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{ file = dialog.FileName; }
else { return null; }
}
//office2007之前 仅支持.xls
//const string cmdText = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;IMEX=1';";
//支持.xls和.xlsx，即包括office2010等版本的 HDR=Yes代表第一行是标题，不是数据；
const string cmdText = "Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
DataTable dt = null;
//建立连接（程序与Excel文件）
if (file == "" | !File.Exists(file))
{
MessageBox.Show("未选择文件或指定文件不存在！", "软件提示");
return null;
}
OleDbConnection conn = new OleDbConnection(string.Format(cmdText, file));
try
{
//打开连接
if (conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed)
{
conn.Open();
}
//获取Excel的所有表单
DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
//获取Excel的第一个Sheet名称
string sheetName = "";
if (Sheet != "")
sheetName = Sheet + "$";
else
sheetName = schemaTable.Rows[0]["TABLE_NAME"].ToString().Trim();
//查询sheet中的数据
string strSql = "select * from [" + sheetName + "]";
OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);
DataSet ds = new DataSet();
da.Fill(ds, "mapTable");
dt = ds.Tables[0];
return dt;
}
catch (Exception exc)
{
throw exc;
}
finally
{
conn.Close();
conn.Dispose();
}
}
/// <summary>
/// 判断数据库表是否存在。
/// </summary>
/// <param name="tablename">bhtsoft表</param>
/// <returns></returns>
public static bool CheckExistsTable(string tablename)
{
SQLiteCommand mDbCmd = conn.CreateCommand();
mDbCmd.CommandText = "SELECT COUNT(*) FROM sqlite_master where type='table' and name='" + tablename + "';";
if (0 == Convert.ToInt32(mDbCmd.ExecuteScalar()))
{
return false;
}
else
{
return true;
}
}
/// <summary>
/// 查找数据库表、生成带参数指令，批量修改数据
/// </summary>
/// <param name="tbName">数据库表名</param>
/// <param name="rowName">要操作的字段集</param>
/// <param name="rowValue">要操作字段的值</param>
/// <returns></returns>
static public int SqlCmd(string tbName, string[] rowName, string[] rowValue)
{
conn.Open();
string sqlInsert = "insert into " + tbName;
string cmdstrA = "";
int a = 0;
if (!CheckExistsTable(tbName))
{
MessageBox.Show("数据库中不存在对应表单！", "软件提示");
return a;
}
//向SQL指令中添加字段项
string sqlInsertA = "(";
for (int i = 0; i < rowName.Count(); i++)
{
sqlInsertA += rowName[i];
if (i == rowName.Count() - 1)
{
sqlInsertA += ")";
break;
}
sqlInsertA += ",";
}
//向SQL指令中添加字段值
string sqlInsertB = " VALUES(";
for (int i = 0; i < rowName.Count(); i++)
{
sqlInsertB += "@" + rowName[i];
if (i == rowName.Count() - 1)
{
sqlInsertB += ")";
break;
}
sqlInsertB += ",";
}
cmdstrA = sqlInsert + sqlInsertA + sqlInsertB;
//生成SQL指令参数
my_Cmd.Parameters.Clear();
for (int i = 0; i < rowName.Count(); i++)
{
my_Cmd.Parameters.AddWithValue("@" + rowName[i].ToString(), rowValue[i].Trim());
}
try
{
if (conn.State == ConnectionState.Open & cmdstrA != "")
{
a = ExecDataBySql(cmdstrA);
}
}
catch (SqlException ex)//捕获数据库异常
{
MessageBox.Show(ex.ToString());//输出异常信息
}
finally
{
conn.Close();//关闭数据库连接
}
return a;
}
/// <summary>
/// elxe数据导入数据库
/// </summary>
/// <param name="elxeName">要导入的elxe表名</param>
/// <param name="dbTableName"></param>
/// <returns></returns>
static public int InsetData(string elxeName, string dbTableName)
{
OpenFileDialog dialog = new OpenFileDialog();
string file = "";
if (elxeName != "")
file = elxeName;
else
{
dialog.Multiselect = true; //该值确定是否可以选择多个文件 dialog.Title = "请选择文件夹";
dialog.Filter = "所有文件(*.*)|*.*";
if (dialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{ file = dialog.FileName; }
else { return 0; }
}
DataTable dt = null;
//建立连接（程序与Excel文件）
if (file == "" | !File.Exists(file))
{
MessageBox.Show("未选择文件或指定文件不存在！", "软件提示");
return 0;
}
//string elxeNameA = "";
//if (elxeName != "")
// elxeNameA = path + elxeName;
//DataTable dt = GetExcelDatatable(elxeNameA, dbTableName);//path + @"\ExcelToDB.xlsx"
dt = Import(file);
int a = 0;
if (dt == null)
{
MessageBox.Show("数据读取失败！", "软件提示");
return a;
}
string[] rowName = new string[dt.Columns.Count];
string[] rowValue = new string[dt.Columns.Count];
string sqlDelete = "Delete from " + dbTableName;
//删除原数据
ExecDataBySql(sqlDelete);
//变量内存表中的所有行
foreach (DataRow dr in dt.Rows)
{
try
{
for (int i = 0; i < dt.Columns.Count; i++)
{
rowName[i] = dt.Columns[i].ColumnName;
rowValue[i] = dr.ItemArray[i].ToString();
}
a = SqlCmd(dbTableName, rowName, rowValue);
if (a <= 0)
{
MessageBox.Show("数据库表单修改失败！", "软件提示");
return a;
}
}
catch (Exception ex)
{
throw ex;
}
}
if (a > 0)
MessageBox.Show("数据导入完成！", "软件提示");
return a;
}
#endregion
#region 导出数据到EXCEL
static OleDbConnection ExConn;
static string connString;
static string FileName = path;
static string SheetName = "Sheet1";
/// <summary>
/// 打开连接
/// </summary>
static private void Open(string FileName)
{
if (ExConn == null)
{
//在此处设置访问的数据库文件
connString = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + FileName + ";Extended Properties='Excel 12.0; IMEX=0'";
//connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=Excel 8.0; IMEX=2'";
ExConn = new OleDbConnection(connString);
ExConn.Open();
}
else if (ExConn.State == System.Data.ConnectionState.Closed)
ExConn.Open();
}
/// <summary>
/// 关闭连接
/// </summary>
static public void Close()
{
if (ExConn != null)
ExConn.Close();
}
private void Choosepath()
{
SaveFileDialog SaveFile = new SaveFileDialog();
//SaveFile.FileName = saveFileName;
//设置文件类型
SaveFile.Filter = "Miscrosoft Office Excel 97-2003 工作表|*.xls|所有文件(*.*)|*.*";
//设置默认文件类型显示顺序
SaveFile.FilterIndex = 1;
//保存对话框是否记忆上次打开的目录
SaveFile.RestoreDirectory = true;
if (SaveFile.ShowDialog() == DialogResult.OK)
{
string localFilePath = SaveFile.FileName.ToString(); //获得文件路径
string fileNameExt = localFilePath.Substring(localFilePath.LastIndexOf("\\") + 1); //获取文件名，不带路径
DataBase.ImportData(DataBase.Sqlcmd_Datareader("SELECT * FROM tb_CGQ"), fileNameExt, "tb_CGQ");
System.Windows.MessageBox.Show("导出数据成功!", "系统信息");
//获取文件路径，不带文件名
//FilePath = localFilePath.Substring(0, localFilePath.LastIndexOf("\\"));
//给文件名前加上时间
//newFileName = DateTime.Now.ToString("yyyyMMdd") + fileNameExt;
//在文件名里加字符
//saveFileDialog1.FileName.Insert(1,"dameng");
//System.IO.FileStream fs = (System.IO.FileStream)sfd.OpenFile();//输出文件
}
}
/// <summary>
/// 导入数据到Excel
/// </summary>
/// <param name="OutTable"></param>
static public void ImportData(DataTable OutTable, string FileName, string SheetName)
{
SaveFileDialog SaveFile = new SaveFileDialog();
string localFilePath="", fileNameExt="", newFileName="", FilePath="";
//设置文件类型
SaveFile.Filter = "Miscrosoft Office Excel 97-2003 工作表|*.xls|Miscrosoft Office Excel 2007 工作表|*.xlsx|所有文件(*.*)|*.*";
//设置默认文件类型显示顺序
SaveFile.FilterIndex = 1;
//保存对话框是否记忆上次打开的目录
SaveFile.RestoreDirectory = true;
//设置默认文件名
SaveFile.FileName = FileName;
if (SaveFile.ShowDialog() == DialogResult.OK)
{
localFilePath = SaveFile.FileName.ToString(); //获得文件路径
fileNameExt = localFilePath.Substring(localFilePath.LastIndexOf("\\") + 1); //获取文件名，不带路径
// DataBase.ImportData(DataBase.Sqlcmd_Datareader("SELECT * FROM tb_CGQ"), fileNameExt, "tb_CGQ");
System.Windows.MessageBox.Show("导出数据成功!", "系统信息");
//获取文件路径，不带文件名
FilePath = localFilePath.Substring(0, localFilePath.LastIndexOf("\\"));
// SaveFile.DefaultExt
//给文件名前加上时间
//newFileName = DateTime.Now.ToString("yyyyMMdd") + fileNameExt;
//在文件名里加字符
//saveFileDialog1.FileName.Insert(1,"dameng");
//System.IO.FileStream fs = (System.IO.FileStream)sfd.OpenFile();//输出文件
}
StringBuilder sb = new StringBuilder();
//sb.Append(localFilePath);
//sb.Append(@"\");
//sb.Append(fileNameExt);
//sb.Append(".xlsx");
CreateExcel(OutTable, localFilePath.ToString(), SheetName);
InsertData(OutTable, localFilePath.ToString(), SheetName);
}
/// <summary>
/// 创建Excel文件和表头
/// </summary>
static private void CreateExcel(DataTable OutTable, string FileName, string SheetName)
{
//我们常常碰到字符串连接的情况，方便和直接的方式是通过"+"符号来实现，
//但是这种方式达到目的的效率比较低，且每执行一次都会创建一个String对象
//即耗时，又浪费空间。使用StringBuilder类就可以避免这种问题的发生
StringBuilder sb = new StringBuilder();
if (File.Exists(FileName))
{
File.Delete(FileName);
}
sb.Append("create table ");
sb.Append(SheetName);
sb.Append("(");
//遍历所有列
foreach (DataColumn col in OutTable.Columns)
{
//创建列：列名+数据类型
sb.Append(col.ColumnName + " varchar,");
}
//移除最后一个，号
sb.Remove(sb.Length - 1, 1);
sb.Append(")");
Open(FileName);
//创建SQL语句执行类
OleDbCommand OleCmd = new OleDbCommand();
OleCmd.Connection = ExConn;//创建连接
OleCmd.CommandText = sb.ToString();//解析指令字符串
OleCmd.ExecuteNonQuery();//执行指令
Close();
}
/// <summary>
/// 插入数据
/// </summary>
static private void InsertData(DataTable OutTable, string FileName, string SheetName)
{
OleDbCommand OleCmd = new OleDbCommand();
//创建OleDbCommand指令参数集
OleDbParameter[] parm = new OleDbParameter[OutTable.Columns.Count];
StringBuilder sb = new StringBuilder();
sb.Append("insert into ");
sb.Append(SheetName);
sb.Append(" values(");
for (int i = 0; i < OutTable.Columns.Count; i++)
{
parm[i] = new OleDbParameter("@P" + OutTable.Columns[i].ColumnName, OleDbType.VarChar);
sb.Append("@P" + OutTable.Columns[i].ColumnName + ",");
OleCmd.Parameters.Add(parm[i]);
}
sb.Remove(sb.Length - 1, 1);
sb.Append(")");
Open(FileName);
OleCmd.Connection = ExConn;
OleCmd.CommandText = sb.ToString();
foreach (DataRow row in OutTable.Rows)
{
for (int i = 0; i < OutTable.Columns.Count; i++)
{
parm[i].Value = row[i].ToString().Trim();
}
OleCmd.ExecuteNonQuery();
}
Close();
}
/// <summary>
/// 从Excel输出数据到数据集
/// </summary>
/// <returns></returns>
public DataSet OutPortData()
{
DataSet ds = new DataSet();
Open(FileName);
OleDbDataAdapter myAdapter = new OleDbDataAdapter("select * from [Sheet1$]", ExConn);
myAdapter.Fill(ds, "Input");
Close();
return ds;
}
#endregion
方法二：将Excle作为一个文件来处理
这种方法与文本文件及其他文件的处理方法一样，安装标准的类库调用对应的方法
这种方法使用时有一定局限性，要安装与引用库类相同版本的office或EXCEL驱动，且还要区分32位和64位系统
#region 生成Excle文件并保存到指定位置常规方式
/// <summary>
/// winform生成Excle文件并保存到指定位置
/// </summary>
/// <param name="dt">数据源</param>
/// <param name="saveFileName">生成Excel文件名</param>
public void ExportToExcel(DataTable dt, string saveFileName)
{
if (dt == null) return;
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
// lblMsg.Text = "无法创建Excel对象，可能您的机子未安装Excel";
MessageBox.Show("请确保您的电脑已经安装Excel", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1
Excel.Range range = null;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
//写入标题
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
range = (Excel.Range)worksheet.Cells[1, i + 1];
//range.Interior.ColorIndex = 15;//背景颜色
range.Font.Bold = true; //粗体
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //居中
//加边框
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin,
Excel.XlColorIndex.xlColorIndexAutomatic, null);
//range.ColumnWidth = 4.63;//设置列宽
//range.EntireColumn.AutoFit();//自动调整列宽
//r1.EntireRow.AutoFit();//自动调整行高
}
//写入内容
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
try
{
worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i];
range = (Excel.Range)worksheet.Cells[r + 2, i + 1];
range.Font.Size = 9; //字体大小
//加边框
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin,
Excel.XlColorIndex.xlColorIndexAutomatic, null);
range.EntireColumn.AutoFit(); //自动调整列宽
}
catch (Exception)
{
}
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
Application.DoEvents();
}
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
if (dt.Columns.Count > 1)
{
range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
}
try
{
workbook.Saved = true;
SaveFileDialog SaveFile = new SaveFileDialog();
SaveFile.FileName = saveFileName;
SaveFile.Filter = "Miscrosoft Office Excel 97-2003 工作表|*.xls|所有文件(*.*)|*.*";
SaveFile.RestoreDirectory = true;
if (SaveFile.ShowDialog() == DialogResult.OK)
{
workbook.SaveCopyAs(SaveFile.FileName);
MessageBox.Show("导出数据成功!", "系统信息");
}
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开!", "系统信息");
}
workbooks.Close();
if (xlApp != null)
{
xlApp.Workbooks.Close();
xlApp.Quit();
int generation = GC.GetGeneration(xlApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
GC.Collect(generation);
}
GC.Collect(); //强行销毁
#region 强行杀死最近打开的Excel进程
System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL");
DateTime startTime = new DateTime();
int m, killId = 0;
for (m = 0; m < excelProc.Length; m++)
{
if (startTime < excelProc[m].StartTime)
{
startTime = excelProc[m].StartTime;
killId = m;
}
}
if (excelProc[killId].HasExited == false)
{
excelProc[killId].Kill();
}
#endregion
}
#endregion
方法三;使用第三方插件NPOI
次插件可以以简单方式直接导出数据，也可以调用其中包括的方法以模块方式导出，代码都比较简单
最重要的是使用时不需要安装office或EXCEL驱动，编译后生成的EXE文件可在任何环境运行
#region 使用NPOI导出
/// <summary>
/// DataTable导出到Excel文件
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="strFileName">保存位置</param>
/// <param name="strSheetName">工作表名称</param>
/// <Author>CallmeYhz 2015-11-26 10:13:09</Author>
public static void Export(DataTable dtSource, string strHeaderText, string strSheetName)
{
if (strSheetName == "")
{
strSheetName = "Sheet";
}
#region 选择文件保存路径和文件名
SaveFileDialog SaveFile = new SaveFileDialog();
string localFilePath = "";
string[] oldColumnNames;
string[] newColumnNames;
if (dtSource == null)
{
System.Windows.MessageBox.Show("为找到要导出的数据表!", "系统信息");
return;
}
oldColumnNames = new string[dtSource.Columns.Count];
for (int i = 0; i < dtSource.Columns.Count; i++)
{
oldColumnNames[i]= dtSource.Columns[i].ColumnName.ToString();
}
newColumnNames = oldColumnNames;
//设置文件类型
SaveFile.Filter = "Miscrosoft Office Excel 97-2003 工作表|*.xls|Miscrosoft Office Excel 2007 工作表|*.xlsx|所有文件(*.*)|*.*";
//设置默认文件类型显示顺序
SaveFile.FilterIndex = 1;
//保存对话框是否记忆上次打开的目录
SaveFile.RestoreDirectory = true;
//设置默认文件名
SaveFile.FileName = FileName;
if (SaveFile.ShowDialog() == DialogResult.OK)
{
//获取文件路径，带文件名
localFilePath = SaveFile.FileName.ToString(); //获得文件路径
using (MemoryStream ms = Export(dtSource, strHeaderText, strSheetName, oldColumnNames, newColumnNames))
{
using (FileStream fs = new FileStream(localFilePath, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
}
System.Windows.MessageBox.Show("导出数据成功!", "系统信息");
}
#endregion
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// MemoryStream:文件流模板，可作为直接导入Excel的数据源
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="strSheetName">工作表名称</param>
/// <Author>CallmeYhz 2015-11-26 10:13:09</Author>
public static MemoryStream Export(DataTable dtSource, string strHeaderText, string strSheetName, string[] oldColumnNames, string[] newColumnNames)
{
if (oldColumnNames.Length != newColumnNames.Length)
{
return new MemoryStream();
}
HSSFWorkbook workbook = new HSSFWorkbook();//创建工作簿
//HSSFSheet sheet = workbook.CreateSheet();// workbook.CreateSheet();
ISheet sheet = workbook.CreateSheet(strSheetName);//创建工作表
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "文件作者信息"; //填加xls文件作者信息
si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
si.Comments = "作者信息"; //填加xls文件作者信息
si.Title = "标题信息"; //填加xls文件标题信息
si.Subject = "主题信息";//填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
#region 取得列宽
//int[] arrColWidth = new int[oldColumnNames.Length];
//for (int i = 0; i < oldColumnNames.Length; i++)
//{
// arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(newColumnNames[i]).Length;
//}
/*
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
* */
for (int i = 0; i < dtSource.Rows.Count; i++)
{
//for (int j = 0; j < oldColumnNames.Length; j++)
//{
// int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][oldColumnNames[j]].ToString()).Length;
// if (intTemp > arrColWidth[j])
// {
// arrColWidth[j] = intTemp;
// }
//}
/*
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
* */
}
#endregion
int rowIndex = 0;
//遍历DataTable表所有列
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet(strSheetName + ((int)rowIndex / 65535).ToString());
}
#region 表头及样式
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
//sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
}
#endregion
#region 列头及样式
{
//HSSFRow headerRow = sheet.CreateRow(1);
IRow headerRow = sheet.CreateRow(1);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
for (int i = 0; i < oldColumnNames.Length; i++)
{
headerRow.CreateCell(i).SetCellValue(newColumnNames[i]);
headerRow.GetCell(i).CellStyle = headStyle;
//设置列宽
//sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 256);
}
/*
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
* */
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
//foreach (DataColumn column in dtSource.Columns)
for (int i = 0; i < oldColumnNames.Length; i++)
{
ICell newCell = dataRow.CreateCell(i);
string drValue = row[oldColumnNames[i]].ToString();
switch (dtSource.Columns[oldColumnNames[i]].DataType.ToString())
{
case "System.String"://字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle;//格式化显示
break;
case "System.Boolean"://布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull"://空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet.Dispose();
sheet = null;
workbook = null;
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return ms;
}
}
/// <summary>
/// DataTable导出到Excel文件（无表头）另外的是有表头的
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="strFileName">保存位置</param>
/// <param name="strSheetName">工作表名称</param>
/// <Author>CallmeYhz 2015-11-26 10:13:09</Author>
public static void MyExport(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames)
{
if (strSheetName == "")
{
strSheetName = "Sheet";
}
MemoryStream getms = new MemoryStream();
#region 为getms赋值
if (oldColumnNames.Length != newColumnNames.Length)
{
getms = new MemoryStream();
}
HSSFWorkbook workbook = new HSSFWorkbook();
//HSSFSheet sheet = workbook.CreateSheet();// workbook.CreateSheet();
ISheet sheet = workbook.CreateSheet(strSheetName);
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "文件作者信息"; //填加xls文件作者信息
si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
si.Comments = "作者信息"; //填加xls文件作者信息
si.Title = "标题信息"; //填加xls文件标题信息
si.Subject = "主题信息";//填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
//HSSFCellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
#region 取得列宽
int[] arrColWidth = new int[oldColumnNames.Length];
for (int i = 0; i < oldColumnNames.Length; i++)
{
arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(newColumnNames[i]).Length;
}
/*
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
* */
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < oldColumnNames.Length; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][oldColumnNames[j]].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
/*
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
* */
}
#endregion
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet(strSheetName + ((int)rowIndex / 65535).ToString());
}
#region 列头及样式
{
//HSSFRow headerRow = sheet.CreateRow(1);
IRow headerRow = sheet.CreateRow(0);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
for (int i = 0; i < oldColumnNames.Length; i++)
{
headerRow.CreateCell(i).SetCellValue(newColumnNames[i]);
headerRow.GetCell(i).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 256);
}
/*
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
* */
}
#endregion
rowIndex = 1;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
//foreach (DataColumn column in dtSource.Columns)
for (int i = 0; i < oldColumnNames.Length; i++)
{
ICell newCell = dataRow.CreateCell(i);
string drValue = row[oldColumnNames[i]].ToString();
switch (dtSource.Columns[oldColumnNames[i]].DataType.ToString())
{
case "System.String"://字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle;//格式化显示
break;
case "System.Boolean"://布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull"://空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet.Dispose();
sheet = null;
workbook = null;
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
getms = ms;
}
#endregion
using (MemoryStream ms = getms)
{
using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
}
}
#endregion
#region 使用NPOI导入
/// <summary>读取excel
/// 默认第一行为表头，导入第一个工作表
/// </summary>
/// <param name="strFileName">excel文档路径</param>
/// <returns></returns>
public static DataTable Import(string strFileName)
{
DataTable dt = new DataTable();
HSSFWorkbook hssfworkbook;
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
IRow headerRow = sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
ICell cell = headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
dataRow[j] = row.GetCell(j).ToString();
}
dt.Rows.Add(dataRow);
}
return dt;
}
/// <summary>
/// 从Excel中获取数据到DataTable
/// </summary>
/// <param name="strFileName">Excel文件全路径(服务器路径)</param>
/// <param name="SheetName">要获取数据的工作表名称</param>
/// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
/// <returns></returns>
public static DataTable RenderDataTableFromExcel(string strFileName, string SheetName, int HeaderRowIndex)
{
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
IWorkbook workbook = new HSSFWorkbook(file);
ISheet sheet = workbook.GetSheet(SheetName);
return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);
}
}
/// <summary>
/// 从Excel中获取数据到DataTable
/// </summary>
/// <param name="strFileName">Excel文件全路径(服务器路径)</param>
/// <param name="SheetIndex">要获取数据的工作表序号(从0开始)</param>
/// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
/// <returns></returns>
public static DataTable RenderDataTableFromExcel(string strFileName, int SheetIndex, int HeaderRowIndex)
{
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
IWorkbook workbook = new HSSFWorkbook(file);
string SheetName = workbook.GetSheetName(SheetIndex);
return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);
}
}
/// <summary>
/// 从Excel中获取数据到DataTable
/// </summary>
/// <param name="ExcelFileStream">Excel文件流</param>
/// <param name="SheetName">要获取数据的工作表名称</param>
/// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
/// <returns></returns>
public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, string SheetName, int HeaderRowIndex)
{
IWorkbook workbook = new HSSFWorkbook(ExcelFileStream);
ExcelFileStream.Close();
return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);
}
/// <summary>
/// 从Excel中获取数据到DataTable
/// </summary>
/// <param name="ExcelFileStream">Excel文件流</param>
/// <param name="SheetIndex">要获取数据的工作表序号(从0开始)</param>
/// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
/// <returns></returns>
public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex)
{
IWorkbook workbook = new HSSFWorkbook(ExcelFileStream);
ExcelFileStream.Close();
string SheetName = workbook.GetSheetName(SheetIndex);
return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);
}
/// <summary>
/// 从Excel中获取数据到DataTable
/// </summary>
/// <param name="workbook">要处理的工作薄</param>
/// <param name="SheetName">要获取数据的工作表名称</param>
/// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
/// <returns></returns>
public static DataTable RenderDataTableFromExcel(IWorkbook workbook, string SheetName, int HeaderRowIndex)
{
ISheet sheet = workbook.GetSheet(SheetName);
DataTable table = new DataTable();
try
{
IRow headerRow = sheet.GetRow(HeaderRowIndex);
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
int rowCount = sheet.LastRowNum;
#region 循环各行各列,写入数据到DataTable
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
ICell cell = row.GetCell(j);
if (cell == null)
{
dataRow[j] = null;
}
else
{
//dataRow[j] = cell.ToString();
switch (cell.CellType)
{
case CellType.Blank:
dataRow[j] = null;
break;
case CellType.Boolean:
dataRow[j] = cell.BooleanCellValue;
break;
case CellType.Numeric:
dataRow[j] = cell.ToString();
break;
case CellType.String:
dataRow[j] = cell.StringCellValue;
break;
case CellType.Error:
dataRow[j] = cell.ErrorCellValue;
break;
case CellType.Formula:
default:
dataRow[j] = "=" + cell.CellFormula;
break;
}
}
}
table.Rows.Add(dataRow);
//dataRow[j] = row.GetCell(j).ToString();
}
#endregion
}
catch (System.Exception ex)
{
table.Clear();
table.Columns.Clear();
table.Columns.Add("出错了");
DataRow dr = table.NewRow();
dr[0] = ex.Message;
table.Rows.Add(dr);
return table;
}
finally
{
//sheet.Dispose();
workbook = null;
sheet = null;
}
#region 清除最后的空行
for (int i = table.Rows.Count - 1; i > 0; i--)
{
bool isnull = true;
for (int j = 0; j < table.Columns.Count; j++)
{
if (table.Rows[i][j] != null)
{
if (table.Rows[i][j].ToString() != "")
{
isnull = false;
break;
}
}
}
if (isnull)
{
table.Rows[i].Delete();
}
}
#endregion
return table;
}
#endregion
