C# 操作EXCEL 参考

1.自定义一个透视表的结构体

public struct PivotItem { public string ascID; public object smTatY; public object smTatN; public object sxTatY; public object sxTatN; }

 

2.方法

using Excel = Microsoft.Office.Interop.Excel; //Excel方法 public class ExcelDB { /// <summary> /// 产生DT /// </summary> /// <param name="sql"></param> /// <param name="conStr"></param> /// <returns></returns> public static DataTable RunSql(string sql,string conStr) { DataTable table = new DataTable(); using (SqlConnection conn = new SqlConnection(conStr)) { try { SqlCommand command = new SqlCommand(sql, conn); conn.Open(); using (IDataReader read = command.ExecuteReader(CommandBehavior.CloseConnection)) { table.Load(read); } } catch { } } return table; } /// <summary> /// 填充WorkSheet 根据DT /// </summary> /// <param name="table"></param> /// <param name="ws"></param> public static void GetWsBySqlTable(DataTable table,ref Excel.Worksheet ws) { int colCount = table.Columns.Count; int rowCount = table.Rows.Count; object[,] objData = new object[rowCount, colCount]; //使用数组填充提高效率 for (int j = 0; j < colCount; j++) { ws.Cells[1, j + 1] = table.Columns[j].ColumnName; for (int i = 0; i < rowCount; i++) { objData[i, j] = table.Rows[i][j].ToString(); } } Excel.Range range = ws.get_Range(ws.Cells[2, 1], ws.Cells[rowCount + 1, colCount]); range.Value2 = objData; } /// <summary> /// TAT明细透视图 /// </summary> /// <param name="range"></param> /// <param name="wb"></param> /// <param name="MyPivot"></param> public static void CreateTATPivotTable(Excel.Range range, Excel.Workbook wb, out Excel.Worksheet MyPivot) { //新建工作表，用来存放数据 MyPivot = (Excel.Worksheet)wb.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); MyPivot.Name = "PivotTable1"; Excel.Range d = MyPivot.get_Range("A1", Type.Missing); Excel.PivotCaches objPivot = wb.PivotCaches(); objPivot.Add(Excel.XlPivotTableSourceType.xlDatabase, range) .CreatePivotTable( d, "透视表", Type.Missing, Excel.XlPivotTableVersionList.xlPivotTableVersion10); //定义数据透视表 Excel.PivotTable MyPivotTable = (Excel.PivotTable)MyPivot.PivotTables("透视表"); MyPivotTable.AddDataField(MyPivotTable.PivotFields("达标状态"), "Analysis", Excel.XlConsolidationFunction.xlCount); ((Excel.PivotField)MyPivotTable.PivotFields("ASCID")).Orientation = Excel.XlPivotFieldOrientation.xlRowField; ((Excel.PivotField)MyPivotTable.PivotFields("维修级别")).Orientation = Excel.XlPivotFieldOrientation.xlColumnField; ((Excel.PivotField)MyPivotTable.PivotFields("达标状态")).Orientation = Excel.XlPivotFieldOrientation.xlColumnField; } }

