C# Excel 操作

Excel数据到datagridview 里面 （流读取）

 System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog();
           
           
            if (fd.ShowDialog() == DialogResult.OK)
            {
                 DataTable DT = Table(fd.FileName);

                  int CellCount = DT.Columns.Count;
                  int RowCount = DT.Rows.Count;
                 List<string> list = new List<string>();
                 for (int a = 0; a <= CellCount - 1; a++)
                 { 
                 list.Add(DT.Rows[0][a].ToString());
                 } 
                 dataGridViewX1.DataSource = DT;
                 DT.Rows.RemoveAt(0);

                 for (int b = 0; b < CellCount; b++)
                 {

                     dataGridViewX1.Columns[b].HeaderCell.Value = list[b];
                     dataGridViewX1.Columns[b].Name = list[b];

                 }
                 DevComponents.DotNetBar.Controls.DataGridViewX Da = new DevComponents.DotNetBar.Controls.DataGridViewX();
 
            }

    public DataTable Table(string ST)
        {
           // string strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ST + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'";
            string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ST + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'";
            OleDbConnection myConn = new OleDbConnection(strCon);
            string strCom = " SELECT * FROM [Sheet1$] ";
            myConn.Open();

            OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);

            DataSet myDataSet = new DataSet();

            myCommand.Fill(myDataSet, "[Sheet1$]");

            myConn.Close();
            return myDataSet.Tables[0];


        }
   

 C#  datagridview 数据到Excel 流导出

    DataTable DT = new DataTable();
            DataRow DR = DT.NewRow();
            for(int a = 0 ;a<dataGridViewX1.Columns.Count;a++)
            {
            DT.Columns.Add(dataGridViewX1.Columns[a].HeaderCell.Value.ToString(),Type.GetType("System.String"));
            }
            int RowCount = dataGridViewX1.Rows.Count;
            int CellCount = dataGridViewX1.Columns.Count;
            for (int a = 0; a < RowCount-1; a++)
            {//新实例化数据
                DR = DT.NewRow();
                
                for (int b = 0; b < CellCount; b++)
                {
                  // dataRow 构建行数据 构建一条添加一条
                   DR[b] = dataGridViewX1.Rows[a].Cells[b].Value.ToString();
                }
                DT.Rows.Add(DR);
                
            }
            //dataGridViewX1.Rows.Clear();

            DataGridViewExportToExcel(DT, "导出Excel");
        }

 public void DataGridViewExportToExcel(DataTable Tab, string strTitle)
        {
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.Filter = "Excel files (*.xls)|*.xls";
            saveFileDialog.FilterIndex = 0;
            saveFileDialog.RestoreDirectory = true;
            saveFileDialog.CreatePrompt = false;
            saveFileDialog.FileName = strTitle + ".xls";
            if (saveFileDialog.ShowDialog() == DialogResult.Cancel) //导出时，点击【取消】按钮
            {
                return;
            }
            Stream myStream = saveFileDialog.OpenFile();
            StreamWriter sw = new StreamWriter(myStream,System.Text.Encoding.GetEncoding(-0));
            string strHeaderText = "";
            try
            {
                //写标题
                for (int i = 0; i < Tab.Columns.Count; i++)
                {
                    if (i > 0)
                    {
                        strHeaderText += "	";
                    }
                    strHeaderText += Tab.Columns[i].ToString();
                }
                sw.WriteLine(strHeaderText);
                //写内容
                string strItemValue = "";
                for (int j = 0; j < Tab.Rows.Count; j++)
                {
                    strItemValue = "";
                    for (int k = 0; k < Tab.Columns.Count; k++)
                    {
                        if (k > 0)
                        {
                            strItemValue += "	";
                        }
                        strItemValue += Tab.Rows[j][k].ToString();
                    }
            sw.WriteLine(strItemValue); //把dgv的每一行的信息写为sw的每一行
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "软件提示");
                throw ex;
            }
            finally
            {
                sw.Close();
                myStream.Close();
            }
        }

 导出Excel （worksheet）

     public void Exportdatagridviewtoexcel(DataGridView mydgv)
        {
            if (mydgv.Rows.Count == 0)
            {
                MessageBox.Show(" 没有数据可供导出！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            else
            {
                SaveFileDialog savedialog = new SaveFileDialog();
                savedialog.DefaultExt = "xlsx";
                savedialog.Filter = "microsoft office execl files (*.xlsx)|*.xlsx";
                savedialog.FilterIndex = 0;
                savedialog.RestoreDirectory = true;
                savedialog.Title = "导出数据到excel表格";
                savedialog.ShowDialog();
                if (savedialog.FileName.IndexOf(":") < 0) return; //被点了取消  
                //Microsoft.office.interop.excel.application xlapp = new microsoft.office.interop.excel.application();
                Microsoft.Office.Interop.Excel.Application xlapp = new Microsoft.Office.Interop.Excel.Application();
                if (xlapp == null)
                {
                    MessageBox.Show("可能您的机子未安装excel，无法创建excel对象！", "系统提示 ", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }

                Microsoft.Office.Interop.Excel.Workbooks workbooks = xlapp.Workbooks;
                Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
                Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1  
                //定义表格内数据的行数和列数   
                int rowscount = mydgv.Rows.Count;
                int colscount = mydgv.Columns.Count;
                //行数不可以大于65536   
                if (rowscount > 65536)
                {
                    MessageBox.Show("数据行记录超过65536行，不能保存！", "系统提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
                //列数不可以大于255   
                if (colscount > 256)
                {
                    MessageBox.Show("数据列记录超过256列，不能保存！", "系统提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
                //写入大标题
                string text = US_GV.UserDeptName + "—库存初始化报表";

                worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[2, 10]).MergeCells = true;
                worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[2, 10]).Font.Size = 17;
                worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[2, 10]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
                worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[3, 10]).Font.Bold = true;
               
                    worksheet.Cells[1, 1] = text;
                //写入列名
                for (int i = 0; i < mydgv.ColumnCount; i++)
                {   
                    worksheet.Cells[3, i + 1] = mydgv.Columns[i].HeaderText;
                }
                //写入数值
                for (int r = 0; r < mydgv.Rows.Count; r++)
                {
                    for (int i = 0; i < mydgv.ColumnCount; i++)
                    {
                        if (mydgv[i, r].ValueType == typeof(string))
                        {

                            worksheet.Cells[r + 4, i + 1] = "" + mydgv.Rows[r].Cells[i].Value.ToString().Replace(" ", ""); ;//将长数值转换成文本
                        }
                        else
                        {
                            worksheet.Cells[r + 4, i + 1] = mydgv.Rows[r].Cells[i].Value.ToString().Replace(" ", ""); ;
                        }
                    }
                    System.Windows.Forms.Application.DoEvents();
                }
                worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
                if (savedialog.FileName != "")
                {
                    try
                    {
                        workbook.Saved = true;
                        workbook.SaveCopyAs(savedialog.FileName);
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("导出文件时出错,文件可能正被打开！..." + ex.Message, "系统提示 ", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }

                }
                //GC.Collect();//强行销毁  
                MessageBox.Show("数据导出成功！ ", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);

            }
        }

导出Excel 属性设置

/// <summary>
/// 单元格背景色及填充方式
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">结束行</param>
/// <param name="endColumn">结束列</param>
/// <param name="color">颜色索引</param>
public void CellsBackColor(int startRow, int startColumn, int endRow, int endColumn, ColorIndex color)
{
Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);
range.Interior.ColorIndex = color;
range.Interior.Pattern = Pattern.Solid;
}

/// <summary>
/// 单元格背景色及填充方式
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">结束行</param>
/// <param name="endColumn">结束列</param>
/// <param name="color">颜色索引</param>
/// <param name="pattern">填充方式</param>
public void CellsBackColor(int startRow, int startColumn, int endRow, int endColumn, ColorIndex color, Pattern pattern)
{
Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);
range.Interior.ColorIndex = color;
range.Interior.Pattern = pattern;
}

/// <summary>
/// 设置行高
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="endRow">结束行</param>
/// <param name="height">行高</param>
public void SetRowHeight(int startRow, int endRow, int height)
{
//获取当前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Rows[startRow.ToString() + ":" + endRow.ToString(), System.Type.Missing];
range.RowHeight = height;
}

/// <summary>
/// 自动调整行高
/// </summary>
/// <param name="columnNum">列号</param>
public void RowAutoFit(int rowNum)
{
//获取当前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Rows[rowNum.ToString() + ":" + rowNum.ToString(), System.Type.Missing];
range.EntireColumn.AutoFit();

}

/// <summary>
/// 设置列宽
/// </summary>
/// <param name="startColumn">起始列(列对应的字母)</param>
/// <param name="endColumn">结束列(列对应的字母)</param>
/// <param name="width"></param>
public void SetColumnWidth(string startColumn, string endColumn, int width)
{
//获取当前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Columns[startColumn + ":" + endColumn, System.Type.Missing];
range.ColumnWidth = width;
}

/// <summary>
/// 设置列宽
/// </summary>
/// <param name="startColumn">起始列</param>
/// <param name="endColumn">结束列</param>
/// <param name="width"></param>
public void SetColumnWidth(int startColumn, int endColumn, int width)
{
string strStartColumn = GetColumnName(startColumn);
string strEndColumn = GetColumnName(endColumn);
//获取当前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Columns[strStartColumn + ":" + strEndColumn, System.Type.Missing];
range.ColumnWidth = width;
}

/// <summary>
/// 自动调整列宽
/// </summary>
/// <param name="columnNum">列号</param>
public void ColumnAutoFit(string column)
{
//获取当前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Columns[column + ":" + column, System.Type.Missing];
range.EntireColumn.AutoFit();

}

/// <summary>
/// 自动调整列宽
/// </summary>
/// <param name="columnNum">列号</param>
public void ColumnAutoFit(int columnNum)
{
string strcolumnNum = GetColumnName(columnNum);
//获取当前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Columns[strcolumnNum + ":" + strcolumnNum, System.Type.Missing];
range.EntireColumn.AutoFit();

}

/// <summary>
/// 字体颜色
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">结束行</param>
/// <param name="endColumn">结束列</param>
/// <param name="color">颜色索引</param>
public void FontColor(int startRow, int startColumn, int endRow, int endColumn, ColorIndex color)
{
Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);
range.Font.ColorIndex = color;
}

/// <summary>
/// 字体样式(加粗,斜体,下划线)
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">结束行</param>
/// <param name="endColumn">结束列</param>
/// <param name="isBold">是否加粗</param>
/// <param name="isItalic">是否斜体</param>
/// <param name="underline">下划线类型</param>
public void FontStyle(int startRow, int startColumn, int endRow, int endColumn, bool isBold, bool isItalic, UnderlineStyle underline)
{
Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);
range.Font.Bold = isBold;
range.Font.Underline = underline;
range.Font.Italic = isItalic;
}

/// <summary>
/// 单元格字体及大小
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">结束行</param>
/// <param name="endColumn">结束列</param>
/// <param name="fontName">字体名称</param>
/// <param name="fontSize">字体大小</param>
public void FontNameSize(int startRow, int startColumn, int endRow, int endColumn,string fontName, int fontSize)
{
Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);
range.Font.Name = fontName;
range.Font.Size = fontSize;
}

