#region 导入、导出方法
/// <summary>
/// DataGridView导出Excel(工作区域保护)
/// </summary>
/// <param name="strCaption">Excel文件中的标题</param>
/// <param name="fileName">文件名</param>
/// <param name="noLockColumnNames">不需要保护的列明</param>
/// <returns>返回信息,空为成功</returns>
public string ExportExcel(string strCaption, string fileName, string[] noLockColumnNames)
{
string result = "";
// 列索引,行索引,总列数,总行数
int ColIndex = 0;
int RowIndex = 0;
int ColCount = this.ColumnCount;
int RowCount = this.RowCount;
if (this.RowCount == 0)
{
result = "无记录";
}
// 创建Excel对象
Excel.Application xlApp = new Excel.ApplicationClass();
if (xlApp == null)
{
result = "Excel无法启动";
}
try
{
// 创建Excel工作薄
Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[1];
// 设置标题
Excel.Range range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, ColCount]); //标题所占的单元格数与DataGridView中的列数相同
range.MergeCells = true;
xlApp.ActiveCell.FormulaR1C1 = strCaption;
xlApp.ActiveCell.Font.Size = 20;
xlApp.ActiveCell.Font.Bold = true;
xlApp.ActiveCell.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
// 创建缓存数据
object[,] objData = new object[RowCount + 1, ColCount];
//获取列标题
foreach (DataGridViewColumn col in this.Columns)
{
objData[RowIndex, ColIndex++] = col.HeaderText;
}
// 获取数据
for (RowIndex = 1; RowIndex <= RowCount; RowIndex++)
{
for (ColIndex = 0; ColIndex < ColCount; ColIndex++)
{
if (noLockColumnNames != null )
{
if (noLockColumnNames.Length != 0)
{
Array.Sort(noLockColumnNames);
int num = Array.BinarySearch(noLockColumnNames, this.Columns[ColIndex].Name);
if (num >= 0)
{
xlSheet.get_Range(xlApp.Cells[3, ColIndex + 1], xlApp.Cells[RowCount + 2, ColIndex + 1]).Locked = false;
}
}
}
if (this[ColIndex, RowIndex - 1].ValueType == typeof(string)
|| this[ColIndex, RowIndex - 1].ValueType == typeof(DateTime))//这里就是验证DataGridView单元格中的类型,如果是string或是DataTime类型,则在放入缓存时在该内容前加入" ";
{
objData[RowIndex, ColIndex] = "" + this[ColIndex, RowIndex - 1].Value;
}
else
{
objData[RowIndex, ColIndex] = this[ColIndex, RowIndex - 1].Value;
}
//设置隐藏列
if (this[ColIndex, RowIndex - 1].Visible == false)
{
Excel.Range range2 = xlSheet.get_Range(xlApp.Cells[1, ColIndex + 1], xlApp.Cells[100, ColIndex + 1]);
range2.EntireColumn.Hidden = true;
//range2.EntireColumn.ColumnWidth = 0;
}
else
{
//Excel.Range range2 = xlSheet.get_Range(xlApp.Cells[1, ColIndex + 1], xlApp.Cells[100, ColIndex + 1]);
//range2.Columns.EntireColumn.AutoFit();
//xlApp.Columns.EntireColumn.AutoFit();
}
}
System.Windows.Forms.Application.DoEvents();
}
// 写入Excel
range = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, ColCount]);
range.Value2 = objData;
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);//加黑框
if (this.Rows.Count > 0)
{
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
}
if (this.Columns.Count > 1)
{
range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
}
range.Select();
xlSheet.Columns.EntireColumn.AutoFit();
// 获取数据
for (RowIndex = 1; RowIndex <= RowCount; RowIndex++)
{
for (ColIndex = 0; ColIndex < ColCount; ColIndex++)
{
//设置隐藏列
if (this[ColIndex, RowIndex - 1].Visible == false)
{
Excel.Range range2 = xlSheet.get_Range(xlApp.Cells[1, ColIndex + 1], xlApp.Cells[100, ColIndex + 1]);
range2.EntireColumn.Hidden = true;
//range2.EntireColumn.ColumnWidth = 0;
}
}
}
xlSheet.Protect("scmapp@cppei",//1Password
true, //2保护形状
true, //3
true, //4
Type.Missing, //5
true, //6
true, //7
true,//8
false, //9
false, //10
false,//11
false,//12
false, //13
false, //14
false, //15
true //16
);
xlBook.Saved = true;
xlBook.SaveCopyAs(fileName);
}
catch (Exception err)
{
result = err.ToString();
}
finally
{
xlApp.Quit();
GC.Collect(); //强制回收
}
return result;
}
/// <summary>
/// DataGridView导出Excel(无保护)
/// </summary>
/// <param name="strCaption">Excel文件中的标题</param>
/// <param name="fileName">文件名</param>
/// <returns>返回信息,空为成功</returns>
public string ExportExcel(string strCaption, string fileName)
{
string[] colList = { };
return ExportExcel(strCaption, fileName, colList);
}
/// <summary>
/// 导入方法
/// </summary>
/// <returns></returns>
public string ImportExcel(DataTable dt)
{
string fileName;
string _ReturnMessage = "";
OpenFileDialog fd = new OpenFileDialog();
fd.Filter = "xls files (*.xls)|*.xls|xlsx files (*.xlsx)|*.xlsx";
fd.Multiselect = false; //每次选择一个
fd.RestoreDirectory = true; //保存上一次的路径
fd.ValidateNames = true; //检查文件名的有效性
fd.CheckFileExists = true; //检查文件存在否
fd.CheckPathExists = true; //检查文件路径存在否
if (fd.ShowDialog() != DialogResult.OK)
{
return null;
}
fileName = fd.FileName;
#region 准备EXCEL
//判断是否安装EXCEL
Excel.Application xlApp = new Excel.ApplicationClass();
if (xlApp == null)
{
_ReturnMessage = "无法创建Excel对象,可能您的计算机未安装Excel";
return null;
}
//判断文件是否被其他进程使用
Excel.Workbook workbook;
try
{
workbook = xlApp.Workbooks.Open(fileName, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, 1, 0);
}
catch
{
_ReturnMessage = "Excel文件处于打开状态,请保存关闭";
return null;
}
//获得所有Sheet名称
int n = workbook.Worksheets.Count;
string[] SheetSet = new string[n];
System.Collections.ArrayList al = new System.Collections.ArrayList();
for (int i = 1; i <= n; i++)
{
SheetSet[i - 1] = ((Excel.Worksheet)workbook.Worksheets[i]).Name;
}
//释放Excel相关对象
workbook.Close(null, null, null);
xlApp.Quit();
if (workbook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
}
if (xlApp != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
}
GC.Collect();
#endregion
//把EXCEL导入到DataSet
DataSet ds = new DataSet();
for (int i = 0; i < this.Columns.Count; i++)
{
try
{
dt.Columns.Add(this.Columns[i].DataPropertyName);
}
catch (System.Exception ex)
{
}
}
bool openconnsuccess = true;
string connStr = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + fileName + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";
//HDR=YES 有两个值:YES/NO,表示第一行是否字段名,默认是YES,第一行是字段名 . IMEX=1 解决数字与字符混合时,识别不正常的情况=1表示所有数据按照字符处理
//string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;HDR=YES\";";//打开2007
OleDbConnection conn = new OleDbConnection(connStr);
try
{
conn.Open();
}
catch (System.Exception ex)
{
openconnsuccess = false;
}
if (!openconnsuccess)
{
try
{
connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1';";
conn = new OleDbConnection(connStr);
conn.Open();
openconnsuccess = true;
}
catch (System.Exception ex1)
{
}
}
if (!openconnsuccess)
{
try
{
connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 14.0 Xml;HDR=YES;IMEX=1';";
conn = new OleDbConnection(connStr);
conn.Open();
openconnsuccess = true;
}
catch (System.Exception ex1)
{
}
}
if (!openconnsuccess)
{
MessageBox.Show("与excel连接失败,请检查是否正确安装了OFFICE excel");
return null;
}
using (conn)
{
//OleDbDataAdapter da;
int row = 0;
//for(int i=1; i<=n; i++)
//{
int[] indx = new int[dt.Columns.Count];
OleDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from [" + SheetSet[0] + "$] ";//从第一个表中读取数据
OleDbDataReader odr = cmd.ExecuteReader();
while (odr.Read())
{
row++;
if (row == 1) continue;
if (odr[0].ToString() == string.Empty && odr[1].ToString() == string.Empty) continue;
if (row > this.Rows.Count + 1) break; ;
object[] drow = new object[dt.Columns.Count];
for (int j = 0; j < dt.Columns.Count; j++)
{
//drow[j] = odr[indx[j]];
drow[j] = odr[j];
}
dt.Rows.Add(drow);
}
odr.Close();
conn.Close();
conn.Dispose();
}
return _ReturnMessage;
}
#endregion
Winform导入导出方法总结
最新推荐文章于 2024-06-26 12:47:17 发布