3.产生excel主要方法体,

Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); Excel.Workbooks wbs = (Excel.Workbooks)app.Workbooks; Excel.Workbook wb = (Excel.Workbook)wbs.Add(true); Excel.Sheets wss = (Excel.Sheets)app.Worksheets; Excel.Worksheet ws = (Excel.Worksheet)(wss.get_Item(1)); //设置源数据的表名，形如TAT3-23数据 ws.Name = "TAT" + dateTimePicker1.Value.Month + "-" + dateTimePicker1.Value.Day.ToString() + "数据"; string select = "select 所属区域, 维修中心, 厂商维修单号,Upper(LEFT(厂商维修单号,5)) ASCID , 维修单类型, 维修种类," + "维修级别, 是否申请备件, ZONE值, 是否OTOR, 标准TAT, 调整TAT, HP开单时间, 用户期望上门时间, HP维修完成时间," + "HP结单时间, 备件申请时间, 达标状态, 超期分类, 超期说明, HP超期原因, 故障描述, 绩效工程, 地址, 区县, 产品号, 条形码from hp_source where " + " HP结单时间>= '" + start.Date + "'and cast(HP结单时间as datetime)<'" + end.Date.AddDays(1) + "'"; DataTable table = ExcelDB.RunSql(select, hp_source); int colCount = table.Columns.Count; int rowCount = table.Rows.Count; if (rowCount <= 0) { MessageBox.Show("无数据"); return; } ExcelDB.GetWsBySqlTable(table, ref ws);//产生基础数据 //app.Visible = true; app.DisplayAlerts = false; Excel.Range ex = ws.get_Range(ws.Cells[1, 1], ws.Cells[rowCount + 1, colCount]); ex.Font.Size = 10; Excel.Worksheet MyPivot; ExcelDB.CreateTATPivotTable(ex, wb, out MyPivot);//产生透视数据表 Excel.Worksheet TAT = (Excel.Worksheet)wb.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); TAT.Name = "SMAndSX"; Excel.Range r; r = ws.get_Range(ws.Cells[1, 1], ws.Cells[1, 27]); r.Font.ColorIndex = 3; r = TAT.get_Range(TAT.Cells[4, 1], TAT.Cells[31, 1]); r.Interior.ColorIndex = 35;//35代表浅绿，代表浅青绿 //颜色参考 http://msdn.microsoft.com/en-us/library/cc296089(v=office.12).aspx r = TAT.get_Range(TAT.Cells[1, 2], TAT.Cells[1, 7]); r.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; r.Merge(r.MergeCells); r = TAT.get_Range(TAT.Cells[2, 2], TAT.Cells[2, 4]); r.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; r.Merge(r.MergeCells); r = TAT.get_Range(TAT.Cells[2, 5], TAT.Cells[2, 7]); r.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; r.Merge(r.MergeCells); TAT.Cells[1, 2] = "3-1"; TAT.Cells[2, 2] = "SM"; TAT.Cells[2, 5] = "SD"; string[] str = Method.SwitchArrayByEnum(new hpTatEnum()); for (int i = 0; i < str.Length; i++) { TAT.Cells[3, i + 1] = str[i]; } r = TAT.get_Range(TAT.Cells[1, 1], TAT.Cells[32, str.Length]); r.Font.Size = 10; object ExcelRangeValue = (MyPivot.UsedRange).Value2; object[,] objData = (object[,])ExcelRangeValue;//下标开始 //定义数组 List<PivotItem> list = new List<PivotItem>(); PivotItem item;//结构 for (int k = 0; k < ASCID.Length; k++) { for (int i = 1; i < objData.GetLength(0); i++) { if (ASCID[k].Trim() == (objData[i, 1] == null ? "" : objData[i, 1].ToString())) { item = new PivotItem();//填充满足条件的链表 item.ascID = objData[i, 1].ToString(); item.smTatY = objData[i, 2] == null ? "0" : objData[i, 2]; item.smTatN = objData[i, 3] == null ? "0" : objData[i, 3]; item.sxTatY = objData[i, 5] == null ? "0" : objData[i, 5]; item.sxTatN = objData[i, 6] == null ? "0" : objData[i, 6]; list.Add(item); } } } int count1 = 0, count2 = 0, count3 = 0, count4 = 0; for (int i = 0; i < list.Count; i++) { TAT.Cells[i + 4, 1] = list[i].ascID;//从链表填充Excel结果表 if (list[i].smTatY != null) count1 += int.Parse(list[i].smTatY.ToString()); TAT.Cells[i + 4, 3] = list[i].smTatY; if (list[i].smTatN != null) count2 += int.Parse(list[i].smTatN.ToString()); TAT.Cells[i + 4, 4] = list[i].smTatN; if (list[i].sxTatY != null) count3 += int.Parse(list[i].sxTatY.ToString()); TAT.Cells[i + 4, 6] = list[i].sxTatY; if (list[i].sxTatN != null) count4 += int.Parse(list[i].sxTatN.ToString()); TAT.Cells[i + 4, 7] = list[i].sxTatN; } //汇总行 int Tatstart = 4, Tatbottom = 32; r = TAT.get_Range("A32", "G32"); r.Font.Bold = true; r.Interior.ColorIndex = 38; TAT.Cells[Tatbottom, 1] = "CL汇总"; TAT.Cells[Tatbottom, 2] = (double)count1 / (double)(count1 + count2); TAT.Cells[Tatbottom, 3] = count1; TAT.Cells[Tatbottom, 4] = count2; TAT.Cells[Tatbottom, 5] = (double)count3 / (double)(count3 + count4); TAT.Cells[Tatbottom, 6] = count3; TAT.Cells[Tatbottom, 7] = count4; r = TAT.get_Range(TAT.Cells[Tatstart, 2], TAT.Cells[Tatbottom, 2]); r.NumberFormat = "0.00%"; r.FormulaR1C1 = "=IF(ISERROR(RC[1]/(RC[1]+RC[2])),0,RC[1]/(RC[1]+RC[2]))";//Excel公式 r = TAT.get_Range(TAT.Cells[Tatstart, 5], TAT.Cells[Tatbottom, 5]); r.NumberFormat = "0.00%"; r.FormulaR1C1 = "=IF(ISERROR(RC[1]/(RC[1]+RC[2])),0,RC[1]/(RC[1]+RC[2]))"; //MyPivot.Delete(); string path = "c://" + DateTime.Now.ToShortDateString() + ".xls"; ws.SaveAs(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing); //清理回收Excel进程 app.Quit(); app = null; GC.Collect(); MessageBox.Show("导出完毕！/r/n" + path);