/// <summary>
/// 打开一个存在的Excel文件
/// </summary>
/// <param name="fileName">Excel完整路径加文件名</param>
public void Open(string fileName)
{
myExcel = new Excel.Application();
myWorkBook = myExcel.Workbooks.Add(fileName);
myFileName = fileName;
}

/// <summary>
/// 保存Excel
/// </summary>
/// <returns>保存成功返回True</returns>
public bool Save()
{
if (myFileName == "")
{
return false;
}
else
{
try
{
myWorkBook.Save();
return true;
}
catch (Exception ex)
{
return false;
}
}
}

/// <summary>
/// Excel文档另存为
/// </summary>
/// <param name="fileName">保存完整路径加文件名</param>
/// <returns>保存成功返回True</returns>
public bool SaveAs(string fileName)
{
try
{
myWorkBook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
return true;

}
catch (Exception ex)
{
return false;

}
}

/// <summary>
/// 关闭Excel
/// </summary>
public void Close()
{
myWorkBook.Close(Type.Missing, Type.Missing, Type.Missing);
myExcel.Quit();
myWorkBook = null;
myExcel = null;
GC.Collect();
}

/// <summary>
/// 关闭Excel
/// </summary>
/// <param name="isSave">是否保存</param>
public void Close(bool isSave)
{
myWorkBook.Close(isSave, Type.Missing, Type.Missing);
myExcel.Quit();
myWorkBook = null;
myExcel = null;
GC.Collect();
}

