private string GetExcelSheetName(string pPath)
{
//打开一个Excel应用
_excelApp = new Excel.Application();
if (_excelApp == null)
{
throw new Exception("打开Excel应用时发生错误！");
}
_books = _excelApp.Workbooks;
//打开一个现有的工作薄
_book = _books.Add(pPath);
_sheets = _book.Sheets;
//选择第一个Sheet页
_sheet = (Excel._Worksheet)_sheets.get_Item(1);
string sheetName = _sheet.Name;
ReleaseCOM(_sheet);
ReleaseCOM(_sheets);
ReleaseCOM(_book);
ReleaseCOM(_books);
_excelApp.Quit();
ReleaseCOM(_excelApp);
return sheetName;
}
public void readExcel()
{
string valueString = string.Empty;
objExcelApp = new Microsoft.Office.Interop.Excel.Application();
objBooks = (Excel.Workbooks)objExcelApp.Workbooks;
//Open the workbook containing the address data.
objBook = objBooks.Open(@"C:\Temp\data\Test.xlsx", 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);
//Get a reference to the first sheet of the workbook.
objSheets = objBook.Worksheets;
objSheet = (Excel._Worksheet)objSheets.get_Item(1);
//Select the range of data containing the addresses and get the outer boundaries.
rngLast = objSheet.get_Range("A1").SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell);
long lLastRow = rngLast.Row;
long lLastCol = rngLast.Column;
// Iterate through the data and concatenate the values into a comma-delimited string.
for (long rowCounter = 1; rowCounter <= lLastRow; rowCounter++)
{
for (long colCounter = 1; colCounter <= lLastCol; colCounter++)
{
//Write the next value into the string.
Excel.Range cell = (Excel.Range)objSheet.Cells[rowCounter, colCounter];
string cellvalue = cell.Value.ToString();
//TODO: add your business logic for retrieve cell value
}
}
}
/// <summary>
/// 打开一个Excel文件
/// </summary>
/// <param name="FileName"></param>
public static void Open( string fileName )//
{
app = new Microsoft.Office.Interop.Excel.Application();
wbs = app.Workbooks;
wb = wbs.Add( fileName );
ws = wb.Worksheets[ 1 ] as Worksheet;
mFilename = fileName;
}
public Export()
{
// Instantiate Excel and start a new workbook.
objApp = new Excel.Application();
objBooks = objApp.Workbooks;
objBook = objBooks.Add(Missing.Value);
objSheets = objBook.Worksheets;
objSheet = (Excel._Worksheet)objSheets.get_Item(1);
}
private Worksheet objExcelWorkSheet; //定义Workbook工作表对象
#endregion Fields
#region Methods
// Change the Mapping WorkFlow @2015-7-16 By CC
public void EditWorkFlow(string StrTestData)
{
objExcelApp = new Microsoft.Office.Interop.Excel.Application();
objExcelWorkBooks = objExcelApp.Workbooks;
objExcelWorkbook = objExcelWorkBooks.Open(StrTestData, 0, false, 5, "", "", true,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
objExcelWorkSheet = (Worksheet)objExcelWorkbook.Worksheets["WorkFlow"]; //strSheetName is the Sheet Name of Exce，if there is no name then Defult is "1"
objExcelWorkSheet.Select(Type.Missing);
int rowsint = objExcelWorkSheet.UsedRange.Cells.Rows.Count; //get the Row Number
int columnsint = objExcelWorkSheet.UsedRange.Cells.Columns.Count;//get the column number
int rowNo = 0;
int ParameterRowNo = 0;
int columnNo = 0;
int FlagColumnNo = 0;
for (int j = 1; j <= columnsint; j++)
{
string strColumnsName = ((Range)objExcelWorkSheet.Cells[1, j]).Text.ToString();
if (strColumnsName == "Flag")
{
FlagColumnNo = j;
}
if (strColumnsName == "BusinessFlow")
{
rowNo = j;
}
if (strColumnsName == "ParameterValue")
{
ParameterRowNo = j;
}
}
for (int i = 2; i <= rowsint; i++)
{
string strFlagValue = ((Range)objExcelWorkSheet.Cells[i, FlagColumnNo]).Text.ToString();
if (strFlagValue =="Y")
{
int RowNO = i;
objExcelWorkSheet.Cells[RowNO, FlagColumnNo] = "N";
}
string strBusinessFlow = ((Range)objExcelWorkSheet.Cells[i, rowNo]).Text.ToString();
if (strBusinessFlow == "HeaderMapping" || strBusinessFlow == "TaxMapping" || strBusinessFlow == "LineItemsMapping")
{
columnNo = i;
string strParameterValue = ((Range)objExcelWorkSheet.Cells[i, ParameterRowNo]).Text.ToString();
if (strParameterValue == "IR" || strParameterValue == "TradeBilling" || strParameterValue == "TradeCredit" || strParameterValue == "TradeDebit" || strParameterValue == "TradeReturn")
{
objExcelWorkSheet.Cells[i, 1] = "Y";
}
}
}
objExcelWorkbook.Save();
objExcelWorkbook.Close(false, StrTestData, false);
objExcelApp.Quit();
NAR(objExcelApp);
NAR(objExcelWorkbook);
NAR(objExcelWorkSheet);
}
public ReportBuilder()
{
app = new Excel.Application();
appBooks = app.Workbooks;
currentBook = appBooks.Add(Missing.Value);
sheets = currentBook.Worksheets;
currentSheet = (Excel._Worksheet)sheets.get_Item(1);
range = currentSheet.get_Range("A1", Missing.Value);
charts = currentSheet.ChartObjects(Type.Missing);
chartObject = charts.Add(400, LastIndex, chartWidth, chartHeight);
}
//for writing
public ExcelOperator()
{
ExcelApp = new Excel.Application();
workbooks = ExcelApp.Workbooks;
workbook = workbooks.Add();
sheets = workbook.Sheets;
worksheet = sheets[1];
worksheet.Select(Type.Missing);
directoryPath = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory);
fileName = "shift.xlsx";
}
public ExcelWriter(string newSaveFile, string newExam)
{
saveFile = newSaveFile;
exam = newExam;
row = 2;
questionNumber = 1;
xlApp = new Excel.Application();
if (xlApp == null)
{
Console.WriteLine("EXCEL could not be started. Check that your office installation and project references are correct.");
return;
}
xlApp.Visible = true; // Turn this to true if you want to see the program in the foreground
wbs = xlApp.Workbooks;
wb = wbs.Add(Excel.XlWBATemplate.xlWBATWorksheet);
ws = (Excel.Worksheet)wb.Worksheets[1];
if (ws == null)
{
Console.WriteLine("Worksheet could not be created. Check that your office installation and project references are correct.");
}
ws.Cells[1, 1].EntireRow.Font.Bold = true;
// Setup the labels
ws.Cells[1, 1].Value2 = "Code";
ws.Cells[1, 2].Value2 = "Label";
ws.Cells[1, 3].Value2 = "ParentItemRef";
ws.Cells[1, 4].Value2 = "ItemType";
ws.Cells[1, 5].Value2 = "ItemLevelScore";
ws.Cells[1, 6].Value2 = "ItemCorrectMarks";
ws.Cells[1, 7].Value2 = "ItemWrongMarks";
ws.Cells[1, 8].Value2 = "Difficulty";
ws.Cells[1, 9].Value2 = "Classification";
ws.Cells[1, 10].Value2 = "Experience";
ws.Cells[1, 11].Value2 = "Language";
ws.Cells[1, 12].Value2 = "Shuffle";
ws.Cells[1, 13].Value2 = "NoOfOptions";
ws.Cells[1, 14].Value2 = "CorrectOption";
ws.Cells[1, 15].Value2 = "ItemText";
ws.Cells[1, 16].Value2 = "ItemImage";
ws.Cells[1, 17].Value2 = "ItemRationale";
int col = 18;
for (int o = 1; o <= 10; o++, col += 3)
{
ws.Cells[1, col].Value2 = "Option" + o;
ws.Cells[1, col + 1].Value2 = "Option" + o + "_Image";
ws.Cells[1, col + 2].Value2 = "Option" + o + "_Rationale";
}
}
// private Excel.Worksheet sheet;
public override void Convert(String inputFile, String outputFile)
{
Object nothing = Type.Missing;
try
{
if (!File.Exists(inputFile))
{
throw new ConvertException("File not Exists");
}
if (IsPasswordProtected(inputFile))
{
throw new ConvertException("Password Exist");
}
app = new Excel.Application();
books = app.Workbooks;
book = books.Open(inputFile, false, true, nothing, nothing, nothing, true, nothing, nothing, false, false, nothing, false, nothing, false);
bool hasContent = false;
foreach (Excel.Worksheet sheet in book.Worksheets)
{
Excel.Range range = sheet.UsedRange;
if (range != null) {
Excel.Range found = range.Cells.Find("*", nothing, nothing, nothing, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, nothing, nothing, nothing);
if (found != null) hasContent = true;
releaseCOMObject(found);
releaseCOMObject(range);
}
}
if (!hasContent) throw new ConvertException("No Content");
book.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF, outputFile, Excel.XlFixedFormatQuality.xlQualityMinimum, false, false, nothing, nothing, false, nothing);
}
catch (Exception e)
{
release();
throw new ConvertException(e.Message);
}
release();
}
private void forotborisfailov(string[] vsS, int a1, ref int z1, ref int z2, ref int z3, ref string s, string cilka)
{
excelapp = new Excel.Application();
excelappworkbooks = excelapp.Workbooks;
excelappworkbook = excelapp.Workbooks.Open(System.IO.Path.Combine(we, cilka),
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
excelsheets = excelappworkbook.Worksheets;
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1);
for (int i = 0; i < 350; i++)
{
if ((z1 < 26) && (z3 == 0)) s = vsS[z1] + a1.ToString();
else
{
z3++;
if (z3 == 1) z1 = 0;
s = vsS[z2] + vsS[z1] + a1.ToString();
if (z1 == 25) { z2++; z1 = 0; }
}
if (s == "IW" + a1.ToString()) break;
excelcells = excelworksheet.get_Range(s, Type.Missing);
string excelnumber = excelcells.Value2;
if ((excelnumber != null) && (excelnumber != "")) this.ponedelnic1flor.Add(excelnumber);
z1++;
}
z1 = 0;
z2 = 0;
z3 = 0;
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(2);
for (int i = 0; i < 350; i++)
{
if ((z1 < 26) && (z3 == 0)) s = vsS[z1] + a1.ToString();
else
{
z3++;
if (z3 == 1) z1 = 0;
s = vsS[z2] + vsS[z1] + a1.ToString();
if (z1 == 25) { z2++; z1 = 0; }
}
if (s == "IW" + a1.ToString()) break;
excelcells = excelworksheet.get_Range(s, Type.Missing);
string excelnumber = excelcells.Value2;
if ((excelnumber != null) && (excelnumber != "")) this.ponedelnic1flor.Add(excelnumber);
z1++;
}
z1 = 0;
z2 = 0;
z3 = 0;
excelappworkbook.Close();
excelappworkbooks.Close();
excelapp.Workbooks.Close();
}
async void ExtractDataFromExcel(CancellationTokenSource cts)
{
Excel.Application excelApp = null;
Excel.Workbooks workBooks = null;
Excel.Workbook workBook = null;
Excel.Sheets workSheets = null;
Excel.Worksheet workSheet = null;
datas = new List <DataModel>();
Stopwatch sw = new Stopwatch();
try
{
OpenFile("Excel files(*.xls*)|*.xls*");
await mainWindow.OutputText.Dispatcher.BeginInvoke(DispatcherPriority.Normal, new Action(delegate()
{
mainWindow.OutputText.Text += "Подождите, идёт обработка файла Excel.\n";
}));
excelApp = new Excel.Application
{
Visible = false,
ScreenUpdating = false,
EnableEvents = false
};
workBooks = excelApp.Workbooks;
workBook = workBooks.Open(FileName);
sw.Start();
workSheets = workBook.Worksheets;
workSheet = (Excel.Worksheet)workSheets.get_Item(1);
ReadFromRow(workSheet);
await mainWindow.OutputText.Dispatcher.BeginInvoke(DispatcherPriority.Normal, new Action(delegate()
{
mainWindow.OutputText.Text += "Файл Excel, обработан. Можете продолжить работу.\n";
sw.Stop();
mainWindow.OutputText.Text += "Время обработки файла Excel: " + (sw.ElapsedMilliseconds / 1000.0).ToString() + " сек.\n";
mainWindow.OutputText.Text += "Обработанно: " + datas.Count + " строк.\n";
mainWindow.OutputText.ScrollToEnd();
}));
await mainWindow.ProcessCorelDRAWFile.Dispatcher.BeginInvoke(DispatcherPriority.Normal, new Action(delegate()
{
mainWindow.ProcessCorelDRAWFile.IsEnabled = true;
}));
}
catch (OperationCanceledException)
{
await mainWindow.OutputText.Dispatcher.BeginInvoke(DispatcherPriority.Normal, new Action(delegate()
{
mainWindow.OutputText.Text += "Операция была отменена пользователем!\n";
mainWindow.OutputText.ScrollToEnd();
}));
}
catch (Exception ex)
{
await mainWindow.OutputText.Dispatcher.BeginInvoke(DispatcherPriority.Normal, new Action(delegate()
{
mainWindow.OutputText.Text += $"Work is failed.\n{ex.Message}\n";
mainWindow.OutputText.ScrollToEnd();
}));
}
finally
{
workBook.Close();
excelApp.Quit();
await mainWindow.ProcessExcelFile.Dispatcher.BeginInvoke(DispatcherPriority.Normal, new Action(delegate()
{
mainWindow.ProcessExcelFile.IsEnabled = true;
}));
Marshal.ReleaseComObject(workSheet);
Marshal.ReleaseComObject(workSheets);
Marshal.ReleaseComObject(workBook);
Marshal.ReleaseComObject(workBooks);
Marshal.ReleaseComObject(excelApp);
}
}
public void Start(bool visible, bool displayAlerts, bool ignoreRemoteRequests)
{
AssertNotDisposed();
if (started)
{
return;
}
try
{
_excel = new Excel.Application();
}
catch (Exception e)
{
throw new Exception("Failed to start Excel.", e);
}
var hWnd = (IntPtr)_excel.Hwnd;
uint processId;
ProcessFunctions.GetWindowThreadProcessId(hWnd, out processId);
excelProcess = Process.GetProcessById((int)processId);
excelProcess.EnableRaisingEvents = true;
excelProcess.Exited += (s, e) =>
{
if (!disposed)
{
Dispose(true);
OnExit(ExitCause.Unknown);
}
};
_excel.Visible = true;
_excel.DisplayAlerts = displayAlerts;
_excel.IgnoreRemoteRequests = ignoreRemoteRequests;
_workbooks = _excel.Workbooks;
workbooks = new List<IWorkbook>();
disposeCallback = sender => workbooks.Remove((Workbook)sender);
started = true;
}
public void Create()//创建一个Microsoft.Office.Interop.Excel对象
{
app = new Microsoft.Office.Interop.Excel.Application();
wbs = app.Workbooks;
wb = wbs.Add(true);
}
public static void DeleteNamesWithExcelApp(String[] tps)
{
Excel.Workbooks xlWorkbooks = null;
Excel.Workbook xlWorkbook = null;
Excel.Names ranges = null;
var xlApp = new Excel.Application();
try
{
foreach (String m in tps)
{
xlWorkbooks = xlApp.Workbooks;
xlWorkbook = xlWorkbooks.Open(m);
ranges = xlWorkbook.Names;
int leftoveritems = ranges.Count;
Excel.Name name = null;
try
{
for (int i = leftoveritems; i >= 1; i--)
{
name = xlWorkbook.Names.Item(i);
name.Delete();
if (name != null)
{
Marshal.ReleaseComObject(name);
}
name = null;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
if (name != null)
{
Marshal.ReleaseComObject(name);
}
}
if (xlWorkbook != null)
{
xlWorkbook.Close(true);
Marshal.ReleaseComObject(xlWorkbook);
xlWorkbook = null;
}
if (xlWorkbooks != null)
{
Marshal.ReleaseComObject(xlWorkbooks);
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
if (ranges != null)
{
Marshal.ReleaseComObject(ranges);
}
if (xlWorkbook != null)
{
Marshal.ReleaseComObject(xlWorkbook);
}
if (xlWorkbooks != null)
{
Marshal.ReleaseComObject(xlWorkbooks);
}
if (xlApp != null)
{
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
xlApp = null;
}
}
}
public RevitUI.Result Execute(RevitUI.ExternalCommandData commandData, ref string message, RevitDB.ElementSet elements)
{
RevitUI.UIDocument uidoc = commandData.Application.ActiveUIDocument;
RevitDB.Document doc = uidoc.Document;
if (doc.IsFamilyDocument)//感觉族文档没必要
{
RevitUI.TaskDialog.Show("Revit", "该操作仅适用项目文档，不适用族文档！");
return(RevitUI.Result.Succeeded);
}
try
{
RevitDB.ViewSchedule vs = doc.ActiveView as RevitDB.ViewSchedule;
RevitDB.TableData td = vs.GetTableData();
RevitDB.TableSectionData tsdh = td.GetSectionData(RevitDB.SectionType.Header);
RevitDB.TableSectionData tsdb = td.GetSectionData(RevitDB.SectionType.Body);
Excel.Application app = new Excel.Application();
Excel.Workbooks wbs = app.Workbooks;
Excel.Workbook wb = wbs.Add(Type.Missing);
Excel.Worksheet ws = wb.Worksheets["Sheet1"];
int cs = tsdb.NumberOfColumns;
int rs = tsdb.NumberOfRows;
Excel.Range rg1 = ws.Cells[1, 1];
Excel.Range rg2 = ws.Cells[1, cs];
rg1.Value = vs.GetCellText(RevitDB.SectionType.Header, 0, 0);
rg2.Value = "";
Excel.Range rg = ws.get_Range(rg1, rg2);
rg.Merge();
rg.Font.Name = "黑体";
rg.Font.Bold = 400;
rg.Font.Size = 14;
rg.Font.ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
rg.HorizontalAlignment = Excel.Constants.xlCenter;
rg.RowHeight = 25;
for (int i = 0; i < rs; i++)
{
for (int j = 0; j < cs; j++)
{
RevitDB.CellType ct = tsdb.GetCellType(i, j);
ws.Cells[i + 2, j + 1] = vs.GetCellText(RevitDB.SectionType.Body, i, j);
}
}
rg1 = ws.Cells[2, 1];
rg2 = ws.Cells[rs + 1, cs];
rg = ws.get_Range(rg1, rg2);
rg.Font.Name = "仿宋";
rg.Font.Size = 11;
rg.Font.ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
rg.HorizontalAlignment = Excel.Constants.xlCenter;
rg.Borders.ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
rg.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
rg.EntireColumn.AutoFit();
rg.RowHeight = 20;
rg.Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).Weight = Excel.XlBorderWeight.xlMedium;
rg.Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).ColorIndex = 3;
rg.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).Weight = Excel.XlBorderWeight.xlMedium;
rg.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).ColorIndex = 3;
rg.Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).Weight = Excel.XlBorderWeight.xlMedium;
rg.Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).ColorIndex = 3;
rg.Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight).Weight = Excel.XlBorderWeight.xlMedium;
rg.Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight).ColorIndex = 3;
SaveFileDialog sf = new SaveFileDialog();
sf.Title = "Revit";
sf.AddExtension = true;
sf.Filter = "Excel2007文件|*.xlsx|Excel2003文件|*.xls|文本文件|*.txt|所有文件|*.*";
sf.FileName = vs.ViewName;
if (DialogResult.OK == sf.ShowDialog())
{
wb.SaveAs(sf.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
app.AlertBeforeOverwriting = false;
wb.Close(true, Type.Missing, Type.Missing);
wbs.Close();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
wb = null;
wbs = null;
app = null;
GC.Collect();
}
catch (Exception)
{
return(RevitUI.Result.Cancelled);
}
RevitUI.TaskDialog.Show("Revit", "Revit Export Excel Successful!");
return(RevitUI.Result.Succeeded);
}
/// <summary>
/// Function for reading marks from excel file
/// </summary>
/// <param name="fileName">The name of file to read</param>
/// <param name="xlApp">The object of Excel app</param>
/// <param name="defMarks">A list for deformation marks</param>
/// <param name="bearMarks">A list for bearing marks</param>
/// <param name="frstBear">The name of first bear mark</param>
/// <param name="scndBear">The name of second bear mark</param>
/// <param name="thrdBear">The name of third bear mark</param>
public static void ExcelReader(string fileName, Excel.Application xlApp, List <DeformationMark> defMarks, List <Mark> bearMarks, string frstBear, string scndBear, string thrdBear)
{
Excel.Workbooks xlWorkBooks = null;
Excel.Workbook xlWorkBook = null;
Excel.Worksheet xlWorkSheet = null;
xlWorkBooks = xlApp.Workbooks;
try
{
xlWorkBook = xlWorkBooks.Open(fileName, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing);
}
catch (Exception ex)
{
xlApp.Quit();
ReleaseObject(xlWorkBooks);
ReleaseObject(xlWorkSheet);
ReleaseObject(xlWorkBook);
ReleaseObject(xlApp);
throw new Exception("ERROR: не удалось открыть файл с именем " + fileName);
}
try
{
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Sheets[1];
}
catch (Exception)
{
xlWorkBook.Close(true, Type.Missing, Type.Missing);
xlApp.Quit();
ReleaseObject(xlWorkBooks);
ReleaseObject(xlWorkSheet);
ReleaseObject(xlWorkBook);
ReleaseObject(xlApp);
throw new Exception("ERROR: не удалось открыть страницу файла " + fileName + " для чтения");
}
var lastCell = xlWorkSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell);
int lastRow = (int)lastCell.Row;
for (int i = 2; i <= (int)lastCell.Row; i++)
{
if (xlWorkSheet.Cells[i, 1].Text.ToString().Equals(frstBear) ||
xlWorkSheet.Cells[i, 1].Text.ToString().Equals(scndBear) ||
xlWorkSheet.Cells[i, 1].Text.ToString().Equals(thrdBear))
{
try
{
Mark newBearMark = new Mark(xlWorkSheet.Cells[i, 1].Text.ToString(),
Math.Round(Double.Parse(xlWorkSheet.Cells[i, 2].Text.ToString().Replace(",", ".")), 5),
Math.Round(Double.Parse(xlWorkSheet.Cells[i, 3].Text.ToString().Replace(",", ".")), 5),
Math.Round(Double.Parse(xlWorkSheet.Cells[i, 4].Text.ToString().Replace(",", ".")), 5));
bearMarks.Add(newBearMark);
} catch (Exception e)
{
xlWorkBook.Close(true, Type.Missing, Type.Missing);
xlApp.Quit();
ReleaseObject(xlWorkBooks);
ReleaseObject(xlWorkSheet);
ReleaseObject(xlWorkBook);
ReleaseObject(xlApp);
throw new Exception("ERROR: неверный формат данных в файле " + fileName);
}
}
else
{
try
{
DeformationMark newDefMark = new DeformationMark(xlWorkSheet.Cells[i, 1].Text.ToString(),
Math.Round(Double.Parse(xlWorkSheet.Cells[i, 2].Text.ToString().Replace(",", ".")), 5),
Math.Round(Double.Parse(xlWorkSheet.Cells[i, 3].Text.ToString().Replace(",", ".")), 5),
Math.Round(Double.Parse(xlWorkSheet.Cells[i, 4].Text.ToString().Replace(",", ".")), 5));
defMarks.Add(newDefMark);
} catch (Exception e)
{
xlWorkBook.Close(true, Type.Missing, Type.Missing);
xlApp.Quit();
ReleaseObject(xlWorkBooks);
ReleaseObject(xlWorkSheet);
ReleaseObject(xlWorkBook);
ReleaseObject(xlApp);
throw new Exception("ERROR: неверный формат данных в файле " + fileName);
}
}
}
bearMarks.Sort(Comparer <Mark> .Create((x, y) => x.Name.CompareTo(y.Name)));
xlWorkBook.Close(false, Type.Missing, Type.Missing);
xlApp.Quit();
ReleaseObject(xlWorkBooks);
ReleaseObject(xlWorkSheet);
ReleaseObject(xlWorkBook);
ReleaseObject(xlApp);
}
/// <summary>
/// Method that opens a new workbook, then opens each workbook listed (currently only excel 2003), pulls the requested cells out, and closes it.
/// </summary>
/// <param name="items">list of filenames</param>
/// <param name="cellrange">cell range(s)</param>
/// <param name="outfile">file to save to</param>
/// <param name="ColumnHeader">Desired column-header for the new excel sheet</param>
public void Work(List <string> items, string cellrange, string outfile, string ColumnHeader)
{
object misValue = Type.Missing;
Excel.Application myExcelApp = new Excel.Application();
Excel.Workbooks myExcelWorkbooks = myExcelApp.Workbooks;
Excel.Workbook outbook = myExcelWorkbooks.Add(misValue);
myExcelApp.DisplayAlerts = false;
myExcelApp.ScreenUpdating = false;
myExcelApp.Visible = false;
myExcelApp.UserControl = false;
myExcelApp.Interactive = false;
try
{
Excel.Worksheet ripped = (Excel.Worksheet)outbook.Worksheets[1];
int cellXPos = 1, cellYPos;
if (!ColumnHeader.Equals(""))
{
Range columnHeaderRange = ripped.Range["B1"];
columnHeaderRange.Value2 = ColumnHeader;
cellXPos++;
}
for (int i = 0; i < items.Count; i++)
{
string filename = items[i];
if (filename.Length > 0)
{
cellYPos = 2;
Range titleRange = ripped.Range["A" + cellXPos];
titleRange.Value2 = filename;
if (filename.Contains(".xlsx"))
{
_thisForm.SetStatus("No logic in place for xlsx files. skipping " + Path.GetFileName(filename));
}
else if (filename.Contains(".xls"))
{
Excel.Workbook currentExcelWorkbook = myExcelWorkbooks.Open(filename, misValue, misValue, misValue, misValue, misValue,
misValue, misValue, misValue, misValue, misValue,
misValue, misValue, misValue, misValue);
Excel.Worksheet sheet = currentExcelWorkbook.Worksheets[1]; // could add in logic to have the user specify which worksheet to look in.
List <string> cells =
new List <string>(cellrange.Split(',').Select(item => item.Trim()).ToArray()); // split the user specified cells into a list of strings.
if (cells.Count == 0)
{
_thisForm.SetStatus("Error: No ranges stated");
return;
}
foreach (string cell in cells)
{
// go over range
if (cell.Contains(":"))
{
string[] cellsplit = cell.Split(':');
Excel.Range xRange = sheet.get_Range(cellsplit[0], cellsplit[1]);
object[,] values2DArray = (object[, ])xRange.Value2;
foreach (var o in values2DArray)
{
if (o == null)
{
continue;
}
string yCol = GetExcelColumnName(cellYPos);
Range newCell = ripped.get_Range(yCol + cellXPos);
newCell.Value2 = o.ToString();
cellYPos++;
}
}
else
{
// add one specific cell
Range xRange = sheet.get_Range(cell);
if (xRange.Text != null && !xRange.Text.Equals(""))
{
string yCol = GetExcelColumnName(cellYPos);
Range newCell = ripped.get_Range(yCol + cellXPos);
newCell.Value2 = xRange.Text;
cellYPos++;
}
}
}
cellXPos++;
currentExcelWorkbook.Close(false, misValue, misValue);
}
_thisForm.SetStatus("Successfully opened " + Path.GetFileName(filename));
Thread.Sleep(50);
}
}
_thisForm.SetStatus("Finished ripping. Saving to excel file: " + outfile);
outbook.Worksheets.Add(ripped);
outbook.SaveCopyAs(outfile);
_thisForm.SetStatus("Saved to " + outfile);
// close any instances of excel
outbook.Close(misValue, misValue, misValue);
myExcelWorkbooks.Close();
myExcelApp.Quit();
Release(outbook);
Release(myExcelWorkbooks);
Release(myExcelApp);
GC.Collect();
GC.WaitForPendingFinalizers();
}
catch (Exception ex)
{
_thisForm.Text = "Error occurred: " + ex.Message;
// close any instances of excel
outbook.Close(misValue, misValue, misValue);
myExcelWorkbooks.Close();
myExcelApp.Quit();
Release(outbook);
Release(myExcelWorkbooks);
Release(myExcelApp);
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
private void ExcelCreate()
{
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); //设置环境变量为en，防止Excel错误0x80028018
app = new Excel.Application();
wbs = app.Workbooks;
Book1 = wbs.Add(true);
Sheet2 = (Excel.Worksheet)Book1.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Sheet2.Name = "Output";
}
public void ExcelClose()
{
//关闭一个Excel对象，销毁对象
Book1.Close(false, Type.Missing, Type.Missing);
wbs.Close();
app.Quit();
Book1 = null;
wbs = null;
app = null;
GC.Collect();
System.Threading.Thread.CurrentThread.CurrentCulture = CurrentCI; //恢复环境变量
}
public Form1()
{
InitializeComponent();
double x, y, d, dx, dy, x0, y0;
int y_step;
excelapp = new Excel.Application();
excelapp.Visible = true;
excelapp.SheetsInNewWorkbook = 3;
excelapp.Workbooks.Add(Type.Missing);
excelapp.DisplayAlerts = true;
excelappworkbooks = excelapp.Workbooks;
excelappworkbook = excelappworkbooks[1];
excelsheets = excelappworkbook.Worksheets;
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1);
excelcells_a1 = excelworksheet.get_Range("A1", Type.Missing);
excelcells = excelcells_a1.get_Offset(0, 0);
///////////////////////////
////// Источники ///////
///////////////////////////
double sourceRadius = 0.2;
sources[0].id = 0;
sources[0].x = 0.27;
sources[0].y = 2.00;
sources[0].radius = sourceRadius;
sources[1].id = 1;
sources[1].x = 1.10;
sources[1].y = 2.81;
sources[1].radius = sourceRadius;
sources[2].id = 2;
sources[2].x = 2.22;
sources[2].y = 2.64;
sources[2].radius = sourceRadius;
sources[3].id = 3;
sources[3].x = 2.80;
sources[3].y = 1.61;
sources[3].radius = sourceRadius;
sources[4].id = 4;
sources[4].x = 2.39;
sources[4].y = 0.51;
sources[4].radius = sourceRadius;
sources[5].id = 5;
sources[5].x = 1.30;
sources[5].y = 0.13;
sources[5].radius = sourceRadius;
sources[6].id = 6;
sources[6].x = 0.36;
sources[6].y = 0.81;
sources[6].radius = sourceRadius;
sources[7].id = 7;
sources[7].x = 1.49;
sources[7].y = 1.50;
sources[7].radius = sourceRadius;
///////////////////////////
////// Приёмники ///////
///////////////////////////
/*
// Вариант один к одному для ВОСЬМИ приёмников
double receiverRadius = 0.23;
double receiverInnerRadius = 0.13;
receiversCount = 8;
receivers[0].id = 0;
receivers[0].x = 0.23;
receivers[0].y = 1.97;
receivers[0].radius = receiverRadius;
receivers[0].innerRadius = receiverInnerRadius;
receivers[1].id = 1;
receivers[1].x = 1.08;
receivers[1].y = 2.76;
receivers[1].radius = receiverRadius;
receivers[1].innerRadius = receiverInnerRadius;
receivers[2].id = 2;
receivers[2].x = 2.23;
receivers[2].y = 2.62;
receivers[2].radius = receiverRadius;
receivers[2].innerRadius = receiverInnerRadius;
receivers[3].id = 3;
receivers[3].x = 2.82;
receivers[3].y = 1.63;
receivers[3].radius = receiverRadius;
receivers[3].innerRadius = receiverInnerRadius;
receivers[4].id = 4;
receivers[4].x = 2.44;
receivers[4].y = 0.54;
receivers[4].radius = receiverRadius;
receivers[4].innerRadius = receiverInnerRadius;
receivers[5].id = 5;
receivers[5].x = 1.31;
receivers[5].y = 0.2;
receivers[5].radius = receiverRadius;
receivers[5].innerRadius = receiverInnerRadius;
receivers[6].id = 6;
receivers[6].x = 0.35;
receivers[6].y = 0.81;
receivers[6].radius = receiverRadius;
receivers[6].innerRadius = receiverInnerRadius;
receivers[7].id = 7;
receivers[7].x = 1.49;
receivers[7].y = 1.50;
receivers[7].radius = receiverRadius;
receivers[7].innerRadius = receiverInnerRadius;
*/
// Полтное гексагональное покрытие приёмниками
x0 = 1.5; // Центр шины - x
y0 = 1.5; // Центр шины - y
/*
dx = 0.46; // Шаг по x для гексагональной разметки
dy = 0.42; // Шаг по y для гексагональной разметки
double receiverRadius = 0.23;
double receiverInnerRadius = 0.0;
*/
dx = 0.46; // Шаг по x для гексагональной разметки
dy = 0.42; // Шаг по y для гексагональной разметки
double receiverRadius = 0.20;
double receiverInnerRadius = 0.10;
y_step = 0;
y = 0;
receiversCount = 0;
while (y < busRadius * 2)
{
// Для гексагональной разметки координаты по x на каждом уровне смещаем по-разному
if (y_step % 2 == 0)
{
x = 0;
}
else
{
x = dx / 2;
}
while (x < busRadius * 2)
{
d = Math.Sqrt((x0 - x) * (x0 - x) + (y0 - y) * (y0 - y));
if (d < busRadius)
{
// Канал в шине (точка в круге)
receivers[receiversCount].x = x;
receivers[receiversCount].y = y;
receivers[receiversCount].radius = receiverRadius;
receivers[receiversCount].innerRadius = receiverInnerRadius;
receiversCount++;
}
x = x + dx;
}
y = y + dy;
y_step++;
}
////////////////////////
////// Каналы ///////
////////////////////////
dx = 0.0429; // Шаг по x для гексагональной разметки
dy = 0.0357; // Шаг по y для гексагональной разметки
x0 = 1.5; // Центр шины - x
y0 = 1.5; // Центр шины - y
double channelRaduis = 0.021; // Радиус одного канала
y_step = 0;
y = 0;
while (y < busRadius * 2)
{
// Для гексагональной разметки координаты по x на каждом уровне смещаем по-разному
if (y_step % 2 == 0)
{
x = 0;
}
else
{
x = dx / 2;
}
while (x < busRadius * 2)
{
d = Math.Sqrt((x0 - x) * (x0 - x) + (y0 - y) * (y0 - y));
if (d < busRadius)
{
// Канал в шине (точка в круге)
channels[channelsCount].x = x;
channels[channelsCount].y = y;
channels[channelsCount].radius = channelRaduis;
channels[channelsCount].isOk = true;
channelsCount++;
}
x = x + dx;
}
y = y + dy;
y_step++;
}
}
public void Setup()
{
resourcePath = @"../../../../test.xlsx";
app = new Excel.Application();
var excelSheetPath = Path.GetFullPath(resourcePath);
wbs = app.Workbooks;
wb = wbs.Open(excelSheetPath);
wss = wb.Worksheets;
ws = (Excel.Worksheet)wss["Tasks"];
excelController = new ExcelController(resourcePath);
}
protected virtual void Dispose(bool disposing)
{
workbook.Close(false);
ExcelApp.Quit();
Marshal.ReleaseComObject(worksheet);
Marshal.ReleaseComObject(sheets);
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(workbooks);
Marshal.ReleaseComObject(ExcelApp);
range = null;
worksheet = null;
sheets = null;
workbook = null;
workbooks = null;
ExcelApp = null;
}
public static void ExportToExcel(ListView pListView)
{
if (pListView.Items == null)
{
return;
}
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = DateTime.Now.ToString("yyyy-MM-dd");
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
return;
}
//这里直接删除，因为saveDialog已经做了文件是否存在的判断
if (File.Exists(saveFileName))
{
File.Delete(saveFileName);
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机器未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
xlApp.Visible = false;
//填充列
for (int i = 0; i < pListView.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = pListView.Columns[i].Text.ToString();
((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1]).Font.Bold = true;
}
//填充数据（这里分了两种情况，1：lv带CheckedBox，2：不带CheckedBox）
//带CheckedBoxes
if (pListView.CheckBoxes == true)
{
int tmpCnt = 0;
for (int i = 0; i < pListView.Items.Count; i++)
{
if (pListView.Items[i].Checked == true)
{
for (int j = 0; j < pListView.Columns.Count; j++)
{
if (j == 0)
{
worksheet.Cells[2 + tmpCnt, j + 1] = pListView.Items[i].Text.ToString();
((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2 + tmpCnt, j + 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
}
else
{
worksheet.Cells[2 + tmpCnt, j + 1] = pListView.Items[i].SubItems[j].Text.ToString();
((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2 + tmpCnt, j + 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
}
}
tmpCnt++;
}
}
}
else //不带Checkedboxe
{
for (int i = 0; i < pListView.Items.Count; i++)
{
for (int j = 0; j < pListView.Columns.Count; j++)
{
if (j == 0)
{
worksheet.Cells[2 + i, j + 1] = pListView.Items[i].Text.ToString();
((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2 + i, j + 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
}
else
{
worksheet.Cells[2 + i, j + 1] = pListView.Items[i].SubItems[j].Text.ToString();
((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2 + i, j + 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
}
}
}
}
object missing = System.Reflection.Missing.Value;
try
{
workbook.Saved = true;
workbook.SaveAs(saveFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlXMLSpreadsheet, missing, missing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
}
catch (Exception e1)
{
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + e1.Message);
}
finally
{
xlApp.Quit();
System.GC.Collect();
}
MessageBox.Show("导出Excle成功！");
}
/// <summary>
/// 导出数据到excel文件
/// </summary>
/// <param name="dt">要导出的数据集</param>
/// <returns>生成的文件名</returns>
static public string ExportToExcel(DataTable dt)
{
Excel.Application excelApp = null;
Excel.Workbooks wbks = null;
Excel._Workbook wbk = null;
try
{
excelApp = new Excel.Application();
excelApp.Visible = false;//是打开不可见
wbks = excelApp.Workbooks;
wbk = wbks.Add(true);
System.Windows.Forms.SaveFileDialog saveFileDialog = new System.Windows.Forms.SaveFileDialog();
saveFileDialog.Title = "保存为";
saveFileDialog.Filter = "xls工作薄|*.xls|xlsx工作薄|*.xlsx";
String version = excelApp.Version; //获取你使用的excel 的版本号
int FormatNum; //保存excel文件的格式
if (Convert.ToDouble(version) < 12) //You use Excel 97-2003
{
{
FormatNum = -4143;
}
}
else//you use excel 2007 or later
{
{
FormatNum = 56;
}
}
object Nothing = Missing.Value;
Excel._Worksheet whs;
whs = (Excel._Worksheet)wbk.Sheets[1];//获取第一张工作表
whs.Activate();
//写入标题行
int rowIndex = 1;
for (int col = 0; col < dt.Columns.Count; col++)
{
whs.Cells[rowIndex, col + 1] = dt.Columns[col].Caption.ToString();
}
rowIndex++;
//写入数据内容
foreach (DataRow row in dt.Rows)
{
for (int colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
{
whs.Cells[rowIndex, colIndex + 1] = row[colIndex].ToString();
}
rowIndex++;
}
excelApp.DisplayAlerts = false;
//保存excel文件
//wbk.SaveCopyAs(@"D:\test.xls");
string newFileName = string.Empty;
if (saveFileDialog.ShowDialog() == DialogResult.OK && saveFileDialog.FileName.Trim() != "")
{
newFileName = saveFileDialog.FileName;
wbk.SaveAs(newFileName, FormatNum);
System.Windows.Forms.MessageBox.Show("数据已经成功导入EXCEL文件" + newFileName, "数据导出", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
//关闭文件
wbk.Close(false, Nothing, Nothing);
return(newFileName);
}
catch (Exception e)
{
System.Windows.Forms.MessageBox.Show(e.ToString());
return("EXPORT ERROR");
}
finally
{
//wbks.Close();//关闭工作簿
excelApp.Quit(); //关闭excel应用程序
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); //释放excel进程
excelApp = null;
}
}
private void IMPORTToolStripMenuItem_Click(object sender, EventArgs e)
{
SimulateFromTextBox = false;
OpenFileDialog openfiledialog1 = new OpenFileDialog();
openfiledialog1.Filter = "Excel Sheet(*.xlsx)|*.xlsx|Excel Sheet(*.xls)|*.xls|All Files(*.*)|*.*";
openfiledialog1.FilterIndex = 1;
if (openfiledialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
path = openfiledialog1.FileName;
}
else if (openfiledialog1.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
{
return;
}
dataGridViewMusking.DataSource = null;
for (int j = 0; j < dataGridViewMusking.Rows.Count - 1; j++)
{
dataGridViewMusking.Rows.RemoveAt(j);
j--;
while (dataGridViewMusking.Rows.Count == 0)
{
continue;
}
}
Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Excel.Workbooks workbooks = app.Workbooks;
Excel.Workbook workbook = workbooks.Open(path);
Excel.Worksheet worksheet = workbook.ActiveSheet;
try
{
value = worksheet.Cells[1, 2].value;
TxtK.Text = value.ToString();
value = worksheet.Cells[2, 2].value;
TxtX.Text = value.ToString();
unit = Convert.ToString(worksheet.Cells[3, 2].value);
if (unit == "Days" || unit == "Hours")
{
ComboBoxInterval.Text = unit;
ComboBoxK.Text = unit;
}
else
{
ComboBoxInterval.Text = "";
ComboBoxK.Text = "";
MessageBox.Show("Unit of Time should be in Days or Hours \n (Days and Hours are Case-Senitive)");
}
/*unit = Convert.ToString(worksheet.Cells[4, 2].value);
* if (unit == "Days" || unit == "Hours")
* {
* ComboBoxK.Text = unit;
* }
* else
* {
* ComboBoxInterval.Text = "";
* MessageBox.Show("Unit of K should be in Days or Hours \n (Days and Hours are Case-Senitive)");
* }*/
//int rcount = worksheet.UsedRange.Rows.Count;
int rcount = n;
int i = 0;
for (i = 0; i < rcount; i++)
{
dataGridViewMusking.Rows.Add();
dataGridViewMusking.Rows[i].Cells["ColTime"].Value = worksheet.Cells[i + 6, 1].value;
dataGridViewMusking.Rows[i].Cells["ColInflow"].Value = worksheet.Cells[i + 6, 2].value;
dataGridViewMusking.Rows[i].Cells["ColOutflow"].Value = worksheet.Cells[i + 6, 3].value;
//worksheet.cells[rows, column].value; here rows column starts from 1 and rows starts from 1 of excel.
}
workbook.Close();
app.Quit();
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(workbooks);
Marshal.ReleaseComObject(worksheet);
// Marshal.ReleaseComObject(rcount);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
workbook.Close();
app.Quit();
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(workbooks);
Marshal.ReleaseComObject(worksheet);
}
MessageBox.Show("IMPORT COMPLETE !");
}
/// <summary>
/// 保持excel
/// </summary>
/// <param name="isChart">是否生成图表</param>
/// <returns></returns>
public bool Save(Boolean isChart)
{
Excel.Application xlApp = new Excel.Application();
Excel.Workbooks xlBooks = xlApp.Workbooks;
Excel.Workbook xlBook = xlBooks.Add(Missing.Value);
Excel.Sheets xlsheets = xlBook.Worksheets;
Excel.Worksheet xlSheet = (Excel.Worksheet)xlsheets.get_Item(1);
int rowIndex = 0;
try
{
List <ChartStuct> csList = new List <ChartStuct>();
if (excelDatas != null)
{
ChartStuct cs = new ChartStuct();
int innerRows = 0;
foreach (ExcelData data in excelDatas)
{
if (isChart)
{
//是多维图表
if (isMulti)
{
cs = new ChartStuct();
//解决如果是柱状图和线图比较，不能只是辅助y轴的问题，将图表类型改为线图
if (data.chartType.Equals(ChartType.column))
{
data.chartType = ChartType.line;
}
}
cs.yname = data.Title;
cs.chartType = convertType(data.chartType);
cs.seriesTitles = data.series;
}
if (data.Rows != null && data.Rows.Count > 0)
{
int rowLength = 0;
int iRows = 0;
foreach (IList <string> column in data.Rows)
{
innerRows++;
iRows++;
rowIndex++;
for (int i = 0; i < column.Count; i++)
{
if (align)
{
xlSheet.Cells[rowIndex, i + 1] = column[i];// string.Format("'{0}", column[i]);
}
else
{
xlSheet.Cells[i + 1, rowIndex] = column[i];// string.Format("'{0}", column[i]);
}
}
rowLength = column.Count;
}
if (isChart)
{
//是多维图表
if (isMulti)
{
cs.dataRange = xlSheet.get_Range(xlApp.Cells[1, rowIndex - iRows + 1], xlApp.Cells[rowLength, rowIndex]);
}
else
{
cs.dataRange = xlSheet.get_Range(xlApp.Cells[1, rowIndex - innerRows + 1], xlApp.Cells[rowLength, rowIndex]);
}
}
}
if (cs.dataRange != null)
{
cs.dataRange.NumberFormat = "@";
cs.dataRange.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
cs.dataRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
//xlSheet.get_Range(xlApp.Cells[rowIndex, 1], xlApp.Cells[rowIndex, column.Count + 1]).NumberFormat = "@";
//xlSheet.get_Range(xlApp.Cells[rowIndex, 1], xlApp.Cells[rowIndex, column.Count + 1]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
csList.Add(cs);
}
}
}
//生成图表
if (isChart)
{
try
{
ExcelUtil.createInnerChart(xlSheet, csList, this.chartName, this.xName);
}
catch (Exception ee)
{
Console.WriteLine(" ExcelUtil.createInnerChart" + ee.Message);
}
}
xlSheet = null;
string fullPath = string.Format("{0}\\{1}.xls", this.excelPath, this.fileName);
if (File.Exists(fullPath))
{
File.Delete(fullPath);
}
xlBook.SaveAs(fullPath, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value);
xlBook.Close(false, Missing.Value, Missing.Value);
return(true);
}
catch (Exception e)
{
Console.WriteLine("Save" + e.Message);
return(false);
}
finally
{
xlApp.Quit();
xlSheet = null;
xlBook = null;
xlApp = null;
GC.Collect();
}
}
public static bool DataGridViewToExcel(ExportInfo exportInfo, DataGridView grid)
{
Excel.ApplicationClass excel = new Excel.ApplicationClass();
Excel.Workbooks workbooks = excel.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];
Excel.Range range = (Excel.Range)worksheet.Columns;
try
{
worksheet.Name = "Sheet1";
range.NumberFormatLocal = "@"; //设置数字格式为文本
range.EntireColumn.AutoFit(); //自动调整列宽
int rowIndex = 0;
rowIndex += 1;
worksheet.Cells[rowIndex, 1] = exportInfo.Title;
rowIndex += 1;
worksheet.Cells[rowIndex, 1] = exportInfo.Filter;
rowIndex += 1;
worksheet.Cells[rowIndex, 1] = exportInfo.Counter;
rowIndex += 1;
//title
int c = 0;
foreach (DataGridViewColumn column in grid.Columns)
{
if (column.Visible != false)
{
c += 1;
range = (Excel.Range)worksheet.Columns[c];
range.ColumnWidth = column.Width / 8; //大概grid列宽除8
worksheet.Cells[rowIndex, c] = column.HeaderText;
}
}
//title Font Color Style
range = worksheet.Range[worksheet.Cells[rowIndex, 1], worksheet.Cells[rowIndex, c]];
range.Font.Bold = true;
range.Interior.ColorIndex = 15;
//rows
int r = rowIndex;
foreach (DataGridViewRow row in grid.Rows)
{
r += 1;
c = 0;
foreach (DataGridViewColumn column in grid.Columns)
{
if (column.Visible != false)
{
c += 1;
//worksheet.Cells[r, c] = Convert.ToString(row.Cells[column.Index].Value);
worksheet.Cells[r, c] = row.Cells[column.Index].Value;
}
}
}
//title + rows LineStyle
range = worksheet.Range[worksheet.Cells[rowIndex, 1], worksheet.Cells[r, c]];
range.Borders.LineStyle = 1;
excel.Visible = true; //显示EXCEL
return(true);
}
catch (Exception e)
{
range.Clear();
Marshal.ReleaseComObject(range);
range = null;
Marshal.ReleaseComObject(worksheet);
worksheet = null;
var saveChanges = false;
workbook.Close(saveChanges);
Marshal.ReleaseComObject(workbook);
workbook = null;
workbooks.Close();
Marshal.ReleaseComObject(workbooks);
workbooks = null;
excel.Quit();
Marshal.ReleaseComObject(excel);
excel = null;
GC.Collect(); //强制垃圾回收。
throw new Exception(e.Message);
}
}
private void PrepareRecord()
{
objRecordExcel = new Excel.Application();
objRecordBooks = objRecordExcel.Workbooks;
if (File.Exists(txtStorage.Text))
objRecordBook = objRecordBooks.Open(txtStorage.Text);
else
objRecordBook = objRecordBooks.Add(true);
objRecordSheets = objRecordBook.Sheets;
objRecordSheet1 = objRecordSheets[1]; // 收件箱
objRecordSheet2 = objRecordSheets[1]; // 已发短信
bool bSheet1Exists = false, bSheet2Exists = false;
foreach (Excel.Worksheet objSheet in objRecordSheets)
{
if (objSheet.Name == "收件箱")
{
objRecordSheet1 = objSheet;
bSheet1Exists = true;
}
else if (objSheet.Name == "已发短信")
{
objRecordSheet2 = objSheet;
bSheet2Exists = true;
}
}
if (!bSheet1Exists)
{
objRecordSheet1 = objRecordSheets.Add();
objRecordSheet1.Name = "收件箱";
objRecordSheet1.Cells[1, 1] = "发件人";
objRecordSheet1.Cells[1, 2] = "短信内容";
objRecordSheet1.Cells[1, 3] = "发送时间";
}
if (!bSheet2Exists)
{
objRecordSheet2 = objRecordSheets.Add();
objRecordSheet2.Name = "已发短信";
objRecordSheet2.Cells[1, 1] = "收件人";
objRecordSheet2.Cells[1, 2] = "短信内容";
objRecordSheet2.Cells[1, 3] = "发送时间";
objRecordSheet2.Cells[1, 4] = "结果";
objRecordSheet2.Cells[1, 5] = "余额";
}
}
/// <summary>
/// 以操作Excel控件的方式将DataGridView数据导出到Excel add by sunny 2007/1/18
/// </summary >
/// <param name= "GridView " >DataGridView对象 </param >
/// <param name= "strExcelFile " >Excel文件名 </param >
/// <param name= "strError " >out参数，返回出错信息 </param >
/// <returns >
/// -1 出错
/// 0 成功
/// </returns >
public static int DataGridViewToExcel(System.Windows.Forms.DataGridView GridView)
{
#region
int nRet = 0;
SaveFileDialog saveDlg1 = new SaveFileDialog();
saveDlg1.DefaultExt = "xls";
saveDlg1.Filter = "Microsoft Office Excel文件(*.xls)|*.xls|所有文件(*.*)|*.*";
saveDlg1.Title = "文件另存为"; //saveDlg1.FileName
//saveDlg1.InitialDirectory = Directory.GetCurrentDirectory();
//if (saveDlg1.ShowDialog() == DialogResult.Cancel) return nRet;
if (saveDlg1.ShowDialog() == DialogResult.Cancel)
{
return(-1);
}
Excel2.Application xlApp = new Excel2.Application();
Excel2.Workbooks workbooks = xlApp.Workbooks;
Excel2.Workbook workbook = workbooks.Add(Excel2.XlWBATemplate.xlWBATWorksheet);
Excel2.Worksheet worksheet = (Excel2.Worksheet)workbook.Worksheets[1];//取得sheet1
try
{
//~~
// 写字段名
for (int i = 0; i < GridView.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = GridView.Columns[i].HeaderText.ToString();
}
// 写记录
for (int i = 0; i < GridView.Rows.Count; i++)
{
for (int j = 0; j < GridView.Columns.Count; j++)
{
//Fu_我的很多dgv里面都是复杂类，无法用这条语句导出
//worksheet.Cells[i + 2, j + 1] = GridView.Rows[i].Cells[j].Value.ToString();
//Fu_11.5_重写，获取格式化后的值
//worksheet.Cells[i + 2, j + 1] = GridView.Rows[i].Cells[j].EditedFormattedValue == null ? "" : GridView.Rows[i].Cells[j].EditedFormattedValue.ToString();
string value = GridView.Rows[i].Cells[j].EditedFormattedValue == null ? "" : GridView.Rows[i].Cells[j].EditedFormattedValue.ToString();
if (value.Length > 11)
{
value = "'" + value;
}
worksheet.Cells[i + 2, j + 1] = value;
}
}
worksheet.Columns.EntireColumn.AutoFit(); //自动适应每列的宽度 add by sunny.li
//下面这句会报错
//Excel.Range rg = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, GridView.Columns.Count]);
//Fu_csdn上的写法 ， 膜拜大神...
Excel2.Range rg = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, GridView.Columns.Count]];
rg.Font.Bold = true;
workbook.Saved = true;
workbook.SaveCopyAs(saveDlg1.FileName);
// 关掉内存中的进程
// xlApp.Quit();
nRet = 0;
}
catch (Exception ex)
{
//strError = ex.ToString();
//MessageBox.Show(ex.ToString());
nRet = -1;
}
return(nRet);
#endregion
}
//打开一个Excel文件
public void Open(string FileName)
{
app = new Excel.Application();
wbs = app.Workbooks;
wb = wbs.Add(FileName);
//wb = wbs.Open(FileName, 0, true, 5,"", "", true, Excel.XlPlatform.xlWindows, "t", false, false, 0, true,Type.Missing,Type.Missing);
//wb = wbs.Open(FileName,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Excel.XlPlatform.xlWindows,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);
mFilename = FileName;
}
public void Create(string strFilePath)
{
_strFilePath = strFilePath;
_application = new Microsoft.Office.Interop.Excel.Application();
_workbooks = _application.Workbooks;
_workbook = _workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
_worksheet = (Microsoft.Office.Interop.Excel.Worksheet)_workbook.Worksheets[1];
}
//创建一个Excel对象
public void Create()
{
app = new Excel.Application();
wbs = app.Workbooks;
wb = wbs.Add(true);
}
internal InventoryWorkBookClass(ref ListBox lbWBooks, ref ListBox lbWSheets)
: this()
{
xlWBooks = xlApp.Workbooks;
if (xlWBooks.Count != 0)
{
foreach (Excel.Workbook wBook in xlWBooks)
{
lbWBooks.Items.Add(wBook.Name);
}
var qry = lbWBooks.Items.Cast<string>().FirstOrDefault(it => Regex.IsMatch(it, ".*inventory.*", RegexOptions.IgnoreCase));
if (qry != null)
{
lbWBooks.SetSelected(lbWBooks.Items.IndexOf(qry), true);
xlWBook = xlWBooks[lbWBooks.Items.IndexOf(qry) + 1];
}
else
{
xlWBook = xlWBooks[1];
lbWBooks.SetSelected(0, true);
}
UpdateLbSheets(ref lbWSheets);
}
else
{
MessageBox.Show("There are no Excel Workbooks open.\rPlease open the Tax-Aide Inventory Workbook", "IDC Merge");
xlApp = null;
Environment.Exit(1);
}
}
public void RunPPTMacro(string pptFileName, string excelFile, string macro, bool visible)
{
//### improve & cleanup code
// Define Workbooks
PowerPoint.Application oPP = null;
PowerPoint.Presentations oPresSet = null;
PowerPoint._Presentation _activePres = null;
object oMissing = System.Reflection.Missing.Value;
string mess = "";
try
{
//open Excel
//System.Diagnostics.Process excelProc = System.Diagnostics.Process.Start(excelFile);
Excel2.Application oExcel = new Excel2.Application();
oExcel.Visible = false;
oExcel.DisplayAlerts = false;
Excel2.Workbooks oBooks = oExcel.Workbooks;
Excel2._Workbook oBook = oBooks.Open(excelFile, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
FileInfo fi = new FileInfo(pptFileName);
//string pptTempName = fi.DirectoryName + @"\TEMP.pptm";
oPP = new PowerPoint.Application();
oPP.DisplayAlerts = Microsoft.Office.Interop.PowerPoint.PpAlertLevel.ppAlertsNone;
//oPP.Visible = MsoTriState.msoFalse;
oPresSet = oPP.Presentations;
_activePres = oPresSet.Open(fi.FullName, MsoTriState.msoFalse, MsoTriState.msoFalse, MsoTriState.msoTrue);
//Object[] oRunArgs = { "'" + fi.Name + "'!M2" };
Object[] oRunArgs = { "'" + fi.Name + "'!" + macro };
oPP.GetType().InvokeMember("Run", System.Reflection.BindingFlags.Default | System.Reflection.BindingFlags.InvokeMethod, null,
oPP, oRunArgs);
//close Excel
try
{
//excelProc.CloseMainWindow();
//excelProc.Close();
//excelProc.Dispose();
//excelProc.Kill();
}
catch (Exception exCloseExcel)
{
//excelProc.Kill();
}
//save excel file
//oBook.Save();
// Quit Excel and clean up.
Thread.Sleep(1000);
oBook.Close(false, oMissing, oMissing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook);
oBook = null;
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks);
oBooks = null;
oExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
oExcel = null;
mess += "kill excel" + Environment.NewLine;
//_activePres.Final = false;
Thread.Sleep(2000);
_activePres.Save();
//_activePres.SaveAs(pptTempName);
mess += "save ppt temp" + Environment.NewLine;
Thread.Sleep(2000);
//_activePres.Final = true;
// Quit PPT and clean up.
if (_activePres != null)
{
mess += "check activepres = null" + Environment.NewLine;
try
{
_activePres.Close();
mess += "activepres close" + Environment.NewLine;
}
catch (Exception exClosePresentation) { }
System.Runtime.InteropServices.Marshal.ReleaseComObject(_activePres);
mess += "release activepres" + Environment.NewLine;
_activePres = null;
mess += "activepres = null" + Environment.NewLine;
System.Runtime.InteropServices.Marshal.ReleaseComObject(oPresSet);
mess += "release presset" + Environment.NewLine;
oPresSet = null;
mess += "presset null" + Environment.NewLine;
try
{
Thread.Sleep(1000);
oPP.Quit();
mess += "opp quit" + Environment.NewLine;
}
catch (Exception exQuitPPT) { }
System.Runtime.InteropServices.Marshal.ReleaseComObject(oPP);
mess += "release opp" + Environment.NewLine;
oPP = null;
mess += "opp = null" + Environment.NewLine;
}
GC.Collect();
mess += "garbage" + Environment.NewLine;
//Kill PPT File
var processes = from p in Process.GetProcessesByName("POWERPNT") select p;
foreach (var process in processes)
{
//if (process.MainWindowTitle == "TEMP.pptm")
try
{
mess += "test process null" + Environment.NewLine;
if (process != null)
{
process.Close();
Thread.Sleep(1000);
mess += "close process" + Environment.NewLine;
if (process != null)
{
process.Kill();
mess += "kil process" + Environment.NewLine;
}
}
}
catch (Exception exCloseProc) { }
}
//rename file & delete temp file
//if (File.Exists(pptFileName))
//{
// File.Delete(pptFileName);
// mess += "delete ppt" + Environment.NewLine;
// File.Move(pptTempName, pptFileName);
// mess += "move temp" + Environment.NewLine;
//}
//if (File.Exists(pptTempName))
// File.Delete(pptTempName);
//mess += "delete temp" + Environment.NewLine;
}
catch (Exception ex)
{
if (_activePres != null)
{
try
{
_activePres.Close();
}
catch (Exception ex2) { }
System.Runtime.InteropServices.Marshal.ReleaseComObject(oPresSet);
oPresSet = null;
System.Runtime.InteropServices.Marshal.ReleaseComObject(_activePres);
_activePres = null;
}
if (oPP != null)
{
try
{
oPP.Quit();
}
catch (Exception ex2) { }
System.Runtime.InteropServices.Marshal.ReleaseComObject(oPP);
oPP = null;
}
log.Error("Error :: RunPPTMacro : " + ex.Message);
throw ex;
}
}
public bool FuncionPrincipal(string usuario, string contraseña, string fechaInicial, string fechafinal)
{
bool exito = false;
string anioselect, AXConcat = "", fechas = "";
int value = 0;
var driverService = ChromeDriverService.CreateDefaultService();
driverService.HideCommandPromptWindow = true;
driver = new ChromeDriver(driverService, new ChromeOptions());
//driver.Navigate().GoToUrl("http://www.provecomer.com.mx/htmlProvecomer/provecomer.html");
driver.Navigate().GoToUrl("http://www.provecomer.com.mx/htmlProvecomer/provecomer.html");
driver.FindElement(By.Name("proveedor")).SendKeys(usuario); //colocando usuario en la pagina "904482"
driver.FindElement(By.Name("password")).SendKeys(contraseña); //colocando pass en la pagina j223j135
Wait("Name", "enviar1", driver);
driver.FindElement(By.Name("enviar1")).Click();
Thread.Sleep(5000);
Wait("Name", "areaTrabajo", driver);
driver.SwitchTo().Frame("areaTrabajo");
Wait("Id", "boxclose", driver);
int ventana = driver.FindElements(By.Id("boxclose")).Count;
if (ventana > 0)
{
driver.FindElement(By.Id("boxclose")).Click();
}
driver.SwitchTo().DefaultContent();
Wait("Name", "menu", driver);
driver.SwitchTo().Frame("menu");
driver.FindElement(By.LinkText("Parametros de Consulta")).Click(); //accediendo al menu del calendario
System.Threading.Thread.Sleep(1000);
IWebDriver driver2 = driver.SwitchTo().Window(driver.WindowHandles.Last()); //obteniendo la instancia del segundo pop up donde sale el calendario
//
string[] FI = fechaInicial.Split('/');
driver.FindElement(By.Name("dia1")).Clear();
driver.FindElement(By.Name("dia1")).SendKeys(FI[0]);
//if (FI[1] == "01" || FI[1] == "1")
switch (FI[1])
{
case "01": driver.FindElement(By.Name("mes1")).SendKeys("Ene"); break;
case "02": driver.FindElement(By.Name("mes1")).SendKeys("Feb"); break;
case "03": driver.FindElement(By.Name("mes1")).SendKeys("Mar"); break;
case "04": driver.FindElement(By.Name("mes1")).SendKeys("Abr"); break;
case "05": driver.FindElement(By.Name("mes1")).SendKeys("May"); break;
case "06": driver.FindElement(By.Name("mes1")).SendKeys("Jun"); break;
case "07": driver.FindElement(By.Name("mes1")).SendKeys("Jul"); break;
case "08": driver.FindElement(By.Name("mes1")).SendKeys("Ago"); break;
case "09": driver.FindElement(By.Name("mes1")).SendKeys("Sep"); break;
case "10": driver.FindElement(By.Name("mes1")).SendKeys("Oct"); break;
case "11": driver.FindElement(By.Name("mes1")).SendKeys("Nov"); break;
case "12": driver.FindElement(By.Name("mes1")).SendKeys("Dic"); break;
}
driver.FindElement(By.Name("dia1")).Clear();
driver.FindElement(By.Name("dia1")).SendKeys(FI[0]);
driver.FindElement(By.Name("anno1")).SendKeys(FI[2]);
driver.FindElement(By.Name("anno1")).SendKeys(FI[2]);
string[] fechaMod = fechafinal.Split('/');
driver.FindElement(By.Name("dia2")).Clear();
driver.FindElement(By.Name("dia2")).SendKeys(fechaMod[0]);
switch (fechaMod[1])
{
case "01": driver.FindElement(By.Name("mes2")).SendKeys("Ene"); break;
case "02": driver.FindElement(By.Name("mes2")).SendKeys("Feb"); break;
case "03": driver.FindElement(By.Name("mes2")).SendKeys("Mar"); break;
case "04": driver.FindElement(By.Name("mes2")).SendKeys("Abr"); break;
case "05": driver.FindElement(By.Name("mes2")).SendKeys("May"); break;
case "06": driver.FindElement(By.Name("mes2")).SendKeys("Jun"); break;
case "07": driver.FindElement(By.Name("mes2")).SendKeys("Jul"); break;
case "08": driver.FindElement(By.Name("mes2")).SendKeys("Ago"); break;
case "09": driver.FindElement(By.Name("mes2")).SendKeys("Sep"); break;
case "10": driver.FindElement(By.Name("mes2")).SendKeys("Oct"); break;
case "11": driver.FindElement(By.Name("mes2")).SendKeys("Nov"); break;
case "12": driver.FindElement(By.Name("mes2")).SendKeys("Dic"); break;
}
driver.FindElement(By.Name("dia2")).Clear();
driver.FindElement(By.Name("dia2")).SendKeys(fechaMod[0]);
driver.FindElement(By.Name("anno2")).SendKeys(fechaMod[2]);
driver2.FindElement(By.XPath("/html/body/form/table/tbody/tr[6]/td/table/tbody/tr/td[1]/a")).Click();
driver2.FindElement(By.XPath("/html/body/table/tbody/tr[8]/td/a/img")).Click();
System.Threading.Thread.Sleep(5000);
driver.SwitchTo().Window(driver.WindowHandles.Last());
driver.SwitchTo().DefaultContent();
Thread.Sleep(5000);
driver.SwitchTo().Frame("menu");
driver.FindElement(By.LinkText("Desgloses")).Click();
Thread.Sleep(5000);
driver.FindElement(By.LinkText("Folios por rango de fechas")).Click();
Wait("Id", "miTabla7", driver);
driver.SwitchTo().DefaultContent();
driver.SwitchTo().Frame("areaTrabajo");
System.Threading.Thread.Sleep(5000);
int cont = 2, numehoja = 1, val = 1;
Wait("Class", "liga", driver);
var col = driver.FindElements(By.ClassName("liga"));
MiExcel = new Excel.Application();
MiExcel.DisplayAlerts = false;
Excel.Workbooks book = MiExcel.Workbooks;
do
{
Thread.Sleep(10);
} while (!MiExcel.Application.Ready);
ArchivoTrabajoExcel = book.Add();
MiExcel.Visible = true;
((Excel.Worksheet) this.MiExcel.Sheets[1]).Select();
do
{
Thread.Sleep(10);
} while (!MiExcel.Application.Ready);
HojaExcel = (Excel.Worksheet)ArchivoTrabajoExcel.ActiveSheet;
HojaExcel.Activate();
HojaExcel.Columns.EntireColumn.NumberFormat = "@";
HojaExcel.Name = "Estado de Cuenta";
for (int i = 0; i < titulos.Length; i++)
{
HojaExcel.Cells[1, i + 1] = titulos[i];
}
ICollection <IWebElement> aux = col;
int conta = 1;
IWebElement tabla_datos_For = driver.FindElement(By.TagName("tbody"));
var tr_collection_For = tabla_datos_For.FindElements(By.TagName("tr"));
int contLink = 0;
for (int ind_tr = 0; ind_tr < tr_collection_For.Count; ind_tr++)
{
Wait("TagName", "tbody", driver);
IWebElement tabla_datos = driver.FindElement(By.TagName("tbody"));
var tr_collection = tabla_datos.FindElements(By.TagName("tr"));
var td = tr_collection[ind_tr].FindElements(By.TagName("td"));
if (contLink >= 9 && td.Count == 14)
{
Wait("xPath", "//*[@id=\"GeneraReporteFrm\"]/table/tbody/tr[" + ind_tr + "]/td[2]/a/img", driver);
var id = tr_collection[ind_tr].FindElement(By.XPath("//*[@id=\"GeneraReporteFrm\"]/table/tbody/tr[" + ind_tr + "]/td[2]/a/img"));
id.Click();
Wait("xPath", "//*[@id=\"GeneraReporteFrm\"]/table/tbody", driver);
IWebElement tabla1 = driver.FindElement(By.XPath("//*[@id=\"GeneraReporteFrm\"]/table/tbody"));
PintarTabla(tabla1, HojaExcel);
Wait("xPath", "//*[@id='GeneraReporteFrm']/table/tbody/tr[1]/td/table/tbody/tr/td[3]/a", driver);
driver.FindElement(By.XPath("//*[@id='GeneraReporteFrm']/table/tbody/tr[1]/td/table/tbody/tr/td[3]/a")).Click();
numehoja = numehoja + 1;
}
contLink++;
}
HojaExcel.Columns.EntireRow.AutoFit();
if (!Directory.Exists(rutaEscritorio))
{
Directory.CreateDirectory(rutaEscritorio);
}
ArchivoTrabajoExcel.SaveAs(rutaEscritorio + "Fresko Estado de Cuenta " + nombreAleatorio() + ".xlsx");
try
{
driver.Close();
driver.Quit();
}
catch { }
exito = true;
return(exito);
}
public void RunExcelMacro(string excelFileName, string macro, bool visible)
{
//### improve & cleanup code
// Define Workbooks
Excel2.Application oExcel = null;
Excel2.Workbooks oBooks = null;
Excel2._Workbook oBook = null;
object oMissing = System.Reflection.Missing.Value;
try
{
//### test thread culture
System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("fr-FR");
FileInfo fi = new FileInfo(excelFileName);
// Create an instance of Microsoft Excel
//Excel2.ApplicationClass oExcel = new Excel2.ApplicationClass();
oExcel = new Excel2.Application();
oExcel.Visible = visible;
oExcel.DisplayAlerts = true;
oBooks = oExcel.Workbooks;
//Open the file, using the 'path' variable
oBook = oBooks.Open(excelFileName, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
//### activate
//Object[] oRunArgs = { "'" + fi.Name + "'!FULL.FULL" };
Object[] oRunArgs = { "'" + fi.Name + "'!" + macro };
oExcel.GetType().InvokeMember("Run", System.Reflection.BindingFlags.Default | System.Reflection.BindingFlags.InvokeMethod, null,
oExcel, oRunArgs);
//oRunArgs = new object[] { fi.Name + "!exportFile" };
//oExcel.GetType().InvokeMember("Run", System.Reflection.BindingFlags.Default | System.Reflection.BindingFlags.InvokeMethod, null,
// oExcel, oRunArgs);
//save excel file
oBook.Save();
// Quit Excel and clean up.
oBook.Close(false, oMissing, oMissing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook);
oBook = null;
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks);
oBooks = null;
oExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
oExcel = null;
//Garbage collection
GC.Collect();
}
catch (Exception ex)
{
if (oBook != null)
{
try
{
oBook.Close(false, oMissing, oMissing);
}
catch (Exception ex2) { }
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook);
oBook = null;
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks);
oBooks = null;
}
if (oExcel != null)
{
try
{
oExcel.Quit();
}
catch (Exception ex2) { }
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
oExcel = null;
}
log.Error("Error :: RunExcelMacro : " + ex.Message);
throw ex;
}
}
/// <summary> Implements <see cref="IExcelTemplateManager.AddView"/> </summary>
public IExcelTemplateView AddView(string sheetTemplatePath, string templateName, string sheetDestinationName, string destinationRange, string clearingCellName)
{
ExcelInterop.Workbooks workbooks = null;
ExcelInterop.Workbook workbook = null;
ExcelInterop.Worksheet sheetContainer = null;
ExcelInterop.Worksheet sheetDestination = null;
try
{
if (string.IsNullOrEmpty(sheetTemplatePath))
{
throw new ArgumentNullException("the sheet container name is mandatory");
}
if (sheetDestinationName == null)
{
throw new ArgumentNullException("Destination sheet name is mandatory");
}
string sheetTemplateName;
if (sheetTemplatePath.Contains("|"))
{
sheetTemplateName = sheetTemplatePath.Substring(sheetTemplatePath.LastIndexOf("|") + 1);
string workbookPath = sheetTemplatePath.Substring(sheetTemplatePath.LastIndexOf("|") - 1);
workbooks = ETKExcel.ExcelApplication.Application.Workbooks;
workbook = workbooks.Open(workbookPath, true, true);
}
else
{
sheetTemplateName = sheetTemplatePath;
workbook = ETKExcel.ExcelApplication.Application.ActiveWorkbook;
}
sheetContainer = ETKExcel.ExcelApplication.GetWorkSheetFromName(workbook, sheetTemplateName);
if (sheetContainer == null)
{
throw new ArgumentException($"Cannot find the Destination sheet '{sheetTemplatePath}'");
}
sheetDestination = ETKExcel.ExcelApplication.GetWorkSheetFromName(workbook, sheetDestinationName);
if (sheetDestination == null)
{
throw new ArgumentException($"Cannot find the Destination sheet '{sheetDestinationName}'");
}
ExcelInterop.Range clearingCell = null;
if (!string.IsNullOrEmpty(clearingCellName))
{
clearingCell = ETKExcel.ExcelApplication.Application.Range[clearingCellName];
if (clearingCell == null)
{
throw new ArgumentException($"Cannot find the clearing cell '{clearingCellName}'. Please use the 'sheetname!rangeaddress' format");
}
}
ExcelInterop.Range destinationRangeRange = sheetDestination.Range[destinationRange];
IExcelTemplateView view = AddView(sheetContainer, templateName, sheetDestination, destinationRangeRange, clearingCell);
return(view);
}
catch (Exception ex)
{
string message = $"Sheet '{(sheetDestination != null ? sheetDestination.Name.EmptyIfNull() : string.Empty)}', cannot add the View from template '{sheetTemplatePath.EmptyIfNull()}.{templateName.EmptyIfNull()}'";
Logger.Instance.LogException(LogType.Error, ex, message);
throw new EtkException(message, ex);
}
finally
{
if (sheetContainer != null)
{
ExcelApplication.ReleaseComObject(sheetContainer);
sheetContainer = null;
}
if (workbook != null)
{
ExcelApplication.ReleaseComObject(workbook);
workbook = null;
}
if (workbooks != null)
{
ExcelApplication.ReleaseComObject(workbooks);
workbooks = null;
}
}
}
public void Translator(string ss)
{
if (comboBox1.Text == "")
{
MessageBox.Show("Выберите срок расчета");
}
else
{
excelapp = new Excel.Application(); // создание нового Excel процесса
System.Diagnostics.Process excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL").Last();
excelappworkbooks = excelapp.Workbooks;
excelappworkbook = excelapp.Workbooks.Add(Application.StartupPath + @"\Regression"); // путь к файлу
excelsheets = excelappworkbook.Worksheets;
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1); // номер листа в рабочей книге
excelworksheet.Range["B1"].Value2 = ss;
string ff;
switch (comboBox1.Text) // выбор срока расчета
{
case ("1 месяц"):
ff = "MICEX10(1).txt";
break;
case ("3 месяца"):
ff = "MICEX10(3).txt";
break;
case ("1 год"):
ff = "MICEX10(1year).txt";
break;
default:
ff = "1";
break;
}
try
{
excelworksheet.Range["A1"].Value2 = ff;
List <string> myList = new List <string>();
List <string> List11 = new List <string>();
using (FileStream fs = new FileStream(ff, FileMode.Open)) // считывание информации из текстовых файлов
{
using (FileStream fss = new FileStream(ss, FileMode.Open))
{
using (StreamReader sr = new StreamReader(fs))
{
using (StreamReader srr = new StreamReader(fss))
{
string s;
string s1;
int x = 0, y = 0;
try
{
do
{
s = sr.ReadLine();
s = s.Replace(".", ",");
myList.Add(s);
s1 = srr.ReadLine();
s1 = s1.Replace(".", ",");
List11.Add(s1);
if (s != null)
{
x++;
}
if (s1 != null)
{
y++;
}
}while (s != null); // считывание информации из текстовых файлов
}
catch (NullReferenceException) { }
double[] dds = new double[x];
double[] dd = new double[x - 1];
double[] dds1 = new double[y];
double[] dd1 = new double[y - 1];
int j = 4;
double sum1, sum3, sum2 = 0;
try
{
for (int i = 0; i < x; i++) // форматирование считанной информации
{
string[] split = myList[i].Split(new char[] { '\t' });
dds[i] = Convert.ToDouble(split[j]);
if (i > 0)
{
sum1 = ((dds[i] - dds[i - 1]) / dds[i - 1]) * 100;
dd[i - 1] = Math.Round(sum1, 3);
excelworksheet.Range["A" + (i + 1).ToString()].Value2 = dd[i - 1];
sum2 += dd[i - 1];
}
progressBar1.Value = i * 100 / (x - 1);
}
}
catch (FormatException)
{
MessageBox.Show("Проверьте корректность обрабатываемого файла " + ff.ToString());
}
try
{
for (int i = 0; i < y; i++) // форматирование считанной информации
{
string[] split = List11[i].Split(new char[] { '\t' });
dds1[i] = Convert.ToDouble(split[j]);
if (i > 0)
{
sum1 = ((dds1[i] - dds1[i - 1]) / dds1[i - 1]) * 100;
dd1[i - 1] = Math.Round(sum1, 3);
excelworksheet.Range["B" + (i + 1).ToString()].Value2 = dd1[i - 1];
}
}
}
catch (FormatException)
{
MessageBox.Show("Проверьте корректность обрабатываемого файла " + ss.ToString());
}
sum3 = Math.Round(sum2 / (x - 1), 3);
dataGridView1.Rows[0].Cells[1].Value = sum3;
double[] massiv_1 = new double[x - 1];
for (int i = 0; i < massiv_1.Length; i++)
{
massiv_1[i] = 1;
}
double matr_1_1 = 0, matr_1_2 = 0, matr_2_1 = 0, matr_2_2 = 0; // расчет обратной матрицы
for (int i = 0; i < x - 1; i++)
{
matr_1_1 += Math.Pow(massiv_1[i], 2);
matr_1_2 += massiv_1[i] * dd[i];
matr_2_1 += dd[i] * massiv_1[i];
matr_2_2 += Math.Pow(dd[i], 2);
}
double opredelitel = matr_1_1 * matr_2_2 - matr_1_2 * matr_2_1; // Далее расчет по формулам в Excel
double zamena = matr_1_1;
matr_1_1 = matr_2_2 / opredelitel;
matr_2_2 = zamena / opredelitel;
double R, R_2, Error, Looks, dF = 1, Ostatok, Itogo, SS1, SS2, SS3, MS, F, Y, X1, error1, error2, t_Stat1, t_Stat2, P1, P2;
excelworksheet.Range["C2"].Formula = "=CORREL(B2:B" + x.ToString() + ",A2:A" + x.ToString() + ")";
R = excelworksheet.Range["C2"].Value2;
R_2 = Math.Pow(R, 2);
excelworksheet.Range["C3"].Formula = "=STEYX(B2:B" + x.ToString() + ",A2:A" + x.ToString() + ")";
Error = excelworksheet.Range["C3"].Value2;
excelworksheet.Range["C4"].Formula = "=COUNT(A2:A" + x.ToString() + ")";
Looks = excelworksheet.Range["C4"].Value2;
Ostatok = (Looks - dF) - 1;
Itogo = Looks - dF;
excelworksheet.Range["C5"].Value2 = "=DEVSQ(B2:B" + x.ToString() + ")";
SS3 = excelworksheet.Range["C5"].Value2;
SS1 = SS3 * R_2;
SS2 = SS3 - SS1;
MS = Math.Pow(Error, 2);
F = (SS1 / dF) / (SS2 / Ostatok);
excelworksheet.Range["C6"].Formula = "=INDEX(LINEST(B2:B" + x.ToString() + ",A2:A" + x.ToString() + "),2)";
Y = excelworksheet.Range["C6"].Value2;
excelworksheet.Range["C7"].Formula = "=INDEX(LINEST(B2:B" + x.ToString() + ",A2:A" + x.ToString() + "),1)";
X1 = excelworksheet.Range["C7"].Value2;
matr_1_1 = matr_1_1 * MS;
matr_2_2 = matr_2_2 * MS;
error1 = Math.Sqrt(matr_1_1);
error2 = Math.Sqrt(matr_2_2);
t_Stat1 = Y / error1;
t_Stat2 = X1 / error2;
excelworksheet.Range["C8"].Value2 = t_Stat1;
excelworksheet.Range["C9"].Value2 = t_Stat2;
excelworksheet.Range["C10"].Value2 = Ostatok;
excelworksheet.Range["C11"].Formula = "=TDIST(ABS(C8),C10,2)";
excelworksheet.Range["C12"].Formula = "=TDIST(ABS(C9),C10,2)";
P1 = excelworksheet.Range["C11"].Value2;
P2 = excelworksheet.Range["C12"].Value2;
excelapp.DisplayAlerts = false; ///////////
excelappworkbook.Close();
excelapp.Application.Quit();
excelProc.Kill();
dataGridView3.Rows[3].Cells[1].Value = Math.Round(R, 6); // вывод информации на форму
dataGridView3.Rows[4].Cells[1].Value = Math.Round(R_2, 6);
dataGridView3.Rows[5].Cells[1].Value = Math.Round(Error, 6);
dataGridView3.Rows[6].Cells[1].Value = Math.Round(Looks, 6);
dataGridView3.Rows[10].Cells[1].Value = Math.Round(dF, 6);
dataGridView3.Rows[11].Cells[1].Value = Math.Round(Ostatok, 6);
dataGridView3.Rows[12].Cells[1].Value = Math.Round(Itogo, 6);
dataGridView3.Rows[15].Cells[1].Value = Math.Round(Y, 6);
dataGridView3.Rows[16].Cells[1].Value = Math.Round(X1, 6);
dataGridView3.Rows[10].Cells[2].Value = Math.Round(SS1, 6);
dataGridView3.Rows[11].Cells[2].Value = Math.Round(SS2, 6);
dataGridView3.Rows[12].Cells[2].Value = Math.Round(SS3, 6);
dataGridView3.Rows[15].Cells[2].Value = Math.Round(error1, 6);
dataGridView3.Rows[16].Cells[2].Value = Math.Round(error2, 6);
dataGridView3.Rows[11].Cells[3].Value = Math.Round(MS, 6);
dataGridView3.Rows[15].Cells[3].Value = Math.Round(t_Stat1, 6);
dataGridView3.Rows[16].Cells[3].Value = Math.Round(t_Stat2, 6);
dataGridView3.Rows[10].Cells[4].Value = Math.Round(F, 6);
dataGridView3.Rows[15].Cells[4].Value = Math.Round(P1, 6);
dataGridView3.Rows[16].Cells[4].Value = Math.Round(P2, 6);
}
}
}
}
}
catch (FileNotFoundException) {
MessageBox.Show("Выберите корректный срок расчета из выпадающего списка");
}
}
}
/// <summary>
/// Get last used column for a row
/// </summary>
/// <param name="fileName">Excel file to read</param>
/// <param name="sheetName">Sheet to work on</param>
/// <param name="row">Row in sheet to get last used column</param>
/// <returns></returns>
public int LastColumnForRow(string fileName, string sheetName, int row)
{
int lastColumn = -1;
if (File.Exists(fileName))
{
Excel.Application xlApp = null;
Excel.Workbooks xlWorkBooks = null;
Excel.Workbook xlWorkBook = null;
Excel.Worksheet xlWorkSheet = null;
Excel.Sheets xlWorkSheets = null;
xlApp = new Excel.Application();
xlApp.DisplayAlerts = false;
xlWorkBooks = xlApp.Workbooks;
xlWorkBook = xlWorkBooks.Open(fileName);
xlApp.Visible = false;
xlWorkSheets = xlWorkBook.Sheets;
for (int x = 1; x <= xlWorkSheets.Count; x++)
{
xlWorkSheet = (Excel.Worksheet)xlWorkSheets[x];
if (xlWorkSheet.Name == sheetName)
{
Excel.Range xlCells = null;
xlCells = xlWorkSheet.Cells;
Excel.Range workRange = xlCells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell);
Excel.Range xlColumns = xlWorkSheet.Columns;
int count = xlColumns.Count;
Marshal.FinalReleaseComObject(xlColumns);
xlColumns = null;
Excel.Range xlLastRange = (Excel.Range)xlWorkSheet.Cells[row, count];
Excel.Range xlDirRange = xlLastRange.End[Excel.XlDirection.xlToLeft];
Marshal.FinalReleaseComObject(xlLastRange);
xlLastRange = null;
lastColumn = xlDirRange.Column;
Marshal.FinalReleaseComObject(xlDirRange);
xlDirRange = null;
Marshal.FinalReleaseComObject(workRange);
workRange = null;
Marshal.FinalReleaseComObject(xlCells);
xlCells = null;
break;
}
Marshal.FinalReleaseComObject(xlWorkSheet);
xlWorkSheet = null;
}
xlWorkBook.Close();
xlApp.UserControl = true;
xlApp.Quit();
Release(xlWorkSheets);
Release(xlWorkSheet);
Release(xlWorkBook);
Release(xlWorkBooks);
Release(xlApp);
return(lastColumn);
}
else
{
throw new Exception("'" + fileName + "' not found.");
}
}
public void ExportExcel(string fileName, string tablename)
{
/*string saveFileName = "";
* //bool fileSaved = false;
* SaveFileDialog saveDialog = new SaveFileDialog();
* saveDialog.DefaultExt = "xlsx";
* saveDialog.Filter = "Excel文件|*.xlsx";
* saveDialog.FileName = fileName;
* saveDialog.ShowDialog();
* Cursor.Current = Cursors.WaitCursor;*/
string saveFileName = fileName;
if (saveFileName.IndexOf(":") < 0)
{
return; //被点了取消
}
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return;
}
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
commondb mydb = new commondb();
string mysql = "select * from " + tablename;
DataSet mydataset = mydb.ExecuteQuery(mysql, tablename);
DataTable dt = mydataset.Tables[0];
//写入标题
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
}
//写入数值
Cursor.Current = Cursors.WaitCursor;
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i
< dt.Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i];
}
System.Windows.Forms.Application.DoEvents();
Cursor.Current = Cursors.WaitCursor;
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
//if (Microsoft.Office.Interop.cmbxType.Text != "Notification")
//{
// Excel.Range rg = worksheet.get_Range(worksheet.Cells[2, 2], worksheet.Cells[ds.Tables[0].Rows.Count + 1, 2]);
// rg.NumberFormat = "00000000";
//}
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
//fileSaved = true;
}
catch (Exception ex)
{
//fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
//else
//{
// fileSaved = false;
//}
xlApp.Quit();
GC.Collect();//强行销毁
// if (fileSaved && System.IO.File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName); //打开EXCEL
}
// 客户名 总金额
public void ExportToExcelFun(System.Data.DataTable dt, String name, double sum)
{
MysqlManager mysqlManager = new MysqlManager();
String phone = mysqlManager.getPhone(name);
if (dt == null)
{
return;
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的电脑未安装Excel");
return;
}
System.Windows.Forms.SaveFileDialog saveDia = new SaveFileDialog();
saveDia.Filter = "Excel|*.xlsx";
saveDia.Title = "导出为Excel文件";
saveDia.FileName = DateTime.Now.ToString("yyyyMMddhhss") + name;
if (saveDia.ShowDialog() == System.Windows.Forms.DialogResult.OK &&
!string.Empty.Equals(saveDia.FileName))
{
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.Workbook workbook = workbooks.Add(System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "\\Template.xlsx");
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
Microsoft.Office.Interop.Excel.Range range = null;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
string fileName = saveDia.FileName;
////写入标题
//for (int i = 0; i < dt.Columns.Count; i++)
//{
// worksheet.Cells[2, i + 1] = dt.Columns[i].ColumnName;
// range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, i + 1];
// //range.Interior.ColorIndex = 15;//背景颜色
// range.Font.Bold = true;//粗体
// range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;//居中
// //加边框
// range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
// //range.ColumnWidth = 4.63;//设置列宽
// //range.EntireColumn.AutoFit();//自动调整列宽
// //r1.EntireRow.AutoFit();//自动调整行高
//}
//写入内容
// 客户名称
worksheet.Cells[2, 2] = name;
// 电话
worksheet.Cells[3, 2] = phone;
// 单号
Encoding gb2312 = Encoding.GetEncoding("GB2312");
string s = Pinyin.ConvertEncoding(name, Encoding.UTF8, gb2312);
worksheet.Cells[2, 8] = s + "-" + DateTime.Now.Year + "-" + DateTime.Now.Month + "-" + DateTime.Now.Day;
// 开票日期
worksheet.Cells[3, 8] = DateTime.Now.ToString();
int r = 0;
for (r = 0; r < dt.DefaultView.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 6, 1] = r + 1;
worksheet.Cells[r + 6, i + 2] = dt.DefaultView[r][i];
//range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + 6, i + 1];
//range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//range.Font.Size = 12;//字体大小
// //加边框
//range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
//range.EntireColumn.AutoFit();//自动调整列宽
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
System.Windows.Forms.Application.DoEvents();
}
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + 6, 7];
range.Font.Size = 15;
range.Font.Bold = true;
worksheet.Cells[r + 6, 7] = "总计： ";
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + 6, 8];
range.Font.Size = 15;
range.Font.Bold = true;
worksheet.Cells[r + 6, 8] = sum + " （元）";
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + 8, 8];
range.Font.Size = 17;
range.Font.Bold = true;
worksheet.Cells[r + 8, 8] = "经手人： ";
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
if (dt.Columns.Count > 1)
{
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
}
try
{
workbook.Saved = true;
workbook.SaveCopyAs(fileName);
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
return;
}
workbooks.Close();
if (xlApp != null)
{
xlApp.Workbooks.Close();
xlApp.Quit();
int generation = System.GC.GetGeneration(xlApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
System.GC.Collect(generation);
}
GC.Collect();//强行销毁
#region 强行杀死最近打开的Excel进程
System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL");
System.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
MessageBox.Show("导出成功!");
}
}
// Generates an excel spreadsheet with graphs of each summary element by depth and saves each graph as a PNG file.
public static void CreateGTGraphs(string domainName, string timeStamp, int depth, List <List <Tuple <String, String> > > summaries)
{
// The top level log directory.
string directory = Parser.GetTopDirectory() + @"GameTrees\" + domainName + @"\" + timeStamp + @"\";
// The graph image directory.
string imageDir = directory + @"graphs\";
// Check if the image directory exists.
if (!File.Exists(imageDir))
{
// If not, create it.
Directory.CreateDirectory(imageDir);
}
// This is needed for a lot of the Excel initialization tasks.
object misValue = System.Reflection.Missing.Value;
// Create an Excel application object.
Excel.Application xl = new Excel.Application();
// Create an Excel work sheet variable.
Excel.Worksheet xlWorkSheet;
// Create an Excel work books variable and point it at the application object's work books..
Excel.Workbooks xlWorkBooks = xl.Workbooks;
// Make the Excel application visible? Not sure, it's magic.
xl.Visible = true;
// Open the summary CSV file in the Excel work book.
xlWorkBooks.OpenText(directory + "summary.csv", misValue, misValue, Excel.XlTextParsingType.xlDelimited,
Excel.XlTextQualifier.xlTextQualifierNone, misValue, misValue,
misValue, misValue, misValue, misValue, misValue, misValue, misValue,
misValue, misValue, misValue, misValue);
// Grab the work sheet that represents the CSV file.
xlWorkSheet = (Excel.Worksheet)xlWorkBooks[1].Worksheets.get_Item(1);
// Loop through every summary element, excluding the depth count which should be first.
for (int summary = 1; summary < summaries[0].Count; summary++)
{
// Create a new Excel chart holder in the work sheet.
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
// Create a new Excel chart and position it below the table and other charts.
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, (15 * summaries[0].Count) + (220 * (summary - 1)), 360, 210);
// Select the chart object's chart. Don't ask me, this Excel interface is strange.
Excel.Chart chartPage = myChart.Chart;
// Make the chart object active.
myChart.Select();
// Set the chart's style. 227 should be a white background with a blue line.
chartPage.ChartStyle = 227;
// Set the type of chart. We are using a line chart.
chartPage.ChartType = Excel.XlChartType.xlLine;
// Turn the legend off.
chartPage.HasLegend = false;
// Create a new series collection to hold the series that will contain the chart data. Again, Excel's interface is dumb.
Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chartPage.SeriesCollection();
// Create a new series.
Excel.Series series1 = seriesCollection.NewSeries();
// Select the chart's X values. These should be the depth counts.
series1.XValues = xlWorkSheet.Range["summary!$A$2:$A$" + depth];
// Select the chart's Y values. These should be the current summary element data.
series1.Values = xlWorkSheet.Range["summary!$" + ToLetter(summary) + "$2:$" + ToLetter(summary) + "$" + depth];
// Name the chart according to the current summary element.
series1.Name = summaries[0][summary].First;
// Export the current chart as a PNG image.
chartPage.Export(imageDir + series1.Name + ".png", "PNG", false);
}
// Save the current work book as an XLS file.
xlWorkBooks[1].SaveAs(directory + "graphsummary-" + timeStamp + ".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
// Close the current work book.
xlWorkBooks[1].Close(true, misValue, misValue);
// Quit the Excel application.
xl.Quit();
// Do some garbage collection? Not sure, all examples I saw had this.
releaseObject(xlWorkSheet);
releaseObject(xlWorkBooks);
releaseObject(xl);
}
private void button1_Click(object sender, EventArgs e)
{
if (ratingFilePath.Text == "")
{
AddLogMessage("Не выбран файл рейтинга! Рейтинг не будет отгружен!");
// return;
}
if (collectFilePath.Text == "")
{
AddLogMessage("Не выбран файл-накопитель! Накопленные очки не будут отгружены!");
//return;
}
if (competitionFilePath.Text == "")
{
AddLogMessage("Не выбран файл с соревнованиями! Список соревнований не будет отгружен!");
//return;
}
this.Enabled = false;
//объявление имён
excelapp = new Excel.Application();
Excel.Workbooks excelappworkbooks = excelapp.Workbooks;
Excel.Sheets excelsheets;
Excel.Worksheet excelworksheet;
Excel._Workbook excelappworkbook;
//ОБРАБОТКА ФАЙЛА РЕЙТИНГА
if (ratingFilePath.Text != "")
{
string ratingFileName = ratingFilePath.Text;
//Открываем книгу и получаем на нее ссылку
excelappworkbook = excelapp.Workbooks.Open(ratingFileName,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing
);
excelsheets = excelappworkbook.Worksheets;
OleDbConnection theConnection = new OleDbConnection($"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={ratingFileName};Extended Properties=\"Excel 8.0;HDR=YES;\"");
theConnection.Open();
DataSet ds = new DataSet();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = theConnection;
// Get all Sheets in Excel File
DataTable dtSheet = theConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
// Loop through all Sheets to get data
//Получаем ссылку на лист ЖО
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1);
AddLogMessage("Обработка файла рейтинга");
Arg a;
a.data = Parse(dtSheet.Rows[0], cmd, ref excelworksheet); a.table = "jo";
Application.DoEvents();
AddLogMessage($"Лист {a.table} отправляется.");
this.backgroundWorker1.RunWorkerAsync(a);
do
{
Application.DoEvents();
} while (backgroundWorker1.IsBusy);
AddLogMessage($"Отправка завершена");
//Получаем ссылку на лист ЖП
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(2);
a.data = Parse(dtSheet.Rows[1], cmd, ref excelworksheet); a.table = "jp";
AddLogMessage($"Лист {a.table} отправляется.");
this.backgroundWorker1.RunWorkerAsync(a);
do
{
Application.DoEvents();
} while (backgroundWorker1.IsBusy);
AddLogMessage($"Отправка завершена");
//Получаем ссылку на лист ЖС
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(3);
a.data = Parse(dtSheet.Rows[2], cmd, ref excelworksheet); a.table = "js";
AddLogMessage($"Лист {a.table} отправляется.");
this.backgroundWorker1.RunWorkerAsync(a);
do
{
Application.DoEvents();
} while (backgroundWorker1.IsBusy);
AddLogMessage($"Отправка завершена");
//Получаем ссылку на лист МО
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(4);
a.data = Parse(dtSheet.Rows[3], cmd, ref excelworksheet); a.table = "mo";
AddLogMessage($"Лист {a.table} отправляется.");
this.backgroundWorker1.RunWorkerAsync(a);
do
{
Application.DoEvents();
} while (backgroundWorker1.IsBusy);
AddLogMessage($"Отправка завершена");
//Получаем ссылку на лист МП
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(5);
a.data = Parse(dtSheet.Rows[4], cmd, ref excelworksheet); a.table = "mp";
AddLogMessage($"Лист {a.table} отправляется.");
this.backgroundWorker1.RunWorkerAsync(a);
do
{
Application.DoEvents();
} while (backgroundWorker1.IsBusy);
AddLogMessage($"Отправка завершена");
//Получаем ссылку на лист МС
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(6);
a.data = Parse(dtSheet.Rows[5], cmd, ref excelworksheet); a.table = "ms";
AddLogMessage($"Лист {a.table} отправляется.");
this.backgroundWorker1.RunWorkerAsync(a);
do
{
Application.DoEvents();
} while (backgroundWorker1.IsBusy);
AddLogMessage($"Отправка рейтинга завершена");
}
//ОБРАБОТКА СОРЕВНОВАНИЙ
if (competitionFilePath.Text != "")
{
excelappworkbooks = excelapp.Workbooks;
string competitionFileName = competitionFilePath.Text;
//Открываем книгу и получаем на нее ссылку
excelappworkbook = excelapp.Workbooks.Open(competitionFileName,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing
);
excelsheets = excelappworkbook.Worksheets;
AddLogMessage("Обработка файла соревнований");
//Получаем ссылку на лист
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1);
uint l = GetLength(ref excelworksheet) + 7;
competitions = new Competition[l];
string prev = DateTime.Now.ToString("dd.MM.yyyy"); //храним предыдущую датувремя
int cur = 1;
for (uint i = l - 1; i >= 1; i--)
{
for (char j = 'A'; j <= 'C'; j++)
{
Excel.Range cell = excelworksheet.get_Range($"{j}{i}", Type.Missing);
switch (j)
{
case 'A':
if (cell.Font.Color.ToString() != "0")
{
competitions[i - 1].Intl = 1;
}
else
{
competitions[i - 1].Intl = 0;
}
if (DateTime.FromOADate(Convert.ToDouble(cell.Value2)).ToString("dd.MM.yyyy") != prev)
{
competitions[i - 1].Date = DateTime.FromOADate(Convert.ToDouble(cell.Value2)).ToString("dd.MM.yyyy") + $"/1";
prev = DateTime.FromOADate(Convert.ToDouble(cell.Value2)).ToString("dd.MM.yyyy");
cur = 1;
}
else
{
cur++;
competitions[i - 1].Date = DateTime.FromOADate(Convert.ToDouble(cell.Value2)).ToString("dd.MM.yyyy") + $"/{cur}";
prev = DateTime.FromOADate(Convert.ToDouble(cell.Value2)).ToString("dd.MM.yyyy");
}
break;
case 'B':
competitions[i - 1].Name = cell.Value2.ToString();
break;
case 'C':
if (cell.Value2 != null)
{
competitions[i - 1].Place = cell.Value2.ToString();
}
else
{
competitions[i - 1].Place = "";
}
break;
default:
break;
}
}
}
AddLogMessage("Отправка файла соревнований в БД");
backgroundWorker2.RunWorkerAsync(competitions); //подали на отправку
do
{
Application.DoEvents();
} while (backgroundWorker2.IsBusy);
}
//ОБРАБОТКА НАКОПИТЕЛЯ
if (collectFilePath.Text != "")
{
excelappworkbooks = excelapp.Workbooks;
string collectFileName = collectFilePath.Text;
//Открываем книгу и получаем на нее ссылку
excelappworkbook = excelapp.Workbooks.Open(collectFileName,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing
);
excelsheets = excelappworkbook.Worksheets;
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1);//jo
OleDbConnection theConnection = new OleDbConnection($"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={collectFileName};Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"");
theConnection.Open();
DataSet ds = new DataSet();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = theConnection;
// Get all Sheets in Excel File
DataTable dtSheet = theConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
// Loop through all Sheets to get data
Arg2 intoWorker;
AddLogMessage("Обработка накопителя ЖО (1/6)");
intoWorker.data = ParseNakop(dtSheet.Rows[0], cmd, ref excelworksheet); intoWorker.table = "jo_participated";
AddLogMessage("Отправка в базу");
backgroundWorker3.RunWorkerAsync(intoWorker);
do
{
Application.DoEvents();
} while (backgroundWorker3.IsBusy);
////2
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(2);
AddLogMessage("Обработка накопителя ЖП (2/6)");
intoWorker.data = ParseNakop(dtSheet.Rows[1], cmd, ref excelworksheet); intoWorker.table = "jp_participated";
AddLogMessage("Начинаю отгрузку в базу");
backgroundWorker3.RunWorkerAsync(intoWorker);
do
{
Application.DoEvents();
} while (backgroundWorker3.IsBusy);
////3
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(3);
AddLogMessage("Обработка накопителя ЖС (3/6)");
intoWorker.data = ParseNakop(dtSheet.Rows[2], cmd, ref excelworksheet); intoWorker.table = "js_participated";
AddLogMessage("Начинаю отгрузку в базу");
backgroundWorker3.RunWorkerAsync(intoWorker);
do
{
Application.DoEvents();
} while (backgroundWorker3.IsBusy);
//4
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(4);
AddLogMessage("Обработка накопителя МО (4/6)");
intoWorker.data = ParseNakop(dtSheet.Rows[3], cmd, ref excelworksheet); intoWorker.table = "mo_participated";
AddLogMessage("Начинаю отгрузку в базу");
backgroundWorker3.RunWorkerAsync(intoWorker);
do
{
Application.DoEvents();
} while (backgroundWorker3.IsBusy);
//5
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(5);
AddLogMessage("Обработка накопителя МП (5/6)");
intoWorker.data = ParseNakop(dtSheet.Rows[4], cmd, ref excelworksheet); intoWorker.table = "mp_participated";
AddLogMessage("Начинаю отгрузку в базу");
backgroundWorker3.RunWorkerAsync(intoWorker);
do
{
Application.DoEvents();
} while (backgroundWorker3.IsBusy);
//6
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(6);
AddLogMessage("Обработка накопителя МС (6/6)");
intoWorker.data = ParseNakop(dtSheet.Rows[5], cmd, ref excelworksheet); intoWorker.table = "ms_participated";
AddLogMessage("Начинаю отгрузку в базу");
backgroundWorker3.RunWorkerAsync(intoWorker);
do
{
Application.DoEvents();
} while (backgroundWorker3.IsBusy);
}
excelapp.Quit();
this.Enabled = true;
AddLogMessage("Все отгрузки завершены.");
progressBar1.Value = 0;
}
private void CloseExcel()
{
try
{
myExcelWorkbook.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
myExcelWorkbooks.Close();
myExcelApp.Quit();
Marshal.ReleaseComObject(myExcelWorksheet);
Marshal.ReleaseComObject(myExcelWorksheet);
Marshal.ReleaseComObject(myExcelWorkbook);
Marshal.ReleaseComObject(myExcelWorkbooks);
Marshal.ReleaseComObject(myExcelApp);
myExcelWorksheet = null;
myExcelWorkbooks = null;
myExcelWorkbook = null;
myExcelApp = null;
GC.GetTotalMemory(false);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.GetTotalMemory(true);
}
catch (Exception)
{
}
}
private void WriteCell(string str, string name)
{
try
{
Excel.Application ObjExcel = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
Excel.Workbooks ObjWorkbooks = ObjExcel.Workbooks; // Получаем список открытых книг
//MessageBox.Show("Сколько открыто книг"+ ObjWorkbooks.Count.ToString());
List <string> listnames = new List <string>();
foreach (Excel.Workbook wb in ObjWorkbooks) //разбор книг на коллекцию
{
listnames.Add(wb.Name);
}
listnames.Distinct();
if (name == null)
{
MessageBox.Show("The target Log Book is not selected. Select one");
}
//MessageBox.Show(listnames.Contains(name).ToString());
if (!listnames.Contains(name) & name != null)
{
Form f2 = new Form();
f2.StartPosition = FormStartPosition.CenterScreen;
f2.TopMost = true;
f2.ControlBox = false;
f2.Size = new Size(500, 140);
f2.Text = "The target Log Book " + name + " closed. Unable to record event";
Button button_Openfolder = new Button();
button_Openfolder.Text = "Open containing folder";
button_Openfolder.Size = new Size(400, 25);
button_Openfolder.Location = new Point(10, 10);
button_Openfolder.Click += button_Openfolder_Click;
button_Openfolder.Click += button_dismisstheevent_Click;
Button button_OpentargetLogBook = new Button();
button_OpentargetLogBook.Text = "Open target Log Book and log the event";
button_OpentargetLogBook.Size = new Size(400, 25);
button_OpentargetLogBook.Location = new Point(10, 40);
button_OpentargetLogBook.Click += button_OpentargetLogBook_Click;
button_OpentargetLogBook.Click += button_dismisstheevent_Click;
Button button_dismisstheevent = new Button();
button_dismisstheevent.Text = "OK, dismiss the event";
button_dismisstheevent.Size = new Size(400, 25);
button_dismisstheevent.Location = new Point(10, 70);
button_dismisstheevent.Click += button_dismisstheevent_Click;
f2.Controls.Add(button_Openfolder);
f2.Controls.Add(button_OpentargetLogBook);
f2.Controls.Add(button_dismisstheevent);
f2.ShowDialog();
// события
void button_Openfolder_Click(object sender, EventArgs e)//
{
System.Diagnostics.Process.Start("explorer", directoryName);
}
void button_dismisstheevent_Click(object sender, EventArgs e)//
{
f2.Close();
}
void button_OpentargetLogBook_Click(object sender, EventArgs e)/// Открытие файла
{
try
{
Excel.Workbook ObjWorkBook = ObjExcel.Workbooks.Open(fileInfofull, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
//label_Message.Text = ObjExcel.Workbooks.Count.ToString();
ObjExcel.Visible = true;
f2.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
foreach (Excel.Workbook wb in ObjWorkbooks) //разбор книг на коллекцию
{
if (wb.Name == name) // имя книги должно совпасть с именем выбранной
{
int lastRow = wb.ActiveSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
int lastRow2 = 0;
for (int i = lastRow; i >= 1; i--)
{
if (wb.ActiveSheet.Cells[i, 1].Value != null)
{
lastRow2 = i;
break;
}
}
lastRow2 = lastRow2 + 1;
wb.ActiveSheet.Cells[lastRow2, 3] = str;
wb.ActiveSheet.Cells[lastRow2, 1] = DateTime.Now.ToShortDateString();
wb.ActiveSheet.Cells[lastRow2, 2] = DateTime.Now.ToLongTimeString();
wb.Save();
break;
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void OpenExcel()
{
myExcelApp = new Excel.Application();
myExcelApp.Visible = false;
myExcelWorkbooks = myExcelApp.Workbooks;
String fileName = Application.StartupPath + "\\Data\\pttform.xls"; ;
myExcelWorkbook = myExcelWorkbooks.Open(fileName, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.ActiveSheet;
//HandleRef hwnd = new HandleRef(myExcelApp, (IntPtr)myExcelApp.Hwnd);
//GetWindowThreadProcessId(hwnd, out pid);
}
//读取数据 显示到 datagridviewEmo
void runDisplayEmotion()
{
BlogReader breader;
//if (Datas.sinaJSONList.Count > 0)
// Datas.sinaJSONList.Clear();
if (Datas.sinaJSONList.Count == 0)
{
breader = new BlogReader("temp5.dat");
Datas.sinaJSONList = breader.ReadFromFile();
}
Datas.ProgressBarState = "读取完成,正在分析...";
Datas.ProgresBarStyle = System.Windows.Forms.ProgressBarStyle.Marquee;
string emo = "";
//表情键
List<string> emoKey = new List<string>();
List<EmoValue> emoValue = new List<EmoValue>();
using (FileStream fs = new FileStream("datas/emo.txt", FileMode.Open))
{
StreamReader sr = new StreamReader(fs);
while (!sr.EndOfStream)
{
string line = sr.ReadLine();
if (line.Length > 0)
{
emoKey.Add(line);
emoValue.Add(new EmoValue());
}
}
sr.Close();
fs.Close();
}
MatchCollection mCollection;
foreach (SinaJSON item in Datas.sinaJSONList)
{
mCollection = Regex.Matches(item.Text, @"\[\w+\]");
for (int i = 0; i < mCollection.Count; i++)
{
emo = mCollection[i].Value;
//emo = emo.Substring(1, emo.Length - 2);
int index = emoKey.IndexOf(emo);
int male = 0, female = 0;
if (item.SinaUser.Gender.Equals("m"))
male++;
else
female++;
if (index != -1)
emoValue[index] = new EmoValue(emoValue[index].male + male, emoValue[index].female + female);
}
}
app = new Excel.Application();
workbooks = app.Workbooks as Excel.Workbooks;
workbook = workbooks.Add(Type.Missing);
app.DisplayAlerts = false;
//删除 多余Sheet
foreach (Worksheet ws in workbook.Worksheets)
if (ws != app.ActiveSheet)
{
ws.Delete();
}
foreach (Chart cht in workbook.Charts)
cht.Delete();
//创建一个Sheet,存数据
//worksheet = (Worksheet)workbook.
// Worksheets.Add(Type.Missing, workbook.ActiveSheet,
// Type.Missing, Type.Missing);
worksheet = workbook.Worksheets[1];
worksheet.Name = "数据";
int worksheetIndex = 0;
for (int i = 0; i < emoKey.Count; i++)
{
if (emoValue[i].male > 0 || emoValue[i].female > 0)
{
worksheet.Cells[i + 1, 1] = emoKey[i];
worksheet.Cells[i + 1, 2] = emoValue[i].male;
worksheet.Cells[i + 1, 3] = emoValue[i].female;
worksheetIndex++;
}
}
// TODO: 生成一个统计图对象：
Chart xlChart = (Chart)workbook.Charts.
Add(Type.Missing, worksheet, Type.Missing, Type.Missing);
// TODO: 设定数据来源
Range cellRange = (Range)worksheet.Cells[1, 1];
// TODO: 通过向导生成Chart
xlChart.ChartWizard(cellRange.CurrentRegion,
XlChartType.xl3DColumn, Type.Missing,
XlRowCol.xlColumns, 1, 0, true,
"表情比较", "表情", "数量");
// TODO: 设置统计图Sheet的名称
xlChart.Name = "统计";
// TODO: 让12个Bar都显示不同的颜色
ChartGroup grp = (ChartGroup)xlChart.ChartGroups(1);
grp.GapWidth = 20;
grp.VaryByCategories = true;
// TODO: 让Chart的条目的显示形状变成圆柱形，并给它们显示加上数据标签
Series s1 = (Series)grp.SeriesCollection(1);
s1.Name = "男";
s1.BarShape = XlBarShape.xlCylinder;
s1.HasDataLabels = true;
Series s = (Series)grp.SeriesCollection(2);
s.BarShape = XlBarShape.xlCylinder;
s.HasDataLabels = true;
s.Name = "女";
// TODO: 设置统计图的标题和图例的显示
xlChart.Legend.Position = XlLegendPosition.xlLegendPositionTop;
xlChart.ChartTitle.Font.Size = 24;
xlChart.ChartTitle.Shadow = false;
xlChart.ChartTitle.Border.LineStyle = XlLineStyle.xlContinuous;
// TODO: 设置两个轴的属性，Excel.XlAxisType.xlValue对应的是Y轴，Excel.XlAxisType.xlCategory对应的是X轴
Axis valueAxis = (Axis)xlChart.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary);
valueAxis.AxisTitle.Orientation = -90;
Axis categoryAxis = (Axis)xlChart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
categoryAxis.AxisTitle.Font.Name = "宋体";
//--------------------------------------------------
//workbook.SaveAs(sPath, Type.Missing, Type.Missing,
// Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange,
// Type.Missing, Type.Missing, Type.Missing, Type.Missing,
// Type.Missing);
xlChart.CopyPicture(XlPictureAppearance.xlScreen, XlCopyPictureFormat.xlPicture);
IntPtr hwnd = (IntPtr)app.Hwnd;
Bitmap b = null;
try
{
if (OpenClipboard(hwnd))
{
IntPtr data = GetClipboardData(14); // CF_ENHMETAFILE 14
if (data != IntPtr.Zero)
{
using (Metafile mf = new Metafile(data, true))
{
b = new Bitmap(mf);
}
}
}
}
finally
{
CloseClipboard();
//clear
workbook.Close(Type.Missing, Type.Missing, Type.Missing);
app.Workbooks.Close();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
workbook = null;
app = null;
xlChart = null;
GC.Collect();
}
this.Invoke(new updateDataGridViewEmo(doUpdateDataGridViewEmo), new object[] { emoKey, emoValue, b });
}
// Main conversion routine
public static new int Convert(String inputFile, String outputFile, Hashtable options)
{
Boolean running = (Boolean)options["noquit"];
Microsoft.Office.Interop.Excel.Application app = null;
Microsoft.Office.Interop.Excel.Workbooks workbooks = null;
Microsoft.Office.Interop.Excel.Workbook workbook = null;
System.Object activeSheet = null;
Window activeWindow = null;
Windows wbWin = null;
Hashtable templatePageSetup = new Hashtable();
String tmpFile = null;
object oMissing = System.Reflection.Missing.Value;
Boolean nowrite = (Boolean)options["readonly"];
try
{
// Excel can be very slow to start up, so try to get the COM
// object a few times
int tries = 10;
app = new Microsoft.Office.Interop.Excel.Application();
while (tries > 0)
{
try
{
// Try to set a property on the object
app.ScreenUpdating = false;
}
catch (COMException)
{
// Decrement the number of tries and have a bit of a snooze
tries--;
Thread.Sleep(500);
continue;
}
// Looks ok, so bail out of the loop
break;
}
if (tries == 0)
{
Converter.releaseCOMObject(app);
return((int)ExitCode.ApplicationError);
}
app.Visible = true;
app.DisplayAlerts = false;
app.AskToUpdateLinks = false;
app.AlertBeforeOverwriting = false;
app.EnableLargeOperationAlert = false;
app.Interactive = false;
app.FeatureInstall = Microsoft.Office.Core.MsoFeatureInstall.msoFeatureInstallNone;
var onlyActiveSheet = (Boolean)options["excel_active_sheet"];
Boolean includeProps = !(Boolean)options["excludeprops"];
Boolean skipRecalculation = (Boolean)options["excel_no_recalculate"];
Boolean showHeadings = (Boolean)options["excel_show_headings"];
Boolean showFormulas = (Boolean)options["excel_show_formulas"];
Boolean isHidden = (Boolean)options["hidden"];
Boolean screenQuality = (Boolean)options["screen"];
Boolean updateLinks = !(Boolean)options["excel_no_link_update"];
int maxRows = (int)options[@"excel_max_rows"];
int worksheetNum = (int)options["excel_worksheet"];
int sheetForConversionIdx = 0;
activeWindow = app.ActiveWindow;
Sheets worksheets = null;
XlFileFormat fmt = XlFileFormat.xlOpenXMLWorkbook;
XlFixedFormatQuality quality = XlFixedFormatQuality.xlQualityStandard;
if (isHidden)
{
// Try and at least minimise it
app.WindowState = XlWindowState.xlMinimized;
app.Visible = false;
}
String readPassword = "";
if (!String.IsNullOrEmpty((String)options["password"]))
{
readPassword = (String)options["password"];
}
Object oReadPass = (Object)readPassword;
String writePassword = "";
if (!String.IsNullOrEmpty((String)options["writepassword"]))
{
writePassword = (String)options["writepassword"];
}
Object oWritePass = (Object)writePassword;
// Check for password protection and no password
if (Converter.IsPasswordProtected(inputFile) && String.IsNullOrEmpty(readPassword))
{
Console.WriteLine("Unable to open password protected file");
return((int)ExitCode.PasswordFailure);
}
app.EnableEvents = (bool)options["excel_auto_macros"];
workbooks = app.Workbooks;
// If we have no write password and we're attempting to open for writing, we might be
// caught out by an unexpected write password
if (writePassword == "" && !nowrite)
{
oWritePass = (Object)"FAKEPASSWORD";
try
{
workbook = workbooks.Open(inputFile, updateLinks, nowrite, oMissing, oReadPass, oWritePass, true, oMissing, oMissing, oMissing, oMissing, oMissing, false, oMissing, oMissing);
}
catch (System.Runtime.InteropServices.COMException)
{
// Attempt to open it in read-only mode
workbook = workbooks.Open(inputFile, updateLinks, true, oMissing, oReadPass, oWritePass, true, oMissing, oMissing, oMissing, oMissing, oMissing, false, oMissing, oMissing);
}
}
else
{
workbook = workbooks.Open(inputFile, updateLinks, nowrite, oMissing, oReadPass, oWritePass, true, oMissing, oMissing, oMissing, oMissing, oMissing, false, oMissing, oMissing);
}
// Add in a delay to let Excel sort itself out
addCOMDelay(options);
// Unable to open workbook
if (workbook == null)
{
return((int)ExitCode.FileOpenFailure);
}
if (app.EnableEvents)
{
workbook.RunAutoMacros(XlRunAutoMacro.xlAutoOpen);
}
// Get any template options
setPageOptionsFromTemplate(app, workbooks, options, ref templatePageSetup);
// Get the sheets
worksheets = workbook.Sheets;
// Try and avoid xls files raising a dialog
var temporaryStorageDir = Path.GetTempFileName();
File.Delete(temporaryStorageDir);
Directory.CreateDirectory(temporaryStorageDir);
// We will save as xlsb (binary format) since this doesn't raise some errors when processing
tmpFile = Path.Combine(temporaryStorageDir, Path.GetFileNameWithoutExtension(inputFile) + ".xlsb");
fmt = XlFileFormat.xlExcel12;
// Set up the print quality
if (screenQuality)
{
quality = XlFixedFormatQuality.xlQualityMinimum;
}
// If a worksheet has been specified, try and use just the one
if (worksheetNum > 0)
{
// Force us just to use the active sheet
onlyActiveSheet = true;
try
{
if (worksheetNum > worksheets.Count)
{
// Sheet count is too big
return((int)ExitCode.WorksheetNotFound);
}
if (worksheets[worksheetNum] is _Worksheet)
{
((_Worksheet)worksheets[worksheetNum]).Activate();
sheetForConversionIdx = ((_Worksheet)worksheets[worksheetNum]).Index;
}
else if (worksheets[worksheetNum] is _Chart)
{
((_Chart)worksheets[worksheetNum]).Activate();
sheetForConversionIdx = ((_Chart)worksheets[worksheetNum]).Index;
}
}
catch (Exception)
{
return((int)ExitCode.WorksheetNotFound);
}
}
if (showFormulas)
{
// Determine whether to show formulas
try
{
activeWindow.DisplayFormulas = true;
}
catch (Exception) { }
}
// Keep the windows hidden
if (isHidden)
{
wbWin = workbook.Windows;
if (wbWin.Count > 0)
{
wbWin[1].Visible = false;
}
if (null != activeWindow)
{
activeWindow.Visible = false;
}
}
// Keep track of the active sheet
if (workbook.ActiveSheet != null)
{
activeSheet = workbook.ActiveSheet;
}
// Large excel files may simply not print reliably - if the excel_max_rows
// configuration option is set, then we must close up and forget about
// converting the file. However, if a print area is set in one of the worksheets
// in the document, then assume the author knew what they were doing and
// use the print area.
// We may need to loop through all the worksheets in the document
// depending on the options given. If there are maximum row restrictions
// or formulas are being shown, then we need to loop through all the
// worksheets
if (maxRows > 0 || showFormulas || showHeadings)
{
var row_count_check_ok = true;
var found_rows = 0;
var found_worksheet = "";
// Loop through all the sheets (worksheets and charts)
for (int wsIdx = 1; wsIdx <= worksheets.Count; wsIdx++)
{
var ws = worksheets.Item[wsIdx];
// Skip anything that is not the active sheet
if (onlyActiveSheet)
{
// Have to be careful to treat _Worksheet and _Chart items differently
try
{
int itemIndex = 1;
if (activeSheet is _Worksheet)
{
itemIndex = ((Microsoft.Office.Interop.Excel.Worksheet)activeSheet).Index;
}
else if (activeSheet is _Chart)
{
itemIndex = ((Microsoft.Office.Interop.Excel.Chart)activeSheet).Index;
}
if (wsIdx != itemIndex)
{
Converter.releaseCOMObject(ws);
continue;
}
}
catch (Exception)
{
if (ws != null)
{
Converter.releaseCOMObject(ws);
}
continue;
}
sheetForConversionIdx = wsIdx;
}
if (showHeadings && ws is _Worksheet)
{
PageSetup pageSetup = null;
try
{
pageSetup = ((Microsoft.Office.Interop.Excel.Worksheet)ws).PageSetup;
pageSetup.PrintHeadings = true;
}
catch (Exception) { }
finally
{
Converter.releaseCOMObject(pageSetup);
}
}
// If showing formulas, make things auto-fit
if (showFormulas && ws is _Worksheet)
{
Range cols = null;
try
{
((Microsoft.Office.Interop.Excel._Worksheet)ws).Activate();
activeWindow.DisplayFormulas = true;
cols = ((Microsoft.Office.Interop.Excel.Worksheet)ws).Columns;
cols.AutoFit();
}
catch (Exception) { }
finally
{
Converter.releaseCOMObject(cols);
}
}
// If there is a maximum row count, make sure we check each worksheet
if (maxRows > 0 && ws is _Worksheet)
{
// Check for a print area
var pageSetup = ((Microsoft.Office.Interop.Excel.Worksheet)ws).PageSetup;
var printArea = pageSetup.PrintArea;
Converter.releaseCOMObject(pageSetup);
if (string.IsNullOrEmpty(printArea))
{
// There is no print area, check that the row count is <= to the
// excel_max_rows value. Note that we can't just take the range last
// row, as this may return a huge value, rather find the last non-blank
// row.
var row_count = 0;
var range = ((Microsoft.Office.Interop.Excel.Worksheet)ws).UsedRange;
if (range != null)
{
var rows = range.Rows;
if (rows != null && rows.Count > maxRows)
{
var cells = range.Cells;
if (cells != null)
{
var cellSearch = cells.Find("*", oMissing, oMissing, oMissing, oMissing, Microsoft.Office.Interop.Excel.XlSearchDirection.xlPrevious, false, oMissing, oMissing);
// Make sure we actually get some results, since the worksheet may be totally blank
if (cellSearch != null)
{
row_count = cellSearch.Row;
found_worksheet = ((Microsoft.Office.Interop.Excel.Worksheet)ws).Name;
}
Converter.releaseCOMObject(cellSearch);
}
Converter.releaseCOMObject(cells);
}
Converter.releaseCOMObject(rows);
}
Converter.releaseCOMObject(range);
if (row_count > maxRows)
{
// Too many rows on this worksheet - mark the workbook as unprintable
row_count_check_ok = false;
found_rows = row_count;
Converter.releaseCOMObject(ws);
break;
}
}
} // End of row check
Converter.releaseCOMObject(ws);
}
// Make sure we are not converting a document with too many rows
if (row_count_check_ok == false)
{
throw new Exception(String.Format("Too many rows to process ({0}) on worksheet {1}", found_rows, found_worksheet));
}
}
// Allow for re-calculation to be skipped
if (skipRecalculation)
{
app.Calculation = XlCalculation.xlCalculationManual;
app.CalculateBeforeSave = false;
}
workbook.SaveAs(tmpFile, fmt, Type.Missing, Type.Missing, Type.Missing, false, XlSaveAsAccessMode.xlNoChange, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing);
if (onlyActiveSheet)
{
if (sheetForConversionIdx > 0)
{
activeSheet = worksheets.Item[sheetForConversionIdx];
}
if (activeSheet is _Worksheet)
{
var wps = ((_Worksheet)activeSheet).PageSetup;
setPageSetupProperties(templatePageSetup, wps);
((Microsoft.Office.Interop.Excel.Worksheet)activeSheet).ExportAsFixedFormat(XlFixedFormatType.xlTypePDF,
outputFile, quality, includeProps, false, Type.Missing, Type.Missing, false, Type.Missing);
Converter.releaseCOMObject(wps);
}
else if (activeSheet is _Chart)
{
var wps = ((_Chart)activeSheet).PageSetup;
setPageSetupProperties(templatePageSetup, wps);
((Microsoft.Office.Interop.Excel.Chart)activeSheet).ExportAsFixedFormat(XlFixedFormatType.xlTypePDF,
outputFile, quality, includeProps, false, Type.Missing, Type.Missing, false, Type.Missing);
Converter.releaseCOMObject(wps);
}
else
{
return((int)ExitCode.UnknownError);
}
addCOMDelay(options);
}
else
{
if (hasTemplateOption(options))
{
// Set up the template page setup options on all the worksheets
// in the workbook
foreach (var ws in workbook.Worksheets)
{
var wps = (ws is _Worksheet) ? ((_Worksheet)ws).PageSetup : ((_Chart)ws).PageSetup;
setPageSetupProperties(templatePageSetup, wps);
Converter.releaseCOMObject(wps);
Converter.releaseCOMObject(ws);
}
}
workbook.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF,
outputFile, quality, includeProps, false, Type.Missing, Type.Missing, false, Type.Missing);
}
Converter.releaseCOMObject(worksheets);
Converter.releaseCOMObject(fmt);
Converter.releaseCOMObject(quality);
return((int)ExitCode.Success);
}
catch (COMException ce)
{
if ((uint)ce.ErrorCode == 0x800A03EC)
{
return((int)ExitCode.EmptyWorksheet);
}
else
{
Console.WriteLine(ce.Message);
return((int)ExitCode.UnknownError);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return((int)ExitCode.UnknownError);
}
finally
{
if (workbook != null)
{
Converter.releaseCOMObject(activeSheet);
Converter.releaseCOMObject(activeWindow);
Converter.releaseCOMObject(wbWin);
GC.Collect();
GC.WaitForPendingFinalizers();
// Excel sometimes needs a bit of a delay before we close in order to
// let things get cleaned up
workbook.Saved = true;
closeExcelWorkbook(workbook);
}
if (!running)
{
if (workbooks != null)
{
workbooks.Close();
}
if (app != null)
{
((Microsoft.Office.Interop.Excel._Application)app).Quit();
}
}
// Clean all the COM leftovers
Converter.releaseCOMObject(workbook);
Converter.releaseCOMObject(workbooks);
Converter.releaseCOMObject(app);
GC.Collect();
GC.WaitForPendingFinalizers();
if (tmpFile != null && File.Exists(tmpFile))
{
System.IO.File.Delete(tmpFile);
// Remove the temporary path to the temp file
Directory.Delete(Path.GetDirectoryName(tmpFile));
}
}
}
private void bttExportExcel_Click(object sender, EventArgs e)
{
Thread progressThread = new Thread(delegate()
{
ProgressForm progress = new ProgressForm();
progress.ShowDialog();
});
progressThread.Start();
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
try
{
Excel.Range formatRange;
formatRange = xlWorkSheet.get_Range("H2", "H99999");
formatRange.NumberFormat = "#,###,###";
}
catch { }
int i = 0;
int j = 0;
int ix = 1;
for (j = 0; j <= dataGridView1.ColumnCount - 1; j++)
{
xlWorkSheet.Cells[i + 1, j + 1] = dataGridView1.Columns[j].Name;
}
for (i = 0; i <= dataGridView1.RowCount - 1; i++)
{
for (j = 0; j <= dataGridView1.ColumnCount - 1; j++)
{
DataGridViewCell cell = dataGridView1[j, i];
xlWorkSheet.Cells[ix + 1, j + 1] = cell.Value;
}
ix = ix + 1;
}
xlWorkSheet.Columns.AutoFit();
if (System.IO.File.Exists(@"c:\MIS_Request.xls"))
{
try
{
System.IO.File.Delete(@"c:\MIS_Request.xls");
}
catch { }
}
if (System.IO.File.Exists(@"D:\MIS_Request.xls"))
{
try
{
System.IO.File.Delete(@"D:\MIS_Request.xls");
}
catch { }
}
try
{
xlWorkBook.SaveAs(@"c:\MIS_Request.xls");
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
var excelApp = new Excel.Application();
excelApp.Visible = true;
Excel.Workbooks books = excelApp.Workbooks;
Excel.Workbook Sheet = books.Open(@"c:\MIS_Request.xls");
}
catch
{
xlWorkBook.SaveAs(@"D:\MIS_Request.xls");
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
var excelApp = new Excel.Application();
excelApp.Visible = true;
Excel.Workbooks books = excelApp.Workbooks;
Excel.Workbook Sheet = books.Open(@"D:\MIS_Request.xls");
}
progressThread.Abort();
}
/// <summary>
/// 创建一个Excel程序实例
/// </summary>
private void CreateExcelRef()
{
_excelApp = new Excel.Application();
_books = (Excel.Workbooks)_excelApp.Workbooks;
_book = (Excel._Workbook)(_books.Add(_optionalValue));
_sheets = (Excel.Sheets)_book.Worksheets;
_sheet = (Excel._Worksheet)(_sheets.get_Item(1));
}
public static void OutputAsExcelFile(DataGridView dataGridView)
{
if (dataGridView.Rows.Count <= 0)
{
MessageBox.Show("无数据！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning); return;
}
string fileName = "";
string filePath = "";
SaveFileDialog s = new SaveFileDialog();
s.Title = "保存Excel文件";
s.Filter = "Excel文件(*.xls)|*.xls";
s.FilterIndex = 1;
if (s.ShowDialog() == DialogResult.OK)
{
filePath = s.FileName;
fileName = System.IO.Path.GetFileName(filePath);//获取重命名
}
else
{
return;
}
//第一步：将dataGridView转化为dataTable,这样可以过滤掉dataGridView中的隐藏列
DataTable tmpDataTable = new DataTable("tmpDataTable");
DataTable modelTable = new DataTable("ModelTable");
for (int column = 0; column < dataGridView.Columns.Count; column++)
{
if (dataGridView.Columns[column].Visible == true)
{
DataColumn tempColumn = new DataColumn(dataGridView.Columns[column].HeaderText, typeof(string));
tmpDataTable.Columns.Add(tempColumn);
DataColumn modelColumn = new DataColumn(dataGridView.Columns[column].Name, typeof(string));
modelTable.Columns.Add(modelColumn);
}
}
for (int row = 0; row < dataGridView.Rows.Count; row++)
{
if (dataGridView.Rows[row].Visible == false)
{
continue;
}
DataRow tempRow = tmpDataTable.NewRow();
for (int i = 0; i < tmpDataTable.Columns.Count; i++)
{
tempRow[i] = dataGridView.Rows[row].Cells[modelTable.Columns[i].ColumnName].Value;
}
tmpDataTable.Rows.Add(tempRow);
}
if (tmpDataTable == null)
{
return;
}
//第二步：导出dataTable到Excel
long rowNum = tmpDataTable.Rows.Count; //行数
int columnNum = tmpDataTable.Columns.Count; //列数
Excel.Application m_xlApp = new Excel.Application();
m_xlApp.DisplayAlerts = false;//不显示更改提示
m_xlApp.Visible = false;
Excel.Workbooks workbooks = m_xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
try
{
string[,] datas = new string[rowNum + 1, columnNum];
for (int i = 0; i < columnNum; i++) //写入字段
{
datas[0, i] = tmpDataTable.Columns[i].Caption;
}
//Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]);
Excel.Range range = m_xlApp.Range[worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]];
range.Interior.ColorIndex = 15;//15代表灰色
range.Font.Bold = true;
range.Font.Size = 10;
int r = 0;
for (r = 0; r < rowNum; r++)
{
for (int i = 0; i < columnNum; i++)
{
object obj = tmpDataTable.Rows[r][tmpDataTable.Columns[i].ToString()];
datas[r + 1, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式
}
System.Windows.Forms.Application.DoEvents();
//添加进度条
}
//Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]);
Excel.Range fchR = m_xlApp.Range[worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]];
fchR.Value2 = datas;
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
//worksheet.Name = "dd";
//m_xlApp.WindowState = Excel.XlWindowState.xlMaximized;
m_xlApp.Visible = false;
// = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]);
range = m_xlApp.Range[worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]];
//range.Interior.ColorIndex = 15;//15代表灰色
range.Font.Size = 9;
range.RowHeight = 14.25;
range.Borders.LineStyle = 1;
range.HorizontalAlignment = 1;
workbook.Saved = true;
workbook.SaveCopyAs(filePath);
}
catch (Exception ex)
{
MessageBox.Show("导出异常：" + ex.Message, "导出异常", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
finally
{
EndReport();
}
m_xlApp.Workbooks.Close();
m_xlApp.Workbooks.Application.Quit();
m_xlApp.Application.Quit();
m_xlApp.Quit();
MessageBox.Show(fileName + " 导出成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
public void Open(string strFilePath)
{
_strFilePath = strFilePath;
_application = new Microsoft.Office.Interop.Excel.Application();
_workbooks = _application.Workbooks;
_workbook = _workbooks.Open(_strFilePath, 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);
_worksheet = (Microsoft.Office.Interop.Excel.Worksheet)_workbook.Worksheets[1];
}
private static string OpenExcel(ExcelInfo excelInfo, bool isOpenSheet = true)
{
Console.WriteLine("Open File:【{0}】", excelInfo.FilePath);
if (!File.Exists(excelInfo.FilePath))
{
return $"文件【{excelInfo.FilePath}】不存在";
}
_objExcel = new Excel.Application { Visible = false, DisplayAlerts = false, AlertBeforeOverwriting = false };
_objBooks = _objExcel.Workbooks;
if (excelInfo.FilePath.Equals(String.Empty) || !File.Exists(excelInfo.FilePath))
{
_objBook = _objBooks.Add(ObjOpt);
}
else
{
_objBook = _objBooks.Open(excelInfo.FilePath, ObjOpt, ObjOpt, ObjOpt, ObjOpt,
ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt);
}
if (isOpenSheet)
{
_objSheet = OpenSheet(excelInfo);
if (_objSheet == null)
{
return "没有指定页签";
}
}
return "";
}
public void GeneraExcelMultiplePagina(string pathFile)
{
try
{
DataSet dsConsulta = new DataSet();
#region Ventas
DataSet venta = ConsultaVentas(0, DateTime.Today.AddDays(-1).ToString("yyyyMMdd"), DateTime.Today.AddDays(-1).ToString("yyyyMMdd"));
FusionTablas(dsConsulta, venta, "0", DateTime.Today.AddDays(-1), DateTime.Today.AddDays(-1));
if (DateTime.Today.DayOfWeek.ToString() == "Monday")
{
venta = ConsultaVentas(0, DateTime.Today.AddDays(-8).ToString("yyyyMMdd"), DateTime.Today.AddDays(-2).ToString("yyyyMMdd"));
FusionTablas(dsConsulta, venta, "0", DateTime.Today.AddDays(-8), DateTime.Today.AddDays(-2));
}
if (DateTime.Today.Day == 1)
{
venta = ConsultaVentas(0, DateTime.Today.AddMonths(-1).ToString("yyyyMMdd"), DateTime.Today.AddDays(-1).ToString("yyyyMMdd"));
FusionTablas(dsConsulta, venta, "0", DateTime.Today.AddMonths(-1), DateTime.Today.AddDays(-1));
}
#endregion
#region Nomina
DataSet nomina = ConsultaVentas(1, DateTime.Today.AddDays(-1).ToString("yyyyMMdd"), DateTime.Today.AddDays(-1).ToString("yyyyMMdd"));
FusionTablas(dsConsulta, nomina, "1", DateTime.Today.AddDays(-1), DateTime.Today.AddDays(-1));
if (DateTime.Today.DayOfWeek.ToString() == "Monday")
{
nomina = ConsultaVentas(1, DateTime.Today.AddDays(-8).ToString("yyyyMMdd"), DateTime.Today.AddDays(-2).ToString("yyyyMMdd"));
FusionTablas(dsConsulta, nomina, "1", DateTime.Today.AddDays(-8), DateTime.Today.AddDays(-2));
}
if (DateTime.Today.Day == 1)
{
nomina = ConsultaVentas(1, DateTime.Today.AddMonths(-1).ToString("yyyyMMdd"), DateTime.Today.AddDays(-1).ToString("yyyyMMdd"));
FusionTablas(dsConsulta, nomina, "1", DateTime.Today.AddMonths(-1), DateTime.Today.AddDays(-1));
}
#endregion
#region Inventario
DataSet inven = ConsultaInventario();
FusionTablas(dsConsulta, inven, "2", DateTime.Today, DateTime.Today);
#endregion
List <SqlParameter> parametros = new List <SqlParameter>();
object misValue = System.Reflection.Missing.Value;
ExcelP.Application Excel = new ExcelP.Application();
ExcelP.Workbooks LibrosExcel = Excel.Workbooks;
ExcelP.Workbook LibroExcel = LibrosExcel.Add();
ExcelP.Worksheet HojaExcel;
LibroExcel = Excel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
int worksheetIndex = 0;
foreach (DataTable dt in dsConsulta.Tables)
{
worksheetIndex++;
if (worksheetIndex == 1)
{
HojaExcel = LibroExcel.Worksheets.get_Item(1);
}
else
{
HojaExcel = LibroExcel.Worksheets.Add();
}
HojaExcel.Name = dt.TableName;
#region nombreColumnas
HojaExcel.Range["A1:" + letraTitulo(dt.Columns.Count - 1) + "1"].Font.Bold = true;
HojaExcel.Range["A1:" + letraTitulo(dt.Columns.Count - 1) + "1"].Font.Size = 12;
Microsoft.Office.Interop.Excel.Range objCelda;//= HojaExcel.Range["A1", Type.Missing];
int contcol = 0;
foreach (DataColumn col in dt.Columns)
{
objCelda = HojaExcel.Range[letraTitulo(contcol) + "1", Type.Missing];
objCelda.Value = col.ColumnName.Replace("X", "");
contcol++;
}
objCelda = null;
#endregion
#region setdatos
int rowIndex = 2;
foreach (DataRow row in dsConsulta.Tables[worksheetIndex - 1].Rows)
{
int columnIndex = 1;
foreach (object dc in row.ItemArray)
{
HojaExcel.Cells[rowIndex, columnIndex] = dc.ToString();
columnIndex++;
}
rowIndex++;
}
#endregion
objCelda = HojaExcel.Range["A1:" + letraTitulo(dt.Columns.Count - 1) + (rowIndex - 1).ToString()];
objCelda.Columns.AutoFit(); objCelda = null;
}
Excel.Cells.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
LibroExcel.SaveAs(pathFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, misValue, misValue, misValue, misValue, misValue);
LibroExcel.Close(true, misValue, misValue);
Excel.UserControl = false;
Excel.Quit();
releaseObject(LibroExcel);
releaseObject(LibrosExcel);
releaseObject(Excel);
}
catch (Exception ex)
{
}
}
private void otkritie()
{
try
{
string s = Environment.GetFolderPath(Environment.SpecialFolder.UserProfile);
string we = System.IO.Path.Combine(s, @"ProgramData\FreeRooms");
System.IO.Directory.CreateDirectory(we);
excelapp = new Excel.Application();
excelappworkbooks = excelapp.Workbooks;
excelappworkbook = excelapp.Workbooks.Open(System.IO.Path.Combine(we, numbernedeli),
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
excelsheets = excelappworkbook.Worksheets;
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1);
excelapp.Visible = true;
}
catch { MessageBox.Show("Файл не был найден, обновите базы данных"); }
}
public static new int Convert(String inputFile, String outputFile, Hashtable options)
{
Boolean running = (Boolean)options["noquit"];
Microsoft.Office.Interop.Excel.Application app = null;
Microsoft.Office.Interop.Excel.Workbooks workbooks = null;
Microsoft.Office.Interop.Excel.Workbook workbook = null;
String tmpFile = null;
object oMissing = System.Reflection.Missing.Value;
Boolean nowrite = (Boolean)options["readonly"];
try
{
app = new Microsoft.Office.Interop.Excel.Application()
{
Visible = true,
DisplayAlerts = false,
AskToUpdateLinks = false,
AlertBeforeOverwriting = false,
EnableLargeOperationAlert = false,
Interactive = false,
FeatureInstall = Microsoft.Office.Core.MsoFeatureInstall.msoFeatureInstallNone
};
if ((Boolean)options["hidden"])
{
// Try and at least minimise it
app.WindowState = XlWindowState.xlMinimized;
app.Visible = false;
}
String readPassword = "";
if (!String.IsNullOrEmpty((String)options["password"]))
{
readPassword = (String)options["password"];
}
Object oReadPass = (Object)readPassword;
String writePassword = "";
if (!String.IsNullOrEmpty((String)options["writepassword"]))
{
writePassword = (String)options["writepassword"];
}
Object oWritePass = (Object)writePassword;
// Check for password protection and no password
if (Converter.IsPasswordProtected(inputFile) && String.IsNullOrEmpty(readPassword))
{
Console.WriteLine("Unable to open password protected file");
return((int)ExitCode.PasswordFailure);
}
app.EnableEvents = (bool)options["excel_auto_macros"];
workbooks = app.Workbooks;
workbook = workbooks.Open(inputFile, true, nowrite, oMissing, oReadPass, oWritePass, true, oMissing, oMissing, oMissing, oMissing, oMissing, false, oMissing, oMissing);
// Unable to open workbook
if (workbook == null)
{
return((int)ExitCode.FileOpenFailure);
}
if (app.EnableEvents)
{
workbook.RunAutoMacros(XlRunAutoMacro.xlAutoOpen);
}
// Try and avoid xls files raising a dialog
var temporaryStorageDir = Path.GetTempFileName();
File.Delete(temporaryStorageDir);
Directory.CreateDirectory(temporaryStorageDir);
tmpFile = Path.Combine(temporaryStorageDir, Path.GetFileNameWithoutExtension(inputFile) + ".xls");
// Set up the file save format
XlFileFormat fmt = XlFileFormat.xlOpenXMLWorkbook;
if (workbook.HasVBProject)
{
fmt = XlFileFormat.xlOpenXMLWorkbookMacroEnabled;
tmpFile += "m";
}
else
{
tmpFile += "x";
}
// Set up the print quality
XlFixedFormatQuality quality = XlFixedFormatQuality.xlQualityStandard;
if ((Boolean)options["screen"])
{
quality = XlFixedFormatQuality.xlQualityMinimum;
}
// Remember - Never use 2 dots with COM objects!
// Using more than one dot leaves wrapper objects left over
var wbWin = workbook.Windows;
var appWin = app.Windows;
if ((Boolean)options["excel_show_formulas"])
{
// Determine whether to show formulas
appWin[1].DisplayFormulas = true;
}
if (wbWin.Count > 0)
{
wbWin[1].Visible = (Boolean)options["hidden"] ? false : true;
Converter.ReleaseCOMObject(wbWin);
}
if (appWin.Count > 0)
{
appWin[1].Visible = (Boolean)options["hidden"] ? false : true;
Converter.ReleaseCOMObject(appWin);
}
// Large excel files may simply not print reliably - if the excel_max_rows
// configuration option is set, then we must close up and forget about
// converting the file. However, if a print area is set in one of the worksheets
// in the document, then assume the author knew what they were doing and
// use the print area.
var max_rows = (int)options[@"excel_max_rows"];
// We may need to loop through all the worksheets in the document
// depending on the options given. If there are maximum row restrictions
// or formulas are being shown, then we need to loop through all the
// worksheets
if (max_rows > 0 || (Boolean)options["excel_show_formulas"] || (Boolean)options["excel_show_headings"])
{
var row_count_check_ok = true;
var found_rows = 0;
var found_worksheet = "";
var worksheets = workbook.Worksheets;
foreach (var ws in worksheets)
{
if ((Boolean)options["excel_show_headings"])
{
var pageSetup = ((Microsoft.Office.Interop.Excel.Worksheet)ws).PageSetup;
pageSetup.PrintHeadings = true;
Converter.ReleaseCOMObject(pageSetup);
}
// If showing formulas, make things auto-fit
if ((Boolean)options["excel_show_formulas"])
{
((Microsoft.Office.Interop.Excel.Worksheet)ws).Activate();
app.ActiveWindow.DisplayFormulas = true;
var cols = ((Microsoft.Office.Interop.Excel.Worksheet)ws).Columns;
cols.AutoFit();
Converter.ReleaseCOMObject(cols);
}
// If there is a maximum row count, make sure we check each worksheet
if (max_rows > 0)
{
// Check for a print area
var page_setup = ((Microsoft.Office.Interop.Excel.Worksheet)ws).PageSetup;
var print_area = page_setup.PrintArea;
Converter.ReleaseCOMObject(page_setup);
if (string.IsNullOrEmpty(print_area))
{
// There is no print area, check that the row count is <= to the
// excel_max_rows value. Note that we can't just take the range last
// row, as this may return a huge value, rather find the last non-blank
// row.
var row_count = 0;
var range = ((Microsoft.Office.Interop.Excel.Worksheet)ws).UsedRange;
if (range != null)
{
var rows = range.Rows;
if (rows != null && rows.Count > max_rows)
{
var cells = range.Cells;
if (cells != null)
{
var cellSearch = cells.Find("*", oMissing, oMissing, oMissing, oMissing, Microsoft.Office.Interop.Excel.XlSearchDirection.xlPrevious, false, oMissing, oMissing);
// Make sure we actually get some results, since the worksheet may be totally blank
if (cellSearch != null)
{
row_count = cellSearch.Row;
found_worksheet = ((Microsoft.Office.Interop.Excel.Worksheet)ws).Name;
Converter.ReleaseCOMObject(cellSearch);
}
Converter.ReleaseCOMObject(cells);
}
Converter.ReleaseCOMObject(rows);
}
}
Converter.ReleaseCOMObject(range);
if (row_count > max_rows)
{
// Too many rows on this worksheet - mark the workbook as unprintable
row_count_check_ok = false;
found_rows = row_count;
Converter.ReleaseCOMObject(ws);
break;
}
}
} // End of row check
Converter.ReleaseCOMObject(ws);
}
Converter.ReleaseCOMObject(worksheets);
// Make sure we are not converting a document with too many rows
if (row_count_check_ok == false)
{
throw new Exception(String.Format("Too many rows to process ({0}) on worksheet {1}", found_rows, found_worksheet));
}
}
Boolean includeProps = !(Boolean)options["excludeprops"];
workbook.SaveAs(tmpFile, fmt, Type.Missing, Type.Missing, Type.Missing, false, XlSaveAsAccessMode.xlNoChange, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing);
workbook.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF,
outputFile, quality, includeProps, false, Type.Missing, Type.Missing, false, Type.Missing);
return((int)ExitCode.Success);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return((int)ExitCode.UnknownError);
}
finally
{
if (workbook != null)
{
workbook.Close();
}
if (!running)
{
if (workbooks != null)
{
workbooks.Close();
}
if (app != null)
{
((Microsoft.Office.Interop.Excel._Application)app).Quit();
}
}
// Clean all the COM leftovers
Converter.ReleaseCOMObject(workbook);
Converter.ReleaseCOMObject(workbooks);
Converter.ReleaseCOMObject(app);
if (tmpFile != null && File.Exists(tmpFile))
{
System.IO.File.Delete(tmpFile);
// Remove the temporary path to the temp file
Directory.Delete(Path.GetDirectoryName(tmpFile));
}
}
}
protected virtual void Dispose(bool disposing)
{
if (disposed)
{
return;
}
disposed = true;
if (disposing && started)
{
int count = workbooks.Count;
for (int i = 0; i < count; i++)
{
workbooks[0].Close(false);
}
workbooks = null;
excelProcess.Dispose();
}
if (started)
{
Marshal.ReleaseComObject(_workbooks);
_workbooks = null;
Process process = null;
try
{
var hWnd = (IntPtr)_excel.Hwnd;
process = ProcessFunctions.GetProcessByHwnd(hWnd);
_excel.DisplayAlerts = true;
_excel.IgnoreRemoteRequests = false;
_excel.Quit();
Marshal.ReleaseComObject(_excel);
_excel = null;
process.WaitForExit(1000);
}
catch
{
}
finally
{
if (process != null && !process.HasExited)
{
ProcessFunctions.TryKillProcess(process);
}
process?.Dispose();
}
}
}
private void createReportButton_Click(object sender, EventArgs e)
{
if (Directory.Exists(Settings.Default.FilesDir))
{
createReportButton.Enabled = false;
// Получаем список файлов в каталоге
string[] filesArray = Directory.GetFiles(Settings.Default.FilesDir);
foreach (string fileName in filesArray)
{
// Проверяем расширение файла
FileInfo infoFile = new FileInfo(fileName);
// Совпадает с датами формата: 1900-01-01 2007/08/13 1900.01.01 1900 01 01 1900-01.01
//string fileNamePattern = @"(19|20)\d\d([- /.])(0[1-9]|1[012])([- /.])(0[1-9]|[12][0-9]|3[01])";
string month;
if (dateTimePicker1.Value.Month <= 9) { month = "0" + dateTimePicker1.Value.Month; }
else { month = dateTimePicker1.Value.Month.ToString(); };
string fileNamePattern;
if (checkBox1.Checked)
{
fileNamePattern = "(" + dateTimePicker1.Value.Year + @")([- /.])("
+ month + @")([- /.])(0[1-9]|[12][0-9]|3[01])";
}
else
{
fileNamePattern = ".";
}
// Обнуляем счётчик обработаных файлов
filesCount = 0;
if ((File.Exists(fileName)) && Regex.IsMatch(fileName, fileNamePattern) && (infoFile.Extension == ".dpm"))
{
filesCount++;
// Создаём потоки чтения файлов.
FileStream fileStream = new FileStream(
fileName,
FileMode.Open,
FileAccess.Read
);
StreamReader streamReader = new StreamReader(
fileStream,
Encoding.GetEncoding(1251)
);
toolStripStatusLabel2.Text = "Обработка файла: " + infoFile.Name;
// Считываем построчно файл до самого конца
while (!streamReader.EndOfStream)
{
// Разделяем полученную строку
// Структтура массива:
// info[0] - знак выхождения или выхода трека (">" или "<")
// info[1] - дата и время выхода пести
// info[2] - идентификатор трека при составлении плейлиста
// info[3] - псевдоним, который определяет положение треков на диске
// info[4] - имя файла трека относительно псевдонима
// info[5] - длительность трека
string[] info = streamReader.ReadLine().Split('\t');
// Считаем только вхождение файла
// Т.е. проверяем на символ ">" и проверяем только определённый псевдоним
string[] aliases = Properties.Settings.Default.Aliases.Split(';');
foreach (string alias in aliases)
{
if ((info[0].Trim() == ">") && (info[3].Trim() == alias.Trim()))
{
if (!tableCount.Contains(info[4]))
{
// Если записи нет, создаём новую
tableCount.Add(info[4], 1);
tableDuration.Add(info[4], info[5]);
}
else
{
// Если запись есть, увеличиваем счётчик
tableCount[info[4]] = (int)tableCount[info[4]] + 1;
}
}
}
}
// Закрываем потоки чтения файлов.
streamReader.Close();
fileStream.Close();
} // End If File Exist
} // End Foreach FileNames
// Меняем статус
toolStripStatusLabel2.Text = "Обработано файлов: " + filesCount;
// Создаём нумератор для управление Хэш массивом
IDictionaryEnumerator tableCountEnum = tableCount.GetEnumerator();
toolStripStatusLabel2.Text = "Создание файла отчёта.";
#region СОЗДАЁМ НОВЫЙ ДОКУМЕНТ EXCEL
// Открываем приложение
excelapp = new Excel.Application();
// Прячем окно программы
excelapp.Visible = false;
// Создаём документ с одной книгой
excelapp.SheetsInNewWorkbook = 1;
// Добавляем книгу
excelapp.Workbooks.Add(Type.Missing);
//Запрашивать сохранение
//excelapp.DisplayAlerts = true;
excelapp.DisplayAlerts = false;
//Получаем набор ссылок на объекты Workbook (на созданные книги)
excelappworkbooks = excelapp.Workbooks;
//Получаем ссылку на книгу 1 - нумерация от 1
excelappworkbook = excelappworkbooks[1];
#endregion
toolStripStatusLabel2.Text = "Генерация отчёта.";
#region РАБОТА С ЯЧЕЙКАМИ
excelsheets = excelappworkbook.Worksheets;
// Получаем ссылку на лист 1
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1);
#region Примеры: Выделение группы ячеек
//excelcells = excelworksheet.get_Range("A1", "С10");
// Тоже
//excelcells = excelworksheet.get_Range("A1", "С10").Cells;
//excelcells = excelworksheet.get_Range("A1", "С10").Rows;
//excelcells = excelworksheet.get_Range("A1", "С10").Cells;
// Одну ячейку
//excelcells = excelworksheet.get_Range("A1", "A1");
//excelcells = excelworksheet.get_Range("A1", Type.Missing);
// Десять строк с 1 по 10ю
//excelcells = (Excel.Range)excelworksheet.Rows["1:10", Type.Missing];
// Три столбца
//excelcells = (Excel.Range)excelworksheet.Columns["A:C", Type.Missing];
// Одну строку
//excelcells = (Excel.Range)excelworksheet.Rows["1", Type.Missing];
// Один столбец
//excelcells = (Excel.Range)excelworksheet.Columns["C", Type.Missing];
#endregion
// Выбираем первую ячейку
excelcells = excelworksheet.get_Range("A1", Type.Missing).Cells;
excelcells.Value2 = "Испольнитель";
excelcells = excelworksheet.get_Range("B1", Type.Missing).Cells;
excelcells.Value2 = "Трек";
excelcells = excelworksheet.get_Range("C1", Type.Missing).Cells;
excelcells.Value2 = "Длительность";
excelcells = excelworksheet.get_Range("D1", Type.Missing).Cells;
excelcells.Value2 = "Количество";
excelcells = excelworksheet.get_Range("A1", "D1").Cells;
excelcells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
#endregion
#region ЗАПИСЫВАЕМ ДАННЫЕ В ФАЙЛ
// Сбрасываем счётчик Хэша в начало
tableCountEnum.Reset();
// В цикле перебираем все записи Хэша
for (int i = 0; i < tableCount.Count; i++)
{
// Переход к следующей записи
tableCountEnum.MoveNext();
// Выделяем имя файла из пути к файлу
string trackName = Regex.Match(tableCountEnum.Key.ToString(), @"[^\\]*$").Value;
// Отрезаем расширение файла
trackName = trackName.Substring(0, trackName.Length - 4);
// Заменяем тире с пробелами на знак %
trackName = trackName.Replace("-", "%");
// Разделяем название группы и название трека
string[] fullName = trackName.Split('%');
// Работаем с документом
// Начинаем запись исполнителей со второй строки
int m = i + 2;
excelcells = (Excel.Range)excelworksheet.Cells[m, 1];
excelcells.Value2 = fullName[0].Trim();
excelcells = (Excel.Range)excelworksheet.Cells[m, 2];
if (fullName.Length > 1) {excelcells.Value2 = fullName[1].Trim(); }
excelcells = (Excel.Range)excelworksheet.Cells[m, 3];
excelcells.Value2 = tableDuration[tableCountEnum.Key];
excelcells = (Excel.Range)excelworksheet.Cells[m, 4];
excelcells.Value2 = tableCountEnum.Value.ToString();
}
#endregion
toolStripStatusLabel2.Text = "Сохранение документа.";
#region ЗАВЕРШАЕМ РАБОТУ С EXCEL
//Ссылку можно получить и так, но тогда надо знать имена книг,
//причем, после сохранения - знать расширение файла
//excelappworkbook=excelappworkbooks["Книга 1"];
//Запроса на сохранение для книги не должно быть
excelappworkbook.Saved = true;
// ФОрмат сохранения документа
excelapp.DefaultSaveFormat = Excel.XlFileFormat.xlWorkbookNormal;
// Сохраняем книгу
try
{
// Определяем имя путь сохранения файла
// Если каталог указан, проверяем его корректность и сохраняем файл
// Если каталог не указан или не существует, сохраняем отчёт в папке с программой
string saveFileName;
if (Directory.Exists(Settings.Default.ReportDir) && Settings.Default.ReportDir != "")
{
saveFileName = Settings.Default.ReportDir + "\\Report " +
DateTime.Now.ToShortDateString() + ".xls";
}
else
{
saveFileName = Application.StartupPath + "\\Report " +
DateTime.Now.ToShortDateString() + ".xls";
}
//excelappworkbook.Save();
excelappworkbook.SaveAs(saveFileName, Excel.XlFileFormat.xlWorkbookNormal);
MessageBox.Show("Отчёт успешно сгенерирован и сохранён в файл: " + saveFileName, "Готово",
MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ошибка сохранения файла отчёта.",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
// Закрываем приложение
excelapp.Quit();
createReportButton.Enabled = true;
}
#endregion
toolStripStatusLabel2.Text = "";
} // End if DirectoryExists
else
{
// Выводим сообщение, если каталог отчётов не указан
MessageBox.Show("Каталог с файлами отчётов не найден!",
"Ошибка открытия каталога.", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// 通过dataTable把数据库中数据导出到Excel中
/// </summary>
/// <param name="dgv"></param>
/// <param name="sql"></param>
/// <param name="fileName"></param>
/// <returns></returns>
public void DataBaseToExcel1(DataGridView dgv, string sql, string fileName)
{
DataSet ds = new DataSet();
database.ReadDataBase(sql, "historydata", ds);
System.Data.DataTable dtInfo = new System.Data.DataTable();
dtInfo = ds.Tables["historydata"];
if (dtInfo.Rows.Count == 0)
{
MessageBox.Show("没有数据！！！", "提示信息");
return;
}
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
return;
}
//建立Excel对象
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
if (excel == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel", "警告");
return;
}
System.Reflection.Missing miss = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Workbooks workbooks = excel.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet;
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1
int countItem = 0; //记录条数
int k = 0;
//生成字段名称
for (int i = 0; i < dgv.ColumnCount; i++)
{
excel.Cells[1, i + 1] = dgv.Columns[i].HeaderText;
}
//填充数据
for (int i = 0; i < dtInfo.Rows.Count; i++)
{
countItem = i;
if (countItem % 65535 == 0 && countItem > 0)//一个sheet最多容纳数据条数65535
{
//新加sheet
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(miss, miss, miss, miss);
//生成字段名称
for (int ihead = 0; ihead < dgv.ColumnCount; ihead++)
{
excel.Cells[1, ihead + 1] = dgv.Columns[ihead].HeaderText;
}
k = 0;
}
for (int j = 0; j < dtInfo.Columns.Count; j++)
{
if (dtInfo.Rows[i][j] == typeof(string))
{
worksheet.Cells[k + 2, j + 1] = "'" + dtInfo.Rows[i][j].ToString();
}
else
{
worksheet.Cells[k + 2, j + 1] = dtInfo.Rows[i][j].ToString();
}
}
k++;
}
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
MessageBox.Show(saveFileName + "文件保存成功", "提示");
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
excel.Quit();
GC.Collect();//垃圾回收
}
/// <summary>
/// Retrieve worksheet and name range names.
/// </summary>
/// <returns></returns>
/// <remarks></remarks>
public bool GetInformation()
{
bool Success = true;
if (!(System.IO.File.Exists(FileName)))
{
Exception ex = new Exception("Failed to locate '" + FileName + "'");
this.LastException = ex;
throw ex;
}
mSheets.Clear();
mNameRanges.Clear();
mSheetsData.Clear();
if (mReferenceTables != null)
{
mReferenceTables.Clear();
}
Excel.Application xlApp = null;
Excel.Workbooks xlWorkBooks = null;
Excel.Workbook xlWorkBook = null;
Excel.Workbook xlActiveRanges = null;
Excel.Names xlNames = null;
Excel.Sheets xlWorkSheets = null;
try
{
xlApp = new Excel.Application();
xlApp.DisplayAlerts = false;
xlWorkBooks = xlApp.Workbooks;
xlWorkBook = xlWorkBooks.Open(FileName);
xlActiveRanges = xlApp.ActiveWorkbook;
xlNames = xlActiveRanges.Names;
for (int x = 1; x <= xlNames.Count; x++)
{
Excel.Name xlName = xlNames.Item(x);
mNameRanges.Add(xlName.Name);
Marshal.FinalReleaseComObject(xlName);
xlName = null;
}
xlWorkSheets = xlWorkBook.Sheets;
for (int x = 1; x <= xlWorkSheets.Count; x++)
{
Excel.Worksheet Sheet1 = (Excel.Worksheet)xlWorkSheets[x];
mSheets.Add(Sheet1.Name);
mSheetsData.Add(x, Sheet1.Name);
Marshal.FinalReleaseComObject(Sheet1);
Sheet1 = null;
}
GetReferenceTables(xlWorkSheets);
ReleaseComObject(xlWorkSheets);
xlWorkBook.Close();
xlApp.UserControl = true;
xlApp.Quit();
}
catch (Exception ex)
{
this.LastException = ex;
Success = false;
}
finally
{
if (xlWorkSheets != null)
{
Marshal.FinalReleaseComObject(xlWorkSheets);
xlWorkSheets = null;
}
if (xlNames != null)
{
Marshal.FinalReleaseComObject(xlNames);
xlNames = null;
}
if (xlActiveRanges != null)
{
Marshal.FinalReleaseComObject(xlActiveRanges);
xlActiveRanges = null;
}
if (xlActiveRanges != null)
{
Marshal.FinalReleaseComObject(xlActiveRanges);
xlActiveRanges = null;
}
if (xlWorkBook != null)
{
Marshal.FinalReleaseComObject(xlWorkBook);
xlWorkBook = null;
}
if (xlWorkBooks != null)
{
Marshal.FinalReleaseComObject(xlWorkBooks);
xlWorkBooks = null;
}
if (xlApp != null)
{
Marshal.FinalReleaseComObject(xlApp);
xlApp = null;
}
}
return(Success);
}
