/// <summary> /// 导入EXCEL /// </summary> /// <param name="filepach"></param> /// <param name="filedList"></param> /// <param name="capList"></param> /// <returns></returns> public static DataTable GetExcel(string filepach, IList <string> filedList, IList <string> capList) { string str; FarPoint.Win.Spread.FpSpread fpSpread1 = new FarPoint.Win.Spread.FpSpread(); try { fpSpread1.OpenExcel(filepach); } catch { string filepath1 = Path.GetTempPath() + "\\" + Path.GetFileName(filepach); File.Copy(filepach, filepath1); fpSpread1.OpenExcel(filepath1); File.Delete(filepath1); } DataTable dt = new DataTable(); IList <string> fie = new List <string>(); IList <int> yxzl = new List <int>();//有效的那几列 int m = 1; for (int j = 0; j < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++) { if (capList.Contains(fpSpread1.Sheets[0].Cells[0, j].Text)) { fie.Add(filedList[capList.IndexOf(fpSpread1.Sheets[0].Cells[0, j].Text)]); yxzl.Add(j); } } for (int k = 0; k < fie.Count; k++) { dt.Columns.Add(fie[k]); } for (int i = m; i <= fpSpread1.Sheets[0].GetLastNonEmptyRow(FarPoint.Win.Spread.NonEmptyItemFlag.Data); i++) { DataRow dr = dt.NewRow(); // for (int j = 0; j < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++) int n = 0; foreach (int j in yxzl) { dr[fie[n]] = fpSpread1.Sheets[0].Cells[i, j].Text; n++; } dt.Rows.Add(dr); } return(dt); }
private static void CreateTitle(string fname, string title, string dw) { FarPoint.Win.Spread.FpSpread fps = new FarPoint.Win.Spread.FpSpread(); fps.OpenExcel(fname); SheetView sv = fps.Sheets[0]; int ColumnCount = sv.NonEmptyColumnCount; int RowCount = sv.NonEmptyRowCount; //sv.ColumnCount = ColumnCount; //sv.RowCount = RowCount; sv.AddRows(0, 2); sv.Cells[0, 0].Text = title; sv.Cells[0, 0].Font = new System.Drawing.Font("宋体", 16F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134))); sv.Cells[0, 0].HorizontalAlignment = CellHorizontalAlignment.Center; sv.Cells[0, 0].VerticalAlignment = CellVerticalAlignment.Center; sv.Cells[0, 0].Row.Height = 50; sv.Cells[0, 0].ColumnSpan = ColumnCount; sv.Cells[1, 0].Text = dw; sv.Cells[1, 0].Font = new System.Drawing.Font("宋体", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134))); sv.Cells[1, 0].HorizontalAlignment = CellHorizontalAlignment.Right; sv.Cells[1, 0].VerticalAlignment = CellVerticalAlignment.Center; sv.Cells[1, 0].ColumnSpan = ColumnCount; for (int i = 0; i < ColumnCount; i++) { sv.Cells[2, i].Row.Height = 40; sv.Cells[2, i].Font = new System.Drawing.Font("宋体", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134))); } sv.AddRows(RowCount + 2, 2); sv.Cells[RowCount + 2, 0].Text = "建表时间:" + DateTime.Now.Year + "-" + DateTime.Now.Month + "-" + DateTime.Now.Day; sv.Cells[RowCount + 2, 0].Font = new System.Drawing.Font("宋体", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134))); sv.Cells[RowCount + 2, 0].HorizontalAlignment = CellHorizontalAlignment.Right; sv.Cells[RowCount + 2, 0].VerticalAlignment = CellVerticalAlignment.Center; sv.Cells[RowCount + 2, 0].ColumnSpan = ColumnCount; fps.SaveExcel(fname); }
public static void ExportExcel(GridControl gridControl) { //try //{ SaveFileDialog saveFileDialog1 = new SaveFileDialog(); string fname = ""; saveFileDialog1.Filter = "Microsoft Excel (*.xls)|*.xls"; if (saveFileDialog1.ShowDialog() == DialogResult.OK) { fname = saveFileDialog1.FileName; try { gridControl.ExportToExcelOld(fname); FarPoint.Win.Spread.FpSpread fps = new FarPoint.Win.Spread.FpSpread(); fps.OpenExcel(fname); SheetView sv = fps.Sheets[0]; for (int j = 0; j < sv.NonEmptyRowCount; j++) { for (int k = 0; k < sv.NonEmptyColumnCount; k++) { sv.Cells[j,k].CellType = new FarPoint.Win.Spread.CellType.NumberCellType(); } } fps.SaveExcel(fname); // 定义要使用的Excel 组件接口 // 定义Application 对象,此对象表示整个Excel 程序 Microsoft.Office.Interop.Excel.Application excelApp = null; // 定义Workbook对象,此对象代表工作薄 Microsoft.Office.Interop.Excel.Workbook workBook; // 定义Worksheet 对象,此对象表示Execel 中的一张工作表 Microsoft.Office.Interop.Excel.Worksheet ws = null; Microsoft.Office.Interop.Excel.Range range = null; excelApp = new Microsoft.Office.Interop.Excel.Application(); workBook = excelApp.Workbooks.Open(fname, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); for (int i = 1; i <= workBook.Worksheets.Count; i++) { ws = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[i]; //取消保护工作表 ws.Unprotect(Missing.Value); //有数据的行数 int row = ws.UsedRange.Rows.Count; //有数据的列数 int col = ws.UsedRange.Columns.Count; //创建一个区域 range = ws.get_Range(ws.Cells[1, 1], ws.Cells[row, col]); //设区域内的单元格自动换行 range.Select(); range.NumberFormatLocal = "G/通用格式"; //保护工作表 ws.Protect(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); } //保存工作簿 workBook.Save(); //关闭工作簿 excelApp.Workbooks.Close(); if (MsgBox.ShowYesNo("导出成功,是否打开该文档?") != DialogResult.Yes) return; System.Diagnostics.Process.Start(fname); } catch { MsgBox.Show("无法保存" + fname + "。请用其他文件名保存文件,或将文件存至其他位置。"); return; } } //return true; //} //catch { } }
public static void ExportToExcelOld(string title, string dw, GridControl gc) { SaveFileDialog saveFileDialog1 = new SaveFileDialog(); string fname = ""; saveFileDialog1.Filter = "Microsoft Excel (*.xls)|*.xls"; if (saveFileDialog1.ShowDialog() == DialogResult.OK) { try { fname = saveFileDialog1.FileName; gc.ExportToExcelOld(fname); // gc.ExportToExcelOld(fname); FarPoint.Win.Spread.FpSpread fps = new FarPoint.Win.Spread.FpSpread(); fps.OpenExcel(fname); SheetView sv = fps.Sheets[0]; int ColumnCount = sv.NonEmptyColumnCount+2; int RowCount = sv.NonEmptyRowCount; //sv.ColumnCount = ColumnCount; //sv.RowCount = RowCount; sv.AddRows(0, 2); sv.Cells[0, 0].Text = title; sv.Cells[0, 0].Font = new System.Drawing.Font("����", 16F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134))); sv.Cells[0, 0].HorizontalAlignment = CellHorizontalAlignment.Center; sv.Cells[0, 0].VerticalAlignment = CellVerticalAlignment.Center; sv.Cells[0, 0].Row.Height = 50; sv.Cells[0, 0].ColumnSpan = ColumnCount; sv.Cells[1, 0].Text = dw; sv.Cells[1, 0].Font = new System.Drawing.Font("����", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134))); sv.Cells[1, 0].HorizontalAlignment = CellHorizontalAlignment.Right; sv.Cells[1, 0].VerticalAlignment = CellVerticalAlignment.Center; sv.Cells[1, 0].ColumnSpan = ColumnCount; for (int i = 0; i < ColumnCount; i++) { sv.Cells[2, i].Column.Width = 150; sv.Cells[2, i].Row.Height = 40; sv.Cells[2, i].Font = new System.Drawing.Font("����", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134))); } //FarPoint.Win.Spread.CellType.NumberCellType numberCellTypes= new FarPoint.Win.Spread.CellType.NumberCellType(); //numberCellTypes.DecimalPlaces = 1; //for (int i = 0; i < ColumnCount; i++) //{ // for (int j = 2; i < RowCount; i++) // { // sv.Cells[j, i].CellType = numberCellTypes; // } //} sv.AddRows(RowCount + 2, 2); sv.Cells[RowCount + 2, 0].Text = "����ʱ��:" + DateTime.Now.Year + "-" + DateTime.Now.Month + "-" + DateTime.Now.Day; sv.Cells[RowCount + 2, 0].Font = new System.Drawing.Font("����", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134))); sv.Cells[RowCount + 2, 0].HorizontalAlignment = CellHorizontalAlignment.Right; sv.Cells[RowCount + 2, 0].VerticalAlignment = CellVerticalAlignment.Center; sv.Cells[RowCount + 2, 0].ColumnSpan = ColumnCount; //sv.SetColumnVisible(ColumnCount - 1, false); //sv.SetColumnVisible(ColumnCount - 2, false); for (int j = 0; j < sv.NonEmptyRowCount; j++) { for (int k = 0; k < sv.NonEmptyColumnCount; k++) { sv.Cells[j,k].CellType = new FarPoint.Win.Spread.CellType.NumberCellType(); } } fps.SaveExcel(fname); // ����Ҫʹ�õ�Excel ����ӿ� // ����Application ����,�˶����ʾ����Excel ���� Microsoft.Office.Interop.Excel.Application excelApp = null; // ����Workbook����,�˶����������� Microsoft.Office.Interop.Excel.Workbook workBook; // ����Worksheet ����,�˶����ʾExecel �е�һ�Ź����� Microsoft.Office.Interop.Excel.Worksheet ws = null; Microsoft.Office.Interop.Excel.Range range = null; excelApp = new Microsoft.Office.Interop.Excel.Application(); workBook = excelApp.Workbooks.Open(fname, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); for (int i = 1; i <= workBook.Worksheets.Count; i++) { ws = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[i]; //ȡ������������ ws.Unprotect(Missing.Value); //�����ݵ����� int row = ws.UsedRange.Rows.Count; //�����ݵ����� int col = ws.UsedRange.Columns.Count; //����һ������ range = ws.get_Range(ws.Cells[1, 1], ws.Cells[row, col]); //�������ڵĵ�Ԫ���Զ����� range.Select(); range.NumberFormatLocal = "G/ͨ�ø�ʽ"; //���������� ws.Protect(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); } //���湤���� workBook.Save(); //�رչ����� excelApp.Workbooks.Close(); if (MsgBox.ShowYesNo("�����ɹ����Ƿ���ĵ���") != DialogResult.Yes) return; System.Diagnostics.Process.Start(fname); } catch { MsgBox.Show("������" + fname + "�����������ļ��������ļ������ļ���������λ�á�"); return; } } }
public static void ExportToExcelOld(string title, string dw, GridControl gc) { SaveFileDialog saveFileDialog1 = new SaveFileDialog(); string fname = ""; saveFileDialog1.Filter = "Microsoft Excel (*.xls)|*.xls"; if (saveFileDialog1.ShowDialog() == DialogResult.OK) { try { fname = saveFileDialog1.FileName; gc.ExportToExcelOld(fname); FarPoint.Win.Spread.FpSpread fps = new FarPoint.Win.Spread.FpSpread(); fps.OpenExcel(fname); SheetView sv = fps.Sheets[0]; int ColumnCount = sv.NonEmptyColumnCount; int RowCount = sv.NonEmptyRowCount; //sv.ColumnCount = ColumnCount; //sv.RowCount = RowCount; sv.AddRows(0, 2); sv.Cells[0, 0].Text = title; sv.Cells[0, 0].Font = new System.Drawing.Font("宋体", 16F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134))); sv.Cells[0, 0].HorizontalAlignment = CellHorizontalAlignment.Center; sv.Cells[0, 0].VerticalAlignment = CellVerticalAlignment.Center; sv.Cells[0, 0].Row.Height = 50; sv.Cells[0, 0].ColumnSpan = ColumnCount; sv.Cells[1, 0].Text = dw; sv.Cells[1, 0].Font = new System.Drawing.Font("宋体", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134))); sv.Cells[1, 0].HorizontalAlignment = CellHorizontalAlignment.Right; sv.Cells[1, 0].VerticalAlignment = CellVerticalAlignment.Center; sv.Cells[1, 0].ColumnSpan = ColumnCount; for (int i = 0; i < ColumnCount; i++) { sv.Cells[2, i].Row.Height = 40; sv.Cells[2, i].Font = new System.Drawing.Font("宋体", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134))); } sv.AddRows(RowCount + 2, 2); sv.Cells[RowCount + 2, 0].Text = "建表时间:" + DateTime.Now.Year + "-" + DateTime.Now.Month + "-" + DateTime.Now.Day; sv.Cells[RowCount + 2, 0].Font = new System.Drawing.Font("宋体", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134))); sv.Cells[RowCount + 2, 0].HorizontalAlignment = CellHorizontalAlignment.Right; sv.Cells[RowCount + 2, 0].VerticalAlignment = CellVerticalAlignment.Center; sv.Cells[RowCount + 2, 0].ColumnSpan = ColumnCount; sv.SetColumnVisible(ColumnCount - 1, false); sv.SetColumnVisible(ColumnCount - 2, false); for (int j = 0; j < sv.NonEmptyRowCount; j++) { for (int k = 0; k < sv.NonEmptyColumnCount; k++) { sv.Cells[j, k].CellType = new FarPoint.Win.Spread.CellType.NumberCellType(); } } fps.SaveExcel(fname); // 定义要使用的Excel 组件接口 // 定义Application 对象,此对象表示整个Excel 程序 Microsoft.Office.Interop.Excel.Application excelApp = null; // 定义Workbook对象,此对象代表工作薄 Microsoft.Office.Interop.Excel.Workbook workBook; // 定义Worksheet 对象,此对象表示Execel 中的一张工作表 Microsoft.Office.Interop.Excel.Worksheet ws = null; Microsoft.Office.Interop.Excel.Range range = null; excelApp = new Microsoft.Office.Interop.Excel.Application(); workBook = excelApp.Workbooks.Open(fname, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); for (int i = 1; i <= workBook.Worksheets.Count; i++) { ws = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[i]; //取消保护工作表 ws.Unprotect(Missing.Value); //有数据的行数 int row = ws.UsedRange.Rows.Count; //有数据的列数 int col = ws.UsedRange.Columns.Count; //创建一个区域 range = ws.get_Range(ws.Cells[1, 1], ws.Cells[row, col]); //设区域内的单元格自动换行 range.Select(); range.NumberFormatLocal = "G/通用格式"; //保护工作表 ws.Protect(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); } //保存工作簿 workBook.Save(); //关闭工作簿 excelApp.Workbooks.Close(); if (MsgBox.ShowYesNo("导出成功,是否打开该文档?") != DialogResult.Yes) { return; } System.Diagnostics.Process.Start(fname); } catch { MsgBox.Show("无法保存" + fname + "。请用其他文件名保存文件,或将文件存至其他位置。"); return; } } }
public static void ExportExcel(GridControl gridControl) { //try //{ SaveFileDialog saveFileDialog1 = new SaveFileDialog(); string fname = ""; saveFileDialog1.Filter = "Microsoft Excel (*.xls)|*.xls"; if (saveFileDialog1.ShowDialog() == DialogResult.OK) { fname = saveFileDialog1.FileName; //File.SetAttributes(fname, File.GetAttributes(fname) | FileAttributes.ReadOnly); ////Create the file. //using (FileStream fs = new FileStream(fname, FileMode.OpenOrCreate, FileAccess.Read)) //{ // if (!fs.CanWrite) // { // MsgBox.Show("文件不能被创建,请检查文件是否被打开"); // return; // } //} // try //{ // File.Move(fname,fname+"1"); //} //catch //{ // MsgBox.Show("无法保存"+fname+"。请用其他文件名保存文件,或将文件存至其他位置。"); // return; //} //finally //{ // File.Move(fname + "1",fname); //} try { gridControl.ExportToExcelOld(fname); FarPoint.Win.Spread.FpSpread fps = new FarPoint.Win.Spread.FpSpread(); fps.OpenExcel(fname); SheetView sv = fps.Sheets[0]; for (int j = 0; j < sv.NonEmptyRowCount; j++) { for (int k = 0; k < sv.NonEmptyColumnCount; k++) { FarPoint.Win.Spread.CellType.NumberCellType temptype = new FarPoint.Win.Spread.CellType.NumberCellType(); sv.Cells[j, k].CellType = temptype; } } fps.SaveExcel(fname); // 定义要使用的Excel 组件接口 // 定义Application 对象,此对象表示整个Excel 程序 Microsoft.Office.Interop.Excel.Application excelApp = null; // 定义Workbook对象,此对象代表工作薄 Microsoft.Office.Interop.Excel.Workbook workBook; // 定义Worksheet 对象,此对象表示Execel 中的一张工作表 Microsoft.Office.Interop.Excel.Worksheet ws = null; Microsoft.Office.Interop.Excel.Range range = null; excelApp = new Microsoft.Office.Interop.Excel.Application(); workBook = excelApp.Workbooks.Open(fname, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); for (int i = 1; i <= workBook.Worksheets.Count; i++) { ws = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[i]; //取消保护工作表 ws.Unprotect(Missing.Value); //有数据的行数 int row = ws.UsedRange.Rows.Count; //有数据的列数 int col = ws.UsedRange.Columns.Count; //创建一个区域 range = ws.get_Range(ws.Cells[1, 1], ws.Cells[row, col]); //设区域内的单元格自动换行 range.Select(); range.NumberFormatLocal = "G/通用格式"; //保护工作表 ws.Protect(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); } //保存工作簿 workBook.Save(); //关闭工作簿 excelApp.Workbooks.Close(); if (MsgBox.ShowYesNo("导出成功,是否打开该文档?") != DialogResult.Yes) { return; } System.Diagnostics.Process.Start(fname); } catch { MsgBox.Show("无法保存" + fname + "。请用其他文件名保存文件,或将文件存至其他位置。"); return; } } //return true; //} //catch { } }
private static void CreateTitle(string fname,string title,string dw) { FarPoint.Win.Spread.FpSpread fps = new FarPoint.Win.Spread.FpSpread(); fps.OpenExcel(fname); SheetView sv = fps.Sheets[0]; int ColumnCount = sv.NonEmptyColumnCount; int RowCount = sv.NonEmptyRowCount; //sv.ColumnCount = ColumnCount; //sv.RowCount = RowCount; sv.AddRows(0, 2); sv.Cells[0, 0].Text = title; sv.Cells[0, 0].Font = new System.Drawing.Font("����", 16F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134))); sv.Cells[0, 0].HorizontalAlignment = CellHorizontalAlignment.Center; sv.Cells[0, 0].VerticalAlignment = CellVerticalAlignment.Center; sv.Cells[0, 0].Row.Height = 50; sv.Cells[0, 0].ColumnSpan = ColumnCount; sv.Cells[1, 0].Text = dw; sv.Cells[1, 0].Font = new System.Drawing.Font("����", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134))); sv.Cells[1, 0].HorizontalAlignment = CellHorizontalAlignment.Right; sv.Cells[1, 0].VerticalAlignment = CellVerticalAlignment.Center; sv.Cells[1, 0].ColumnSpan = ColumnCount; for (int i = 0; i < ColumnCount; i++) { sv.Cells[2, i].Row.Height = 40; sv.Cells[2, i].Font = new System.Drawing.Font("����", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134))); } sv.AddRows(RowCount + 2, 2); sv.Cells[RowCount + 2, 0].Text = "����ʱ��:" + DateTime.Now.Year + "-" + DateTime.Now.Month + "-" + DateTime.Now.Day; sv.Cells[RowCount + 2, 0].Font = new System.Drawing.Font("����", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134))); sv.Cells[RowCount + 2, 0].HorizontalAlignment = CellHorizontalAlignment.Right; sv.Cells[RowCount + 2, 0].VerticalAlignment = CellVerticalAlignment.Center; sv.Cells[RowCount + 2, 0].ColumnSpan = ColumnCount; fps.SaveExcel(fname); }
public static void ExportToExcelOld(GridControl gridControl,string title,string dw) { //try //{ // Control.CheckForIllegalCrossThreadCalls = false; SaveFileDialog saveFileDialog1 = new SaveFileDialog(); string fname = ""; saveFileDialog1.Filter = "Microsoft Excel (*.xls)|*.xls"; if (saveFileDialog1.ShowDialog() == DialogResult.OK) { FrmGress frm = new FrmGress(); // Thread thread = new Thread(new ThreadStart(frm.Show)); // thread.Start(); fname = saveFileDialog1.FileName; try { gridControl.ExportToExcelOld(fname); CreateTitle(fname, title, dw); FarPoint.Win.Spread.FpSpread fps = new FarPoint.Win.Spread.FpSpread(); fps.OpenExcel(fname); SheetView sv = fps.Sheets[0]; for (int j = 0; j < sv.NonEmptyRowCount; j++) { for (int k = 0; k < sv.NonEmptyColumnCount; k++) { sv.Cells[j, k].CellType = new FarPoint.Win.Spread.CellType.NumberCellType(); } } fps.SaveExcel(fname); // ����Ҫʹ�õ�Excel ����ӿ� // ����Application ����,�˶����ʾ����Excel ���� Microsoft.Office.Interop.Excel.Application excelApp = null; // ����Workbook����,�˶����������� Microsoft.Office.Interop.Excel.Workbook workBook; // ����Worksheet ����,�˶����ʾExecel �е�һ�Ź����� Microsoft.Office.Interop.Excel.Worksheet ws = null; Microsoft.Office.Interop.Excel.Range range = null; excelApp = new Microsoft.Office.Interop.Excel.Application(); workBook = excelApp.Workbooks.Open(fname, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); for (int i = 1; i <= workBook.Worksheets.Count; i++) { ws = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[i]; //ȡ������������ ws.Unprotect(Missing.Value); //�����ݵ����� int row = ws.UsedRange.Rows.Count; //�����ݵ����� int col = ws.UsedRange.Columns.Count; //����һ������ range = ws.get_Range(ws.Cells[1, 1], ws.Cells[row, col]); //�������ڵĵ�Ԫ���Զ����� range.Select(); range.NumberFormatLocal = "G/ͨ�ø�ʽ"; //���������� ws.Protect(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); } //���湤���� workBook.Save(); //�رչ����� excelApp.Workbooks.Close(); if (MsgBox.ShowYesNo("�����ɹ����Ƿ���ĵ���") != DialogResult.Yes) return; System.Diagnostics.Process.Start(fname); } catch { MsgBox.Show("������" + fname + "�����������ļ��������ļ������ļ���������λ�á�"); return; } } }
/// <summary> /// 导出EXCEL /// </summary> /// <param name="gridControl"></param> /// <param name="title"></param> /// <param name="dw"></param> public static void ExportToExcelandchildren(GridControl gridControl, string title, string dw) { SaveFileDialog saveFileDialog1 = new SaveFileDialog(); string fname = ""; saveFileDialog1.Filter = "Microsoft Excel (*.xls)|*.xls"; if (saveFileDialog1.ShowDialog() == DialogResult.OK) { fname = saveFileDialog1.FileName; try { gridControl.ExportToExcelOld(fname); FarPoint.Win.Spread.FpSpread fps = new FarPoint.Win.Spread.FpSpread(); fps.OpenExcel(fname); SheetView sv = fps.Sheets[0]; //for (int j = 1; j < sv.NonEmptyColumnCount; j++) //{ // for (int k = 0; k < sv.NonEmptyRowCount; k++) // { // sv.Cells[k, j].CellType = new FarPoint.Win.Spread.CellType.NumberCellType(); // } //} //输出子表的数据 //输出母线 ExportChildEXCEL(gridControl.DataSource as DataTable, new UCDeviceMX(), fps, "母线", "01"); //输出两绕组 ExportChildEXCEL(gridControl.DataSource as DataTable, new UCDeviceBYQ2(), fps, "两绕组变压器", "02"); //输出三绕组变压器 ExportChildEXCEL(gridControl.DataSource as DataTable, new UCDeviceBYQ3(), fps, "三绕组变压器", "03"); //输出负荷 ExportChildEXCEL(gridControl.DataSource as DataTable, new UCDeviceFH(), fps, "负荷", "12"); //输出发电机 ExportChildEXCEL(gridControl.DataSource as DataTable, new UCDeviceFDJ(), fps, "发电机", "04"); fps.SaveExcel(fname); //// 定义要使用的Excel 组件接口 //// 定义Application 对象,此对象表示整个Excel 程序 //Microsoft.Office.Interop.Excel.Application excelApp = null; //// 定义Workbook对象,此对象代表工作薄 //Microsoft.Office.Interop.Excel.Workbook workBook; //// 定义Worksheet 对象,此对象表示Execel 中的一张工作表 //Microsoft.Office.Interop.Excel.Worksheet ws = null; //Microsoft.Office.Interop.Excel.Range range = null; //excelApp = new Microsoft.Office.Interop.Excel.Application(); //workBook = excelApp.Workbooks.Open(fname, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); //for (int i = 1; i <= workBook.Worksheets.Count; i++) //{ // ws = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[i]; // //取消保护工作表 // ws.Unprotect(Missing.Value); // //有数据的行数 // int row = ws.UsedRange.Rows.Count; // //有数据的列数 // int col = ws.UsedRange.Columns.Count; // //创建一个区域 // range = ws.get_Range(ws.Cells[1, 1], ws.Cells[row, col]); // //设区域内的单元格自动换行 // range.Select(); // range.NumberFormatLocal = "G/通用格式"; // //保护工作表 // ws.Protect(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); //} ////保存工作簿 //workBook.Save(); ////关闭工作簿 //excelApp.Workbooks.Close(); // CreateTitle(fname, title, dw); if (MsgBox.ShowYesNo("导出成功,是否打开该文档?") != DialogResult.Yes) { return; } System.Diagnostics.Process.Start(fname); } catch (Exception ee) { MsgBox.Show("无法保存" + fname + "。请用其他文件名保存文件,或将文件存至其他位置。"); return; } } }
/// <summary> /// 导出EXCEL /// </summary> /// <param name="gridControl"></param> /// <param name="title"></param> /// <param name="dw"></param> public static void ExportToExcelOld(GridControl gridControl, string title, string dw) { SaveFileDialog saveFileDialog1 = new SaveFileDialog(); string fname = ""; saveFileDialog1.Filter = "Microsoft Excel (*.xls)|*.xls"; if (saveFileDialog1.ShowDialog() == DialogResult.OK) { fname = saveFileDialog1.FileName; try { gridControl.ExportToExcelOld(fname); FarPoint.Win.Spread.FpSpread fps = new FarPoint.Win.Spread.FpSpread(); fps.OpenExcel(fname); SheetView sv = fps.Sheets[0]; //for (int j = 1; j < sv.NonEmptyColumnCount; j++) //{ // for (int k = 0; k < sv.NonEmptyRowCount; k++) // { // sv.Cells[k, j].CellType = new FarPoint.Win.Spread.CellType.NumberCellType(); // } //} fps.SaveExcel(fname); // 定义要使用的Excel 组件接口 // 定义Application 对象,此对象表示整个Excel 程序 Microsoft.Office.Interop.Excel.Application excelApp = null; // 定义Workbook对象,此对象代表工作薄 Microsoft.Office.Interop.Excel.Workbook workBook; // 定义Worksheet 对象,此对象表示Execel 中的一张工作表 Microsoft.Office.Interop.Excel.Worksheet ws = null; Microsoft.Office.Interop.Excel.Range range = null; excelApp = new Microsoft.Office.Interop.Excel.Application(); workBook = excelApp.Workbooks.Open(fname, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); for (int i = 1; i <= workBook.Worksheets.Count; i++) { ws = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[i]; //取消保护工作表 ws.Unprotect(Missing.Value); //有数据的行数 int row = ws.UsedRange.Rows.Count; //有数据的列数 int col = ws.UsedRange.Columns.Count; //创建一个区域 range = ws.get_Range(ws.Cells[1, 1], ws.Cells[row, col]); //设区域内的单元格自动换行 range.Select(); range.NumberFormatLocal = "G/通用格式"; //保护工作表 ws.Protect(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); } //保存工作簿 workBook.Save(); //关闭工作簿 excelApp.Workbooks.Close(); // CreateTitle(fname, title, dw); if (MsgBox.ShowYesNo("导出成功,是否打开该文档?") != DialogResult.Yes) { return; } System.Diagnostics.Process.Start(fname); } catch (Exception ee) { MsgBox.Show("无法保存" + fname + "。请用其他文件名保存文件,或将文件存至其他位置。"); return; } } }
/// <summary> /// 导入EXCEL /// </summary> /// <param name="filepach"></param> /// <param name="filedList"></param> /// <param name="capList"></param> /// <returns></returns> public static DataTable GetExcelmainandchildren(string filepach, IList <string> filedList, IList <string> capList, string sheetname, DataTable dt1) { string str; FarPoint.Win.Spread.FpSpread fpSpread1 = new FarPoint.Win.Spread.FpSpread(); try { fpSpread1.OpenExcel(filepach); } catch { string filepath1 = Path.GetTempPath() + "\\" + Path.GetFileName(filepach); File.Copy(filepach, filepath1); fpSpread1.OpenExcel(filepath1); File.Delete(filepath1); } DataTable dt = new DataTable(); IList <string> fie = new List <string>(); IList <int> yxzl = new List <int>();//有效的那几列 SheetView sheetview = fpSpread1.Sheets.Find(sheetname); if (sheetview == null) { return(dt); } int m = 1; for (int j = 1; j < sheetview.GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++) { if (capList.Contains(sheetview.Cells[0, j].Text)) { fie.Add(filedList[capList.IndexOf(sheetview.Cells[0, j].Text)]); yxzl.Add(j); } } for (int k = 0; k < fie.Count; k++) { dt.Columns.Add(fie[k]); } dt.Columns.Add("SvgUID"); for (int i = m; i <= sheetview.GetLastNonEmptyRow(FarPoint.Win.Spread.NonEmptyItemFlag.Data); i++) { string datsourctitle = sheetview.Cells[i, 0].Text; DataRow dr = dt.NewRow(); // for (int j = 0; j < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++) DataRow[] drsource = dt1.Select("Title='" + datsourctitle + "'"); if (drsource.Length != 1) { continue; } dr["SvgUID"] = drsource[0]["UID"]; int n = 0; foreach (int j in yxzl) { dr[fie[n]] = sheetview.Cells[i, j].Text; n++; } dt.Rows.Add(dr); } return(dt); }
public static void ExportExcel(GridControl gridControl) { //try //{ SaveFileDialog saveFileDialog1 = new SaveFileDialog(); string fname = ""; saveFileDialog1.Filter = "Microsoft Excel (*.xls)|*.xls"; if (saveFileDialog1.ShowDialog() == DialogResult.OK) { fname = saveFileDialog1.FileName; //File.SetAttributes(fname, File.GetAttributes(fname) | FileAttributes.ReadOnly); ////Create the file. //using (FileStream fs = new FileStream(fname, FileMode.OpenOrCreate, FileAccess.Read)) //{ // if (!fs.CanWrite) // { // MsgBox.Show("�ļ����ܱ������������ļ��Ƿ�"); // return; // } //} // try //{ // File.Move(fname,fname+"1"); //} //catch //{ // MsgBox.Show("������"+fname+"�����������ļ��������ļ������ļ���������λ�á�"); // return; //} //finally //{ // File.Move(fname + "1",fname); //} try { gridControl.ExportToExcelOld(fname); FarPoint.Win.Spread.FpSpread fps = new FarPoint.Win.Spread.FpSpread(); fps.OpenExcel(fname); SheetView sv = fps.Sheets[0]; for (int j = 0; j < sv.NonEmptyRowCount; j++) { for (int k = 0; k < sv.NonEmptyColumnCount; k++) { FarPoint.Win.Spread.CellType.NumberCellType temptype=new FarPoint.Win.Spread.CellType.NumberCellType(); sv.Cells[j, k].CellType = temptype; } } fps.SaveExcel(fname); // ����Ҫʹ�õ�Excel ����ӿ� // ����Application ����,�˶����ʾ����Excel ���� Microsoft.Office.Interop.Excel.Application excelApp = null; // ����Workbook����,�˶����������� Microsoft.Office.Interop.Excel.Workbook workBook; // ����Worksheet ����,�˶����ʾExecel �е�һ�Ź����� Microsoft.Office.Interop.Excel.Worksheet ws = null; Microsoft.Office.Interop.Excel.Range range = null; excelApp = new Microsoft.Office.Interop.Excel.Application(); workBook = excelApp.Workbooks.Open(fname, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); for (int i = 1; i <= workBook.Worksheets.Count; i++) { ws = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[i]; //ȡ������������ ws.Unprotect(Missing.Value); //�����ݵ����� int row = ws.UsedRange.Rows.Count; //�����ݵ����� int col = ws.UsedRange.Columns.Count; //����һ������ range = ws.get_Range(ws.Cells[1, 1], ws.Cells[row, col]); //�������ڵĵ�Ԫ���Զ����� range.Select(); range.NumberFormatLocal="G/ͨ�ø�ʽ"; //���������� ws.Protect(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); } //���湤���� workBook.Save(); //�رչ����� excelApp.Workbooks.Close(); if (MsgBox.ShowYesNo("�����ɹ����Ƿ���ĵ���") != DialogResult.Yes) return; System.Diagnostics.Process.Start(fname); } catch { MsgBox.Show("������"+fname+"�����������ļ��������ļ������ļ���������λ�á�"); return; } } //return true; //} //catch { } }
public static void ExportToExcelOld(GridControl gridControl, int row, string col, bool bHe) { //try //{ // Control.CheckForIllegalCrossThreadCalls = false; SaveFileDialog saveFileDialog1 = new SaveFileDialog(); string fname = ""; saveFileDialog1.Filter = "Microsoft Excel (*.xls)|*.xls"; if (saveFileDialog1.ShowDialog() == DialogResult.OK) { FrmGress frm = new FrmGress(); // Thread thread = new Thread(new ThreadStart(frm.Show)); // thread.Start(); fname = saveFileDialog1.FileName; try { gridControl.ExportToExcelOld(fname); if (bHe) { FarPoint.Win.Spread.FpSpread fps = new FarPoint.Win.Spread.FpSpread(); fps.OpenExcel(fname); SheetView sv = fps.Sheets[0]; for (int j = 0; j < sv.NonEmptyRowCount; j++) { for (int k = 0; k < sv.NonEmptyColumnCount; k++) { sv.Cells[j, k].CellType = new FarPoint.Win.Spread.CellType.NumberCellType(); } } fps.SaveExcel(fname); // 定义要使用的Excel 组件接口 // 定义Application 对象,此对象表示整个Excel 程序 Microsoft.Office.Interop.Excel.Application excelApp = null; // 定义Workbook对象,此对象代表工作薄 Microsoft.Office.Interop.Excel.Workbook workBook; // 定义Worksheet 对象,此对象表示Execel 中的一张工作表 Microsoft.Office.Interop.Excel.Worksheet ws = null; Microsoft.Office.Interop.Excel.Range range = null; excelApp = new Microsoft.Office.Interop.Excel.Application(); workBook = excelApp.Workbooks.Open(fname, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); for (int i = 1; i <= workBook.Worksheets.Count; i++) { ws = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[i]; //取消保护工作表 ws.Unprotect(Missing.Value); //有数据的行数 int row1 = ws.UsedRange.Rows.Count; //有数据的列数 int col1 = ws.UsedRange.Columns.Count; //创建一个区域 range = ws.get_Range(ws.Cells[1, 1], ws.Cells[row1, col1]); //设区域内的单元格自动换行 range.Select(); range.NumberFormatLocal = "G/通用格式"; //保护工作表 ws.Protect(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); } //保存工作簿 workBook.Save(); //关闭工作簿 excelApp.Workbooks.Close(); ChangeExcel(fname, row, col); } else { FarPoint.Win.Spread.FpSpread fps = new FarPoint.Win.Spread.FpSpread(); fps.OpenExcel(fname); SheetView sv = fps.Sheets[0]; for (int j = 0; j < sv.NonEmptyRowCount; j++) { for (int k = 0; k < sv.NonEmptyColumnCount; k++) { sv.Cells[j, k].CellType = new FarPoint.Win.Spread.CellType.NumberCellType(); } } fps.SaveExcel(fname); // 定义要使用的Excel 组件接口 // 定义Application 对象,此对象表示整个Excel 程序 Microsoft.Office.Interop.Excel.Application excelApp = null; // 定义Workbook对象,此对象代表工作薄 Microsoft.Office.Interop.Excel.Workbook workBook; // 定义Worksheet 对象,此对象表示Execel 中的一张工作表 Microsoft.Office.Interop.Excel.Worksheet ws = null; Microsoft.Office.Interop.Excel.Range range = null; excelApp = new Microsoft.Office.Interop.Excel.Application(); workBook = excelApp.Workbooks.Open(fname, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); for (int i = 1; i <= workBook.Worksheets.Count; i++) { ws = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[i]; //取消保护工作表 ws.Unprotect(Missing.Value); //有数据的行数 int row1 = ws.UsedRange.Rows.Count; //有数据的列数 int col1 = ws.UsedRange.Columns.Count; //创建一个区域 range = ws.get_Range(ws.Cells[1, 1], ws.Cells[row1, col1]); //设区域内的单元格自动换行 range.Select(); range.NumberFormatLocal = "G/通用格式"; //保护工作表 ws.Protect(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); } //保存工作簿 workBook.Save(); //关闭工作簿 excelApp.Workbooks.Close(); if (MsgBox.ShowYesNo("导出成功,是否打开该文档?") != DialogResult.Yes) { return; } System.Diagnostics.Process.Start(fname); } } catch { MsgBox.Show("无法保存" + fname + "。请用其他文件名保存文件,或将文件存至其他位置。"); return; } } }