/// <summary>
/// 关闭Excel
/// </summary>
/// <param name="isSave">是否保存</param>
/// <param name="fileName">存储文件名</param>
public void Close(bool isSave,string fileName)
{
myWorkBook.Close(isSave, fileName, Type.Missing);
myExcel.Quit();
myWorkBook = null;
myExcel = null;
GC.Collect();
}

#region 私有成员
private string GetColumnName(int number)
{
int h, l;
h = number / 26;
l = number % 26;
if (l == 0)
{
h -= 1;
l = 26;
}
string s = GetLetter(h) + GetLetter(l);
return s;
}

private string GetLetter(int number)
{
switch (number)
{
case 1:
return "A";
case 2:
return "B";
case 3:
return "C";
case 4:
return "D";
case 5:
return "E";
case 6:
return "F";
case 7:
return "G";
case 8:
return "H";
case 9:
return "I";
case 10:
return "J";
case 11:
return "K";
case 12:
return "L";
case 13:
return "M";
case 14:
return "N";
case 15:
return "O";
case 16:
return "P";
case 17:
return "Q";
case 18:
return "R";
case 19:
return "S";
case 20:
return "T";
case 21:
return "U";
case 22:
return "V";
case 23:
return "W";
case 24:
return "X";
case 25:
return "Y";
case 26:
return "Z";
default:
return "";
}
}
#endregion


}

/// <summary>
/// 水平对齐方式
/// </summary>
public enum ExcelHAlign
{
常规 = 1,
靠左,
居中,
靠右,
填充,
两端对齐,
跨列居中,
分散对齐
}

/// <summary>
/// 垂直对齐方式
/// </summary>
public enum ExcelVAlign
{
靠上 = 1,
居中,
靠下,
两端对齐,
分散对齐
}

/// <summary>
/// 线粗
/// </summary>
public enum BorderWeight
{
极细 = 1,
细 = 2,
粗 = -4138,
极粗 = 4
}

/// <summary>
/// 线样式
/// </summary>
public enum LineStyle
{
连续直线 = 1,
短线 = -4115,
线点相间 = 4,
短线间两点 = 5,
点 = -4118,
双线 = -4119,
无 = -4142,
少量倾斜点 = 13
}

/// <summary>
/// 下划线方式
/// </summary>
public enum UnderlineStyle
{
无下划线 = -4142,
双线 = - 4119,
双线充满全格 = 5,
单线 = 2,
单线充满全格 = 4
}

/// <summary>
/// 单元格填充方式
/// </summary>
public enum Pattern
{
Automatic = -4105,
Checker = 9,
CrissCross = 16,
Down = -4121,
Gray16 = 17,
Gray25 = -4124,
Gray50 = -4125,
Gray75 = -4126,
Gray8 = 18,
Grid = 15,
Horizontal = -4128,
LightDown = 13,
LightHorizontal = 11,
LightUp = 14,
LightVertical = 12,
None = -4142,
SemiGray75 = 10,
Solid = 1,
Up = -4162,
Vertical = -4166
}

/// <summary>
/// 常用颜色定义,对就Excel中颜色名
/// </summary>
public enum ColorIndex
{
无色 = -4142,
自动 = -4105,
黑色 = 1,
褐色 = 53,
橄榄 = 52,
深绿 = 51,
深青 = 49,
深蓝 = 11,
靛蓝 = 55,
灰色80 = 56,
深红 = 9,
橙色 = 46,
深黄 = 12,
绿色 = 10,
青色 = 14,
蓝色 = 5,
蓝灰 = 47,
灰色50 = 16,
红色 = 3,
浅橙色 = 45,
酸橙色 = 43,
海绿 = 50,
水绿色 = 42,
浅蓝 = 41, 
紫罗兰 = 13,
灰色40 = 48,
粉红 = 7,
金色 = 44,
黄色 = 6,
鲜绿 = 4,
青绿 = 8,
天蓝 = 33,
梅红 = 54,
灰色25 = 15,
玫瑰红 = 38,
茶色 = 40,
浅黄 = 36,
浅绿 = 35,
浅青绿 = 34,
淡蓝 = 37,
淡紫 = 39,
白色 = 2
}
}