public CellFixForm(Microsoft.Office.Interop.Excel.Range cell, System.Drawing.Color color, Action ReAnalyzeFn)
{
_cell = cell;
_color = color;
_fn = ReAnalyzeFn;
InitializeComponent();
}
public static void AddData(int row, int col, string data,
string cell1, string cell2, string format)
{
Worksheet.Cells[row, col] = data;
WorkSheetRange = Worksheet.Range[cell1, cell2];
WorkSheetRange.Borders.Color = Color.Black.ToArgb();
WorkSheetRange.NumberFormat = format;
}
public void createHeaders(int row, int col, string htext, string cell1,
string cell2, int mergeColumns,string b, bool font,int size,string
fcolor)
{
worksheet.Cells[row, col] = htext;
workSheet_range = worksheet.get_Range(cell1, cell2);
workSheet_range.Merge(mergeColumns);
switch(b)
{
case "YELLOW":
workSheet_range.Interior.Color = System.Drawing.Color.Yellow.ToArgb();
break;
case "GRAY":
workSheet_range.Interior.Color = System.Drawing.Color.Gray.ToArgb();
break;
case "GAINSBORO":
workSheet_range.Interior.Color =
System.Drawing.Color.Gainsboro.ToArgb();
break;
case "Turquoise":
workSheet_range.Interior.Color =
System.Drawing.Color.Turquoise.ToArgb();
break;
case "PeachPuff":
workSheet_range.Interior.Color =
System.Drawing.Color.PeachPuff.ToArgb();
break;
default:
// workSheet_range.Interior.Color = System.Drawing.Color..ToArgb();
break;
}
workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb();
workSheet_range.Font.Bold = font;
workSheet_range.ColumnWidth = size;
if (fcolor.Equals(""))
{
workSheet_range.Font.Color = System.Drawing.Color.White.ToArgb();
}
else {
workSheet_range.Font.Color = System.Drawing.Color.Black.ToArgb();
}
}
private void btPrint_Click(object sender, EventArgs e)
{
try
{
DataTable Report = (DataTable)dgvData.DataSource;
int totalColumn = Report.Columns.Count;
Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
excel.Application.Workbooks.Add(true);
#region 填充数据
excel.Cells[1, 1] = HIS.SYSTEM.BussinessLogicLayer.Classes.BaseData.WorkName + "住院预交金统计报表";
Microsoft.Office.Interop.Excel.Range titleStartcell = (Microsoft.Office.Interop.Excel.Range)excel.Cells[1, 1];
Microsoft.Office.Interop.Excel.Range titleEndcell = (Microsoft.Office.Interop.Excel.Range)excel.Cells[1, totalColumn];
excel.get_Range(titleStartcell, titleEndcell).Merge(0);
excel.get_Range(titleStartcell, titleEndcell).HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
excel.get_Range(titleStartcell, titleEndcell).Font.Name = "宋体";
excel.get_Range(titleStartcell, titleEndcell).Font.Size = 15;
excel.get_Range(titleStartcell, titleEndcell).Font.Bold = true;
excel.Cells[2, 1] = "统计时间：";
excel.Cells[2, 2] = dtpBdate.Value.ToString("yyyy-MM-dd HH:mm:ss") + " -- " + dtpEdate.Value.ToString("yyyy-MM-dd HH:mm:ss");
excel.get_Range((Microsoft.Office.Interop.Excel.Range)excel.Cells[2, 2], (Microsoft.Office.Interop.Excel.Range)excel.Cells[2, 6]).Merge(0);
// excel.Cells[2, totalColumn - 2] = "制表人：";
// excel.Cells[2, totalColumn - 1] = .Name;
int row = 3;
Microsoft.Office.Interop.Excel.Range startCell = (Microsoft.Office.Interop.Excel.Range)excel.Cells[row, 1];
Microsoft.Office.Interop.Excel.Range endCell = (Microsoft.Office.Interop.Excel.Range)excel.Cells[row + Report.Rows.Count, totalColumn];
for (int i = 0; i < Report.Columns.Count; i++)
{
excel.Cells[row, i + 1] = Report.Columns[i].ColumnName.ToString();
}
row = row + 1;
for (int i = 0; i < Report.Rows.Count; i++)
{
for (int j = 0; j < Report.Columns.Count; j++)
{
object objValue = Report.Rows[i][Report.Columns[j].ColumnName];
if (Convert.IsDBNull(objValue))
{
continue;
}
excel.Cells[row + i, j + 1] = objValue.ToString();
}
}
#endregion
#region 画网格线
object obj = excel.get_Range(startCell, endCell).Select();
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlDiagonalDown].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlDiagonalUp].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
row = row + Report.Rows.Count + 1;
excel.Cells[row, 1] = "审核人";
excel.Cells[row, totalColumn - 2] = "打印日期：";
excel.Cells[row, totalColumn] = HIS.SYSTEM.PubicBaseClasses.XcDate.ServerDateTime.ToString("yyyy-MM-dd");
#endregion
excel.ActiveWindow.DisplayGridlines = false;
excel.Visible = true;
}
catch
{
MessageBox.Show("输出到Excel发生错误！", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
GC.Collect();
}
}
async private void uploadExcelToGridView()
{
try
{
progressBar1.Value = 0;
button7.Enabled = false;
string fname = "";
OpenFileDialog fdlg = new OpenFileDialog();
fdlg.Title = "Excel File Dialog";
// fdlg.InitialDirectory = @"c:\";
fdlg.Filter = "All files (*.*)|*.*|All files (*.*)|*.*";
fdlg.FilterIndex = 2;
fdlg.RestoreDirectory = true;
if (fdlg.ShowDialog() == DialogResult.OK)
{
fname = fdlg.FileName;
}
else
{
button7.Enabled = true;
button10.Visible = false;
return;
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(fname);
Microsoft.Office.Interop.Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
// dt.Column = colCount;
dataGridView1.ColumnCount = colCount;
dataGridView1.RowCount = rowCount;
progressBar1.Maximum = rowCount;
for (int i = 1; i <= rowCount; i++)
{
if (forceStop)
{
forceStop = false;
clearDataGridView(dataGridView1);
button7.Enabled = true;
button10.Visible = false;
statusLabel.Text = "Canceled your file upload";
return;
}
button7.Enabled = false;
for (int j = 1; j <= colCount; j++)
{
//write the value to the Grid
if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Value2 != null)
{
if (xlRange.Cells[i, j].Value2.ToString().Contains(","))
{
string xx = "";
for (int w = 0; w < xlRange.Cells[i, j].Value2.ToString().Length; w++)
{
if (xlRange.Cells[i, j].Value2.ToString().Substring(w, 1).Equals(","))
{
xx += "_";
}
else
{
xx += xlRange.Cells[i, j].Value2.ToString().Substring(w, 1);
}
}
dataGridView1.Rows[i - 1].Cells[j - 1].Value = xx;
}
else
{
dataGridView1.Rows[i - 1].Cells[j - 1].Value = xlRange.Cells[i, j].Value2.ToString();
}
}
// Console.Write(xlRange.Cells[i, j].Value2.ToString() + "\t");
//add useful things here!
await Task.Delay(05);
}
progressBar1.Value += 1;
statusLabel.Text = "Downloading excel file. Rows to left complete: " + (rowCount - i).ToString();
}
button7.Enabled = true;
statusLabel.Text = "Loaded " + dataGridView1.Rows.Count.ToString() + " rows of data. Ready to create the database";
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
//rule of thumb for releasing com objects:
// never use two dots, all COM objects must be referenced and released individually
// ex: [somthing].[something].[something] is bad
//release com objects to fully kill excel process from running in the background
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
//close and release
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
//quit and release
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
//first row should be col headers
int colCount2 = dataGridView1.Columns.Count;
for (int i = 0; i < colCount2; i++)
{
dataGridView1.Columns[i].HeaderText = dataGridView1.Rows[0].Cells[i].Value.ToString();
}
dataGridView1.Rows.RemoveAt(0);
button8.Enabled = true;
button10.Visible = false;
}
catch (Exception ex)
{
MessageBox.Show("Couldnt read the excel file: " + ex.ToString());
button7.Enabled = true;
button8.Enabled = false;
clearDataGridView(dataGridView1);
}
}
private void spreadSheetToolStripMenuItem_Click(object sender, EventArgs e)
{
Stream myStream = null;
//Creates an open file dialog box to pull the .xml file from
OpenFileDialog theDialog = new OpenFileDialog();
theDialog.Title = "Open Excel File with Data";
//We don't want anything expect excel files
theDialog.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm";
theDialog.InitialDirectory = @"C:\";
DataRow row;
if (theDialog.ShowDialog() == DialogResult.OK)
{
try
{
if ((myStream = theDialog.OpenFile()) != null)
{
//generates some info about the file location in order to read the file
System.IO.FileInfo fInfo = new System.IO.FileInfo(theDialog.FileName);
string strFileLocation = fInfo.FullName;
string pathName = theDialog.FileName;
string fileName = System.IO.Path.GetFileNameWithoutExtension(theDialog.FileName);
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(pathName);
Microsoft.Office.Interop.Excel._Worksheet excelWorksheet = excelWorkbook.Sheets[1];
Microsoft.Office.Interop.Excel.Range excelRange = excelWorksheet.UsedRange;
rowCount = excelRange.Rows.Count; //get row count of excel sheet
colCount = excelRange.Columns.Count; //get column cout of excel data
theData = new Double[rowCount];
//get the labels of Excel sheet
for (int i = 1; i <= rowCount; i++)
{
for (int j = 1; j <= colCount; j++)
{
theDataContainer.Columns.Add(excelRange.Cells[i, j].Value2.ToString());
}
break;
}
int rowCounter; //used for row index number
for (int i = 2; i < rowCount; i++)
{
row = theDataContainer.NewRow(); //assign new row to DataTable
rowCounter = 0;
for (int j = 1; j <= colCount; j++) // loop for available column of excel data
{
//check to see if the cell is empty
if (excelRange.Cells[i, j] != null && excelRange.Cells[i, j].Value2 != null)
{
row[rowCounter] = excelRange.Cells[i, j].Value2.ToString();
if (j == 2)
{
theData[theDataCount] = excelRange.Cells[i, j].Value2;
theDataCount++;
}
}
else
{
row[i] = "";
}
rowCounter++;
}
theDataContainer.Rows.Add(row); //add the row to the DataTable
}
dataGridView1.DataSource = theDataContainer; //assign DataTable as Datasource for DataGridview
dataGridView1.Columns[0].Visible = false; //makes the names invisible originally
//close and clean excel process
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.ReleaseComObject(excelRange);
Marshal.ReleaseComObject(excelWorksheet);
//quit apps
excelWorkbook.Close();
Marshal.ReleaseComObject(excelWorkbook);
excelApp.Quit();
Marshal.ReleaseComObject(excelApp);
}
}
catch (Exception ex)
{
MessageBox.Show("Error: Could not read file from disk. \nOriginal error " + ex.Message);
}
}
}
/// <summary>
/// 采购注文书国外
/// </summary>
public static int ExportPurchaseOverseas(string templetFile, string outFile, DataTable dt, Hashtable ht)
{
object missing = Missing.Value;
DateTime beforeTime;
DateTime afterTime;
#region Excel文件初始化
//只读属性的变更
FileInfo fi = new FileInfo(templetFile);
if (fi.Attributes.ToString().IndexOf("ReadOnly") != -1)
{
fi.Attributes = FileAttributes.Normal;
}
//拷贝模版文件生成新文件
if (File.Exists(templetFile))
{
try
{
File.Copy(templetFile, outFile, true);
}
catch (Exception ex)
{
Logger.Error("文件正在运行，重新生成文件失败。", ex);
return(CConstant.EXPORT_RUNNING);
}
}
else
{
Logger.Error("模版文件不存在。", null);
return(CConstant.EXPORT_TEMPLETE_FILE_NOT_EXIST);
}
beforeTime = DateTime.Now;
//创建一个Application对象并使其可见
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
app.Visible = false;
afterTime = DateTime.Now;
//打开模板文件，得到WorkBook对象
Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Open(outFile, missing, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing);
//得到WorkSheet对象
Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets.get_Item(1);
//特定内容的替换
foreach (DictionaryEntry de in ht)
{
try
{
workSheet.Cells.Replace(de.Key, de.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
catch
{
continue;
}
}
#endregion
//数据
int startRow = 19;
int endRow = dt.Rows.Count + startRow;
int addLine = 0;
for (int i = startRow; i < endRow; i++)
{
if (i >= 36 && i < endRow - 1)
{
Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)workSheet.Rows[i, Type.Missing];
range.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown,
Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
addLine++;
}
DataRow dr = dt.Rows[i - startRow];
for (int j = 0; j < dt.Columns.Count; j++)
{
if (dt.Columns[j].ColumnName.Contains("X_"))
{
continue;
}
if (dr[j].GetType() == typeof(string))
{
workSheet.Cells[i, j + 1] = "'" + Convert.ToString(dr[j]);
}
else
{
workSheet.Cells[i, j + 1] = Convert.ToString(dr[j]);
}
}
}
startRow = 78 + addLine;
endRow = dt.Rows.Count + startRow;
for (int i = startRow; i < endRow; i++)
{
if (i >= (95 + addLine) && i < endRow - 1)
{
Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)workSheet.Rows[i, Type.Missing];
range.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown,
Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
}
DataRow dr = dt.Rows[i - startRow];
for (int j = 0; j < dt.Columns.Count; j++)
{
if (dt.Columns[j].ColumnName.Contains("X_"))
{
continue;
}
if (dr[j].GetType() == typeof(string))
{
workSheet.Cells[i, j + 1] = "'" + Convert.ToString(dr[j]);
}
else
{
workSheet.Cells[i, j + 1] = Convert.ToString(dr[j]);
}
}
}
#region 输出Excel文件并退出
//输出Excel文件并退出
try
{
workBook.Save();
workBook.Close(null, null, null);
app.Workbooks.Close();
app.Application.Quit();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
workSheet = null;
workBook = null;
app = null;
GC.Collect();
}
catch (Exception ex)
{
try
{
File.Delete(outFile);
}
catch (Exception e)
{
Logger.Error("Excel文件保存,失败后复制文件的删除。", e);
}
Logger.Error("Excel文件保存失败。", null);
return(CConstant.EXPORT_FAILURE);
}
finally
{
Process[] myProcesses;
DateTime startTime;
myProcesses = Process.GetProcessesByName("Excel");
//得不到Excel进程ID，暂时只能判断进程启动时间
foreach (Process myProcess in myProcesses)
{
startTime = myProcess.StartTime;
if (startTime > beforeTime && startTime < afterTime)
{
myProcess.Kill();
}
}
}
#endregion
return(CConstant.EXPORT_SUCCESS);
}
public static void CreateHeaders(int row, int col, string htext, string cell1,
string cell2, int mergeColumns, string b, bool font, int size, string
fcolor)
{
Worksheet.Cells[row, col] = htext;
WorkSheetRange = Worksheet.Range[cell1, cell2];
WorkSheetRange.Merge(mergeColumns);
switch (b)
{
case "YELLOW":
WorkSheetRange.Interior.Color = Color.Yellow.ToArgb();
break;
case "GRAY":
WorkSheetRange.Interior.Color = Color.Gray.ToArgb();
break;
case "GAINSBORO":
WorkSheetRange.Interior.Color =
Color.Gainsboro.ToArgb();
break;
case "Turquoise":
WorkSheetRange.Interior.Color =
Color.Turquoise.ToArgb();
break;
case "PeachPuff":
WorkSheetRange.Interior.Color =
Color.PeachPuff.ToArgb();
break;
}
WorkSheetRange.Borders.Color = Color.Black.ToArgb();
WorkSheetRange.Font.Bold = font;
WorkSheetRange.ColumnWidth = size;
WorkSheetRange.Font.Color = fcolor.Equals("") ? Color.White.ToArgb() : Color.Black.ToArgb();
}
//●予約表印刷
private void btnResPrint_Click(object sender, EventArgs e)
{
DataTable dtlRes = clsDbj.fctFilldgv("SELECT ProductId,MemberId"
+ " FROM t_reservation "
// +" WHERE `ComeDate` = '0000-00-00'" //今日の日付
+ " GROUP BY ProductId ,MemberId,ResNumber"
+ " ORDER BY `ResNumber`"
+ ";"
);
DataTable dtlSrock = clsDbj.fctFilldgv("SELECT `Stock_ProductId`, `Stock_StockId`,`Product_ProductName`,`Product_Genre`"
+ " FROM stockt,productt"
+ " WHERE `Product_ProductId` = `Stock_ProductId`"
+ " AND `Stock_LoanCheck` = '1'"
+ ";");
for (int i = 0; i < 6; i++)
{
dtlResPrint.Columns.Add();
}
//予約テーブルの行数分繰り返す
for (int i = 0; i < dtlRes.Rows.Count; i++)
{
//在庫テーブルの行数分繰り返す
for (int j = 0; j < dtlSrock.Rows.Count; j++)
{
//同じプロダクトIDがあった場合
if (dtlRes.Rows[i][0].ToString() == dtlSrock.Rows[j][0].ToString())
{
object[] objResAdd = new object[6];
for (int k = 0; k < 2; k++)
{
objResAdd[k] = dtlRes.Rows[i][k].ToString();
}
//
for (int k = 2; k < 4; k++)
{
objResAdd[k] = dtlSrock.Rows[i][k].ToString();
}
dtlResPrint.Rows.Add(objResAdd);
//ストックテーブルの行を削除する
dtlSrock.Rows.RemoveAt(j);
break;
}
}
}
//テスト表示
dataGridView1.DataSource = dtlResPrint;
//エクセルの宣言
Microsoft.Office.Interop.Excel.Application oXlsApp;
Microsoft.Office.Interop.Excel.Worksheet oSheet;
Microsoft.Office.Interop.Excel.Range[,] Syouhin = new Microsoft.Office.Interop.Excel.Range[10, 10];
// エクセル起動
oXlsApp = new Microsoft.Office.Interop.Excel.Application();
// エクセル非表示
oXlsApp.Application.Visible = false;
oXlsApp.Application.DisplayAlerts = false;
//テンプレートを呼び出し
oXlsApp.Application.Workbooks.Add("C:/Users/Kensaku/Dropbox/ResSeet.xltx");
// シート選択
oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oXlsApp.Worksheets[1];
// カラム幅設定
//oSheet.Columns("C").ColumnWidth = 20
((Microsoft.Office.Interop.Excel.Range)oSheet.get_Range(oSheet.Cells[11, 7], oSheet.Cells[65535, 7])).ColumnWidth = 17;
// カラムの書式設定の表示形式を文字列にする
//oSheet.Columns("C").NumberFormatLocal = "@"
((Microsoft.Office.Interop.Excel.Range)oSheet.get_Range(
oSheet.Cells[1, 3],
oSheet.Cells[65535, 3])).NumberFormatLocal = "@";
//発注日の当てはめ
//セルを当てはめる
//ループ開始
int intStart = 5;
//リストの数を取得
int intDgvRowsCount = dtlResPrint.Rows.Count;
for (int i = 0; i < intDgvRowsCount; i++)
{
//横幅
for (int j = 0; j < 5; j++)
{
Syouhin[i, j] = oSheet.get_Range(oSheet.Cells[intStart, j + 1], oSheet.Cells[intStart, j + 1]);
switch (j)
{
case 0:
break;
case 1:
Syouhin[i, j].Value2 = dtlResPrint.Rows[i][0].ToString(); //予約者名
break;
case 2:
Syouhin[i, j].Value2 = dtlResPrint.Rows[i][1].ToString(); //商品ID
break;
case 3:
Syouhin[i, j].Value2 = dtlResPrint.Rows[i][2].ToString(); //商品名
break;
case 4:
Syouhin[i, j].Value2 = dtlResPrint.Rows[i][3].ToString(); //ジャンル
break;
default:
break;
}
}
intStart++;
}
// エクセル表示
oXlsApp.Application.Visible = true;
//メール送信
for (int i = 0; i < dtlResPrint.Rows.Count; i++)
{
string strMailMessage = "";
strMailMessage += "お客様がご予約されていた商品が準備できました。\n本日の来店をお待ちしております。";
clsOpe.SendMailac("MATSUTAYA予約完了お知らせ", strMailMessage);
}
}
//打印功能
private Microsoft.Office.Interop.Excel._Worksheet printValue(Microsoft.Office.Interop.Excel._Worksheet mysheet, Microsoft.Office.Interop.Excel._Workbook mybook)
{
//外表信息
mysheet.Cells[3, 3].Value = dtOuter.Rows[0]["产品代码"].ToString();
mysheet.Cells[3, 9].Value = dtOuter.Rows[0]["产品批号"].ToString();
mysheet.Cells[3, 10].Value = "生产日期：" + Convert.ToDateTime(dtOuter.Rows[0]["生产日期"].ToString()).Year.ToString() + "年 " + Convert.ToDateTime(dtOuter.Rows[0]["生产日期"].ToString()).Month.ToString() + "月 " + Convert.ToDateTime(dtOuter.Rows[0]["生产日期"].ToString()).Day.ToString() + "日";
mysheet.Cells[12, 2].Value = dtOuter.Rows[0]["抽检量合计"].ToString();
mysheet.Cells[12, 3].Value = dtOuter.Rows[0]["游离异物合计"].ToString();
mysheet.Cells[12, 4].Value = dtOuter.Rows[0]["内含黑点晶点合计"].ToString();
mysheet.Cells[12, 5].Value = dtOuter.Rows[0]["热封线不良合计"].ToString();
mysheet.Cells[12, 6].Value = dtOuter.Rows[0]["其他合计"].ToString();
mysheet.Cells[12, 7].Value = dtOuter.Rows[0]["不良合计"].ToString();
mysheet.Cells[12, 8].Value = dtOuter.Rows[0]["判定"].ToString() == "Yes" ? "√" : "×";
mysheet.Cells[13, 9].Value = "尺寸规格： 宽 " + dtOuter.Rows[0]["尺寸规格宽"].ToString() + " mm × 长 " + dtOuter.Rows[0]["尺寸规格长"].ToString() + " mm（标示±5mm）";
String stringtemp = "";
stringtemp = "检测人：" + dtOuter.Rows[0]["操作员"].ToString();
stringtemp = stringtemp + " 检测日期：" + Convert.ToDateTime(dtOuter.Rows[0]["操作日期"].ToString()).Year.ToString() + "年 " + Convert.ToDateTime(dtOuter.Rows[0]["操作日期"].ToString()).Month.ToString() + "月 " + Convert.ToDateTime(dtOuter.Rows[0]["操作日期"].ToString()).Day.ToString() + "日";
mysheet.Cells[16, 2].Value = stringtemp;
stringtemp = "复核人：" + dtOuter.Rows[0]["审核员"].ToString();
stringtemp = stringtemp + " 复核日期：" + Convert.ToDateTime(dtOuter.Rows[0]["审核日期"].ToString()).Year.ToString() + "年 " + Convert.ToDateTime(dtOuter.Rows[0]["审核日期"].ToString()).Month.ToString() + "月 " + Convert.ToDateTime(dtOuter.Rows[0]["审核日期"].ToString()).Day.ToString() + "日";
mysheet.Cells[16, 9].Value = stringtemp;
//内表信息
int rownum = dtInner.Rows.Count;
//无需插入的部分
for (int i = 0; i < (rownum > 6 ? 6 : rownum); i++)
{
mysheet.Cells[6 + i, 1].Value = Convert.ToDateTime(dtInner.Rows[i]["抽样时间外观检查"].ToString()).ToString("yyyy/MM/dd HH:mm");
mysheet.Cells[6 + i, 2].Value = dtInner.Rows[i]["抽检量外观检查"].ToString();
mysheet.Cells[6 + i, 3].Value = dtInner.Rows[i]["游离异物"].ToString();
mysheet.Cells[6 + i, 4].Value = dtInner.Rows[i]["内含黑点晶点"].ToString();
mysheet.Cells[6 + i, 5].Value = dtInner.Rows[i]["热封线不良"].ToString();
mysheet.Cells[6 + i, 6].Value = dtInner.Rows[i]["其他"].ToString();
mysheet.Cells[6 + i, 7].Value = dtInner.Rows[i]["不良合计"].ToString();
mysheet.Cells[6 + i, 8].Value = dtInner.Rows[i]["判定外观检查"].ToString() == "Yes" ? "√" : "×";
mysheet.Cells[6 + i, 9].Value = Convert.ToDateTime(dtInner.Rows[i]["抽检时间尺寸检测"].ToString()).ToString("yyyy/MM/dd HH:mm");
mysheet.Cells[6 + i, 10].Value = dtInner.Rows[i]["抽检量尺寸检测"].ToString();
mysheet.Cells[6 + i, 11].Value = dtInner.Rows[i]["宽"].ToString() + " × " + dtInner.Rows[i]["长"].ToString();
mysheet.Cells[6 + i, 12].Value = dtInner.Rows[i]["判定尺寸检测"].ToString() == "Yes" ? "√" : "×";
}
//需要插入的部分
if (rownum > 6)
{
for (int i = 6; i < rownum; i++)
{
Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)mysheet.Rows[6 + i, Type.Missing];
range.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown,
Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
mysheet.Cells[6 + i, 1].Value = Convert.ToDateTime(dtInner.Rows[i]["抽样时间外观检查"].ToString()).ToString("yyyy/MM/dd HH:mm");
mysheet.Cells[6 + i, 2].Value = dtInner.Rows[i]["抽检量外观检查"].ToString();
mysheet.Cells[6 + i, 3].Value = dtInner.Rows[i]["游离异物"].ToString();
mysheet.Cells[6 + i, 4].Value = dtInner.Rows[i]["内含黑点晶点"].ToString();
mysheet.Cells[6 + i, 5].Value = dtInner.Rows[i]["热封线不良"].ToString();
mysheet.Cells[6 + i, 6].Value = dtInner.Rows[i]["其他"].ToString();
mysheet.Cells[6 + i, 7].Value = dtInner.Rows[i]["不良合计"].ToString();
mysheet.Cells[6 + i, 8].Value = dtInner.Rows[i]["判定外观检查"].ToString() == "Yes" ? "√" : "×";
mysheet.Cells[6 + i, 9].Value = Convert.ToDateTime(dtInner.Rows[i]["抽检时间尺寸检测"].ToString()).ToString("yyyy/MM/dd HH:mm");
mysheet.Cells[6 + i, 10].Value = dtInner.Rows[i]["抽检量尺寸检测"].ToString();
mysheet.Cells[6 + i, 11].Value = dtInner.Rows[i]["宽"].ToString() + " × " + dtInner.Rows[i]["长"].ToString();
mysheet.Cells[6 + i, 12].Value = dtInner.Rows[i]["判定尺寸检测"].ToString() == "Yes" ? "√" : "×";
}
}
//加页脚
int sheetnum;
SqlDataAdapter da = new SqlDataAdapter("select ID from 产品外观和尺寸检验记录 where 生产指令ID=" + dtOuter.Rows[0]["ID"].ToString(), conn);
DataTable dt = new DataTable("temp");
da.Fill(dt);
List <Int32> sheetList = new List <Int32>();
for (int i = 0; i < dt.Rows.Count; i++)
{
sheetList.Add(Convert.ToInt32(dt.Rows[i]["ID"].ToString()));
}
sheetnum = sheetList.IndexOf(Convert.ToInt32(dtOuter.Rows[0]["ID"])) + 1;
da = new SqlDataAdapter("select ID, 生产指令编号 from 生产指令 where ID=" + dtOuter.Rows[0]["生产指令ID"].ToString(), conn);
da.Fill(dt);
String Instruction = dt.Rows[0]["生产指令编号"].ToString();
mysheet.PageSetup.RightFooter = Instruction + "-16-" + sheetnum.ToString("D3") + " &P/" + mybook.ActiveSheet.PageSetup.Pages.Count.ToString(); // "生产指令-步骤序号- 表序号 /&P"; // &P 是页码
//返回
return(mysheet);
}
object ExcelGetField(Microsoft.Office.Interop.Excel.Range Cell, string tracciato_field, out string errore)
{
errore = "";
object val = Cell.get_Value(Microsoft.Office.Interop.Excel.XlRangeValueDataType.xlRangeValueDefault);
object val2 = Cell.Value2;
string[] ff = tracciato_field.Split(';');
string fieldname = ff[0];
string ftype = ff[2].ToLower().Trim(); //(intero/numero/stringa/codificato/data)
errore = "";
//int len = Convert.ToInt32(ff[3]);
if (val == null)
{
return(DBNull.Value);
}
if (val == DBNull.Value)
{
return(val);
}
if (val.ToString() == "")
{
return(DBNull.Value);
}
decimal numero;
try {
switch (ftype)
{
case "intero": {
string X = val.ToString().Trim().TrimStart('0');
if (X == "")
{
return(0);
}
return(Convert.ToInt32(X));
}
case "stringa":
return(val.ToString().TrimEnd(new char[] { ' ' }));
case "numero":
if (isNumeric(val, out numero))
{
return(Convert.ToDecimal(numero));
}
else
{
errore = " Errore interno nel tracciato per tipo numerico " + fieldname + " di tipo " + ftype + " e di valore " +
val.ToString().Trim().TrimStart('0');
return(null);
}
case "data": // DateTime.FromOADate and DateTime.ToOADate
return(DateTime.FromOADate(Convert.ToDouble(val2)));
case "codificato": {
string[] codici = ff[4].Split('|');
for (int i = 0; i < codici.Length; i++)
{
if (val.ToString().ToLower() == codici[i].ToLower())
{
return(val);
}
}
errore = " Errore interno nel tracciato per tipo codificato " + fieldname + " di tipo " + ftype + " e di valore " +
val.ToString().Trim().TrimStart('0');
return(null);
}
default: {
errore = " Errore interno nel tracciato per tipo " + ftype + " e valore " + val.ToString().Trim().TrimStart('0');
return(null);
}
}
}
catch {
errore = " Errore nella decodifica del campo " + fieldname + " di tipo " + ftype + " e di valore " + val.ToString().Trim().TrimStart('0');
return(null);
}
}
/// <summary>
// 导出至Excel.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void ExportToExceltoolStripMenuItem_Click(object sender, EventArgs e)
{
DataGridViewRowCollection drRows = this.dgv.Rows;
if (drRows.Count == 0)
{
return;
}
//1. 先确定 image path
if (CmdHelper.ifExistsTheProcessByName("excel") || CmdHelper.ifExistsTheProcessByName("et"))
{
Tools.FrmPrompt frmPrompt = new Tools.FrmPrompt("excel.exe|et.exe");
frmPrompt.ShowDialog();
}
//2. 打开Excel
//先获取时间字符串
string currTimeStr = TimeHelper.getCurrentTimeStr();
string xlsFileName = "成衣成本汇总_" + currTimeStr + ".xls";
string srcFilePath = Application.StartupPath + "\\成本汇总\\ProductsCostSummaryTemplate.xls";
string destDir = Application.StartupPath + "\\成本汇总\\";
string destFileName = xlsFileName;
CmdHelper.copyFileToDestDirWithNewFileName(srcFilePath, destDir, destFileName);
//目的文件名为：
destFilePath = destDir + xlsFileName;
MyExcel myExcel = new MyExcel(destFilePath);
myExcel.open(true);
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(myExcel.getFirstWorkSheetAfterOpen());
for (int index = 0; index <= drRows.Count - 1; index++)
{
DataGridViewRow currRow = drRows[index];
string product_name = currRow.Cells["Product_Name"].Value.ToString();
uEHelper.setSpecificCellValue("A" + (2 + index).ToString(), product_name);
//(drRow.Cells["product_name"]
uEHelper.setSpecificCellValue("B" + (2 + index).ToString(), currRow.Cells["total_man_hours"].Value.ToString());
uEHelper.setSpecificCellValue("C" + (2 + index).ToString(), currRow.Cells["total_labour_cost"].Value.ToString());
uEHelper.setSpecificCellValue("D" + (2 + index).ToString(), currRow.Cells["supplier"].Value.ToString());
uEHelper.setSpecificCellValue("E" + (2 + index).ToString(), currRow.Cells["latest_update_time"].Value.ToString());
//picture
//保存此图片
//Image image = ((Image)(currRow.Cells["picture"].Value));
byte[] pictureByteArray = (byte[])currRow.Cells["picture"].Value;
Image image = PictureHelper.ReturnPhoto(pictureByteArray);
string picPath = string.Format(@"{0}\{1}.jpg", destDir, product_name);
image.Save(picPath, image.RawFormat);
Microsoft.Office.Interop.Excel.Range range = uEHelper.getRange("F" + (2 + index).ToString(), "F" + (2 + index).ToString());
uEHelper.pastePicture(range, picPath);
}
myExcel.saveWithoutAutoFit();
myExcel.close();
ShowResult.show(lblResult, string.Format(@"导出完毕，存于：{0}", destFilePath), true);
timerRestoreLabel.Start();
}
//*************************************************************************
// Constructor: DynamicFilterDialog()
//
/// <overloads>
/// Initializes a new instance of the <see
/// cref="DynamicFilterDialog" /> class.
/// </overloads>
///
/// <summary>
/// Initializes a new instance of the <see
/// cref="DynamicFilterDialog" /> class with a workbook.
/// </summary>
///
/// <param name="workbook">
/// Workbook containing the graph contents.
/// </param>
//*************************************************************************
public DynamicFilterDialog(
Microsoft.Office.Interop.Excel.Workbook workbook
)
: this()
{
Debug.Assert(workbook != null);
// Instantiate an object that saves and retrieves the user settings for
// this dialog. Note that the object automatically saves the settings
// when the form closes.
m_oDynamicFilterDialogUserSettings =
new DynamicFilterDialogUserSettings(this);
m_oWorkbook = workbook;
m_oExcelCalculationRestorer =
new ExcelCalculationRestorer(workbook.Application);
m_oExcelCalculationRestorer.TimerIntervalMs =
CalculationRestorerTimerIntervalMs;
m_bHandleControlEvents = false;
m_oChangeEventDelayTimer = new Timer();
m_oChangeEventDelayTimer.Interval = ChangeEventTimerIntervalMs;
m_oChangeEventDelayTimer.Tick += new EventHandler(
this.m_oChangeEventDelayTimer_Tick);
m_oDynamicFilterSettings = null;
m_oEdgeDynamicFilterColumnData = null;
m_oVertexDynamicFilterColumnData = null;
nudFilteredAlpha.Minimum =
(Decimal)AlphaConverter.MinimumAlphaWorkbook;
nudFilteredAlpha.Maximum =
(Decimal)AlphaConverter.MaximumAlphaWorkbook;
nudFilteredAlpha.Value = (Decimal)
( new PerWorkbookSettings(m_oWorkbook) ).FilteredAlpha;
AssertValid();
}
//*************************************************************************
// Method: InitializeDynamicFilters()
//
/// <summary>
/// Initializes the dialog's dynamic filter controls.
/// </summary>
//*************************************************************************
protected void InitializeDynamicFilters()
{
AssertValid();
m_bHandleControlEvents = false;
this.UseWaitCursor = true;
m_oEdgeDynamicFilterColumnData =
InitializeDynamicFiltersForOneTable(WorksheetNames.Edges,
TableNames.Edges, grpEdgeFilters);
m_oVertexDynamicFilterColumnData =
InitializeDynamicFiltersForOneTable(WorksheetNames.Vertices,
TableNames.Vertices, grpVertexFilters);
grpVertexFilters.Top = grpEdgeFilters.Bottom + GroupBoxBottomMargin;
grpEdgeFilters.Visible = grpVertexFilters.Visible = true;
this.UseWaitCursor = false;
m_bHandleControlEvents = true;
}
async Task <int> run(string fname)
{
int result = 0;
//Console.OutputEncoding = Encoding.UTF8;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(fname);
xlWorkbook.WebOptions.Encoding = Microsoft.Office.Core.MsoEncoding.msoEncodingUTF8;
Microsoft.Office.Interop.Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
//add parent don vi
//var a = await read_parent_donvi(rowCount, xlRange);
//add child don vi
//var child_dv = await read_child_donvi(rowCount, xlRange);
//Don vi to nhom
//var child1_dv = await read_child1_donvi(rowCount, xlRange);
//add chuc danh
//var ChucVu = await Read_chucVu(rowCount, xlRange);
// add national
//var national = await Read_national(rowCount, xlRange);
DateTime d = new DateTime(1977, 5, 9);
try
{
// result = await _repository.GetRepository<Account>().CreateAsync(new Account()
// {
// code = 1111,
// FullName = "Nguyễn Anh Dũng",
// Password = StringHelper.stringToSHA512("123456").ToLower(),
// Email = "*****@*****.**",
// CreateDate = DateTime.Now,
// IsManageAccount = true,
// IsNormalAccount = false,
// PhoneNumber = "0978132474"
// }, 0);
for (int i = 1; i <= 100; i++)
{
Debug.WriteLine(i);
result = await _repository.GetRepository <Weight>().CreateAsync(new Weight()
{
code = i,
}, 0);
}
}
catch (Exception ex)
{
throw;
}
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
return(result);
}
public void Export(DataTable dt, string sheetName, string title)
{
//Tạo các đối tượng Excel
Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks oBooks;
Microsoft.Office.Interop.Excel.Sheets oSheets;
Microsoft.Office.Interop.Excel.Workbook oBook;
Microsoft.Office.Interop.Excel.Worksheet oSheet;
//Tạo mới một Excel WorkBook
oExcel.Visible = true;
oExcel.DisplayAlerts = false;
oExcel.Application.SheetsInNewWorkbook = 1;
oBooks = oExcel.Workbooks;
oBook = (Microsoft.Office.Interop.Excel.Workbook)(oExcel.Workbooks.Add(Type.Missing));
oSheets = oBook.Worksheets;
oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oSheets.get_Item(1);
oSheet.Name = sheetName;
// Tạo phần đầu nếu muốn
Microsoft.Office.Interop.Excel.Range head = oSheet.get_Range("A1", "C1");
head.MergeCells = true;
head.Value2 = title;
head.Font.Bold = true;
head.Font.Name = "Tahoma";
head.Font.Size = "18";
head.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
// Tạo tiêu đề cột
Microsoft.Office.Interop.Excel.Range cl1 = oSheet.get_Range("A3", "A3");
cl1.Value2 = "Mã đơn vị";
cl1.ColumnWidth = 13.5;
Microsoft.Office.Interop.Excel.Range cl2 = oSheet.get_Range("B3", "B3");
cl2.Value2 = "Tên đơn vị";
cl2.ColumnWidth = 25.0;
Microsoft.Office.Interop.Excel.Range cl3 = oSheet.get_Range("C3", "C3");
cl3.Value2 = "Chức năng";
cl3.ColumnWidth = 40.0;
Microsoft.Office.Interop.Excel.Range rowHead = oSheet.get_Range("A3", "C3");
rowHead.Font.Bold = true;
// Kẻ viền
rowHead.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
// Thiết lập màu nền
rowHead.Interior.ColorIndex = 15;
rowHead.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
// Tạo mẳng đối tượng để lưu dữ toàn bồ dữ liệu trong DataTable,
// vì dữ liệu được được gán vào các Cell trong Excel phải thông qua object thuần.
object[,] arr = new object[dt.Rows.Count, dt.Columns.Count];
//Chuyển dữ liệu từ DataTable vào mảng đối tượng
for (int r = 0; r < dt.Rows.Count; r++)
{
DataRow dr = dt.Rows[r];
for (int c = 0; c < dt.Columns.Count; c++)
{
arr[r, c] = dr[c];
}
}
//Thiết lập vùng điền dữ liệu
int rowStart = 4;
int columnStart = 1;
int rowEnd = rowStart + dt.Rows.Count - 1;
int columnEnd = dt.Columns.Count;
// Ô bắt đầu điền dữ liệu
Microsoft.Office.Interop.Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowStart, columnStart];
// Ô kết thúc điền dữ liệu
Microsoft.Office.Interop.Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowEnd, columnEnd];
// Lấy về vùng điền dữ liệu
Microsoft.Office.Interop.Excel.Range range = oSheet.get_Range(c1, c2);
//Điền dữ liệu vào vùng đã thiết lập
range.Value2 = arr;
// Kẻ viền
range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
// Căn giữa cột STT
Microsoft.Office.Interop.Excel.Range c3 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowEnd, columnStart];
Microsoft.Office.Interop.Excel.Range c4 = oSheet.get_Range(c1, c3);
oSheet.get_Range(c3, c4).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
}
private void ExportExcel(string fileName, DataGridView myDGV)
{
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; //被点了取消
}
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(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
Microsoft.Office.Interop.Excel.Range range = null;
Microsoft.Office.Interop.Excel.Range range1 = worksheet.get_Range("B2", "W3");
range1.Select();
range1.Merge();
range1.Font.Size = 15;
range1.Borders.LineStyle = 1;
range1.Value2 = "昌 吉 州 人 民 医 院 手 术 通 知 单";
range1.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
Microsoft.Office.Interop.Excel.Range range2 = worksheet.get_Range("B4", "W4");
range2.Select();
range2.Merge();
range2.Font.Size = 11;
range2.Value2 = " 手术日期：" + dtDataTime.Text;
range2.Borders.LineStyle = 1;
Microsoft.Office.Interop.Excel.Range excelRange = worksheet.get_Range("A6", "W6");
excelRange.Select();
xlApp.ActiveWindow.FreezePanes = true;
//写入标题
//int ColCount = 0;
for (int i = 0; i < myDGV.ColumnCount; i++)
{
worksheet.Cells[5, i + 2] = myDGV.Columns[i].HeaderText;
range = xlApp.Cells[5, i + 2];
range.Font.Bold = true;
range.RowHeight = 25;
range.Interior.ColorIndex = 34;
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
range.Borders.LineStyle = 1;
if (i == 3 || i == 8 || i == 9)
{
range.ColumnWidth = 10;
}
else
{
range.EntireColumn.AutoFit();
}
}
//写入数值
for (int r = 0; r < myDGV.Rows.Count; r++)
{
for (int i = 0; i < myDGV.ColumnCount; i++)
{
worksheet.Cells[r + 6, i + 2] = myDGV.Rows[r].Cells[i].Value;
range = worksheet.Cells[r + 6, i + 2];
range.Font.Size = 9;
range.WrapText = true;
int[] a = { 1, 1, 2, 4, 5, 6, 7, 8, 9 };
foreach (int dr in a)
{
if (i == dr)
{
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
}
else
{
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
}
}
range.EntireRow.AutoFit();//行高自适应
range.Borders.LineStyle = 1;
}
System.Windows.Forms.Application.DoEvents();
}
//worksheet.Columns.EntireColumn.Width = 40;//列宽自适应
//worksheet.Rows.AutoFilter();
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
ProgressBar pbar = new ProgressBar();
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();//强行销毁
MessageBox.Show("文件： " + fileName + ".xls 保存成功", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
//●発注_発注確定
private void btnOrder_OrderEnter_Click(object sender, EventArgs e)
{
bool flg = true;
//数量見入力時のエラーメッセージ
for (int i = 0; i < dgvOrder_OrderList.Rows.Count; i++)
{
if (dgvOrder_OrderList[3, i].Value.ToString() == "0" || dgvOrder_OrderList[3, i].Value.ToString() == "")
{
MessageBox.Show("数量の未入力があります。");
flg = false;
}
}
if (flg == true)
{
//エクセルの宣言
Microsoft.Office.Interop.Excel.Application oXlsApp;
Microsoft.Office.Interop.Excel.Worksheet oSheet;
Microsoft.Office.Interop.Excel.Range[,] Syouhin = new Microsoft.Office.Interop.Excel.Range[10, 10];
Microsoft.Office.Interop.Excel.Range oxlOrderDate;
Microsoft.Office.Interop.Excel.Range oxlOrderNumber;
Microsoft.Office.Interop.Excel.Range oxlDeliverDate;
// エクセル起動
oXlsApp = new Microsoft.Office.Interop.Excel.Application();
// エクセル非表示
oXlsApp.Application.Visible = false;
oXlsApp.Application.DisplayAlerts = false;
//テンプレートを呼び出し
oXlsApp.Application.Workbooks.Add("C:/Users/Kensaku/Dropbox/OrderSeet.xltx");
// シート選択
oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oXlsApp.Worksheets[1];
// カラム幅設定
//oSheet.Columns("C").ColumnWidth = 20
((Microsoft.Office.Interop.Excel.Range)oSheet.get_Range(oSheet.Cells[11, 7], oSheet.Cells[65535, 7])).ColumnWidth = 17;
// カラムの書式設定の表示形式を文字列にする
//oSheet.Columns("C").NumberFormatLocal = "@"
((Microsoft.Office.Interop.Excel.Range)oSheet.get_Range(
oSheet.Cells[1, 3],
oSheet.Cells[65535, 3])).NumberFormatLocal = "@";
//発注日の当てはめ
oxlOrderDate = oSheet.get_Range(oSheet.Cells[3, 6], oSheet.Cells[3, 6]);
oxlOrderDate.Value2 = System.DateTime.Now.ToShortDateString();
oxlOrderNumber = oSheet.get_Range(oSheet.Cells[4, 6], oSheet.Cells[4, 6]);
oxlOrderNumber.Value2 = "000001";
oxlDeliverDate = oSheet.get_Range(oSheet.Cells[17, 2], oSheet.Cells[17, 2]);
oxlDeliverDate.Value2 = System.DateTime.Now.AddDays(7).ToShortDateString();
//セルを当てはめる
int intStart = 20;
int intDgvRowsCount = dgvOrder_OrderList.Rows.Count;
for (int i = 0; i < intDgvRowsCount; i++)
{
for (int j = 0; j < 6; j++)
{
Syouhin[i, j] = oSheet.get_Range(oSheet.Cells[intStart, j + 1], oSheet.Cells[intStart, j + 1]);
switch (j)
{
case 0:
Syouhin[i, j].Value2 = dgvOrder_OrderList[0, i].Value.ToString(); //商品ID
break;
case 1:
Syouhin[i, j].Value2 = dgvOrder_OrderList[1, i].Value.ToString(); //商品名
break;
case 2:
Syouhin[i, j].Value2 = "枚";
break;
case 3:
Syouhin[i, j].Value2 = dgvOrder_OrderList[3, i].Value.ToString(); //枚数
break;
case 4:
Syouhin[i, j].Value2 = "7000";
break;
default:
break;
}
}
intStart++;
}
//データベースにインサート
long lngOrderId = long.Parse(clsDbj.fctFillTop("select MAX(Order_OrderId) from OrderT;"));
lngOrderId++;
clsDbj.fctInsert("INSERT INTO `OrderT`(`Order_OrderId`, `Order_OrderDate`) VALUES (" + lngOrderId + ",'" + System.DateTime.Now.ToShortDateString() + "')");
for (int i = 0; i < intDgvRowsCount; i++)
{
clsDbj.fctInsert("INSERT INTO `OrderListT`(`OrderList_OrderId`, `OrderList_RowNumber`, `OrderList_ProductId`, `OrderList_Number`, `Order_ProductPrice`, `Order_DeliverDate`)"
+ " VALUES (" + lngOrderId + "," + i + 1 + "," + dgvOrder_OrderList[0, i].Value.ToString() + "," + dgvOrder_OrderList[3, i].Value.ToString() + "," + 7000 + ",'" + System.DateTime.Now.AddDays(7).ToShortDateString() + "');");
}
// エクセル表示
oXlsApp.Application.Visible = true;
}
}
private void ExportExcel(object obj)
{
System.Windows.Forms.SaveFileDialog opf = new System.Windows.Forms.SaveFileDialog();
opf.FileName = DateTime.Now.ToString("yyyyMMddHHmmss");
opf.Filter = "*.xls|*.xls|所有文件(*.*)|*.*";
if (opf.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
string filepath = opf.FileName;
object missing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
//创建一个Application对象并使其不可见
app.Visible = false;
//创建一个WorkBook对象
Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Add(missing);
Microsoft.Office.Interop.Excel.Worksheet workSheet = null;
Microsoft.Office.Interop.Excel.Range range = null;
workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets.Add(
Type.Missing,
workBook.ActiveSheet,
Type.Missing,
Type.Missing);
int rowCount = 1; //DataTable行数+GirdHead
int colCount = 1; //DataTable列数
//利用二维数组批量写入
string[,] arr = new string[rowCount, colCount];
for (int j = 0; j < rowCount; j++)
{
for (int k = 0; k < colCount; k++)
{
if (j == 0)
{
arr[j, k] = "测试速度:";
}
else
{
arr[j, k] = statusText;
}
}
}
range = (Microsoft.Office.Interop.Excel.Range)workSheet.Cells[1, 1]; //写入Exel的坐标
range = range.get_Resize(rowCount, colCount);
range.Value2 = arr;
workBook.SaveAs(filepath, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
if (workBook.Saved)
{
workBook.Close(null, null, null);
app.Workbooks.Close();
app.Quit();
}
if (range != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
range = null;
}
if (workSheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
workSheet = null;
}
if (workBook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
workBook = null;
}
if (app != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
}
GC.Collect();//强制代码垃圾回收
}
}
private void button2_Click(object sender, EventArgs e)
{
DataSet ds = new DataSet();
ds.ReadXml(Application.StartupPath + "\\ExcelBindingXml.xml");
Microsoft.Office.Interop.Excel.Application m_objExcel = null;
Microsoft.Office.Interop.Excel._Workbook m_objBook = null;
Microsoft.Office.Interop.Excel.Sheets m_objSheets = null;
Microsoft.Office.Interop.Excel._Worksheet m_objSheet = null;
Microsoft.Office.Interop.Excel.Range m_objRange = null;
object m_objOpt = System.Reflection.Missing.Value;
try
{
m_objExcel = new Microsoft.Office.Interop.Excel.Application();
m_objBook = m_objExcel.Workbooks.Open(Application.StartupPath + "\\ExcelTemplate.xlsx", m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objSheets = (Microsoft.Office.Interop.Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Microsoft.Office.Interop.Excel._Worksheet)(m_objSheets.get_Item(1));
int maxRow = m_objSheet.UsedRange.Rows.Count;
int maxCol = m_objSheet.UsedRange.Columns.Count;
int TitleRow = 0;
for (int excelrow = 1; excelrow <= maxRow; excelrow++)
{
for (int col = 0; col < maxCol; col++)
{
string excelColName = ExcelColNumberToColText(col);
m_objRange = m_objSheet.get_Range(excelColName + excelrow.ToString(), m_objOpt);
m_objRange.Text.ToString().Contains("$");
TitleRow = excelrow;
break;
}
}
for (int excelrow = 1; excelrow <= ds.Tables[0].Rows.Count; excelrow++)
{
DataRow dr = ds.Tables[0].Rows[excelrow - 1];
for (int col = 0; col < maxCol; col++)
{
string excelColName = ExcelColNumberToColText(col);
m_objRange = m_objSheet.get_Range(excelColName + TitleRow.ToString(), m_objOpt);
//Microsoft.Office.Interop.Excel.Range item_objRange = m_objSheet.get_Range(excelColName + (maxRow + excelrow).ToString(), m_objOpt);
if (m_objRange.Text.ToString().Replace("$", "") == ds.Tables[0].Columns[col].ColumnName)
{
m_objSheet.Cells[maxRow + excelrow, col + 1].value = dr[col].ToString();
m_objSheet.Cells[maxRow + excelrow, col + 1].Style = m_objRange.Style;
Console.WriteLine(m_objSheet.Cells[maxRow + excelrow, col + 1].value + ":" + dr[col].ToString());
//item_objRange.Value2 = dr[col].ToString();
}
}
}
Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)m_objSheet.Rows[TitleRow, m_objOpt];
range.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);
m_objExcel.DisplayAlerts = false;
m_objBook.SaveAs(Application.StartupPath + "\\ExcelBindingXml.xlsx", m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
m_objBook.Close(m_objOpt, m_objOpt, m_objOpt);
m_objExcel.Workbooks.Close();
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
m_objBook = null;
m_objExcel = null;
GC.Collect();
}
}
public void Export(DataTable dt, string sheetName, string title)
{
//Tạo các đối tượng Excel
//Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application();
object oBooks = oExcel.Workbooks;
Microsoft.Office.Interop.Excel.Sheets oSheets;
Microsoft.Office.Interop.Excel.Workbook oBook;
Microsoft.Office.Interop.Excel.Worksheet oSheet;
//Tạo mới một Excel WorkBook
//System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
//System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
//oExcel.Workbooks.Add();
//System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
oExcel.Visible = true;
oExcel.DisplayAlerts = false;
oExcel.Application.SheetsInNewWorkbook = 1;
oBook = (Microsoft.Office.Interop.Excel.Workbook)(oExcel.Workbooks.Add(Type.Missing));
oSheets = oBook.Worksheets;
oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oSheets.get_Item(1);
oSheet.Name = sheetName;
//// Tạo phần đầu nếu muốn
Microsoft.Office.Interop.Excel.Range head = oSheet.get_Range("A1", "H1");
head.MergeCells = true;
head.Value2 = title;
head.Font.Bold = true;
head.Font.Name = "Tahoma";
head.Font.Size = "18";
head.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
// Tạo tiêu đề cột
Microsoft.Office.Interop.Excel.Range cl1 = oSheet.get_Range("A3", "A3");
cl1.Value2 = "Mã Sinh Viên";
cl1.ColumnWidth = 12;
Microsoft.Office.Interop.Excel.Range cl2 = oSheet.get_Range("B3", "B3");
cl2.Value2 = "Họ tên";
cl2.ColumnWidth = 20.87;
Microsoft.Office.Interop.Excel.Range cl3 = oSheet.get_Range("C3", "C3");
cl3.Value2 = "Quê Quán";
cl3.ColumnWidth = 12.0;
Microsoft.Office.Interop.Excel.Range cl4 = oSheet.get_Range("D3", "D3");
cl4.Value2 = "Ngày Sinh";
cl4.ColumnWidth = 10;
Microsoft.Office.Interop.Excel.Range cl5 = oSheet.get_Range("E3", "E3");
cl5.Value2 = "Nơi Sinh";
cl5.ColumnWidth = 8.72;
Microsoft.Office.Interop.Excel.Range cl6 = oSheet.get_Range("F3", "F3");
cl6.Value2 = "Giới Tính";
cl6.ColumnWidth = 8.57;
Microsoft.Office.Interop.Excel.Range cl7 = oSheet.get_Range("G3", "G3");
cl7.Value2 = "Hình";
cl7.ColumnWidth = 4.43;
Microsoft.Office.Interop.Excel.Range cl8 = oSheet.get_Range("H3", "H3");
cl8.Value2 = "Mã Lớp";
cl8.ColumnWidth = 10;
Microsoft.Office.Interop.Excel.Range cl9 = oSheet.get_Range("I3", "I3");
cl9.Value2 = "Mã Ngành";
cl9.ColumnWidth = 10;
Microsoft.Office.Interop.Excel.Range rowHead = oSheet.get_Range("A3", "I3");
rowHead.Font.Bold = true;
// Kẻ viền
rowHead.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
// Thiết lập màu nền
rowHead.Interior.ColorIndex = 15;
rowHead.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
// Tạo mẳng đối tượng để lưu dữ toàn bồ dữ liệu trong DataTable,
// vì dữ liệu được được gán vào các Cell trong Excel phải thông qua object thuần.
object[,] arr = new object[dt.Rows.Count, dt.Columns.Count];
//Chuyển dữ liệu từ DataTable vào mảng đối tượng
for (int r = 0; r < dt.Rows.Count; r++)
{
DataRow dr = dt.Rows[r];
for (int c = 0; c < dt.Columns.Count; c++)
{
arr[r, c] = dr[c];
}
}
//Thiết lập vùng điền dữ liệu
int rowStart = 4;
int columnStart = 1;
int rowEnd = rowStart + dt.Rows.Count - 1;
int columnEnd = dt.Columns.Count;
// Ô bắt đầu điền dữ liệu
Microsoft.Office.Interop.Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowStart, columnStart];
// Ô kết thúc điền dữ liệu
Microsoft.Office.Interop.Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowEnd, columnEnd];
// Lấy về vùng điền dữ liệu
Microsoft.Office.Interop.Excel.Range range = oSheet.get_Range(c1, c2);
//Điền dữ liệu vào vùng đã thiết lập
range.Value2 = arr;
// Kẻ viền
range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
// Căn giữa cột STT
Microsoft.Office.Interop.Excel.Range c3 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowEnd, columnStart];
Microsoft.Office.Interop.Excel.Range c4 = oSheet.get_Range(c1, c3);
oSheet.get_Range(c3, c4).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
}
/// <summary>
/// 如果字段的类型是结构体，则需要分开处理
/// </summary>
/// <param name="dt"></param>
/// <param name="rowsOffset"></param>
/// <param name="colsOffset"></param>
private void ParseStructToExcel(DataTable dt, ref int rowsOffset, int colsOffset)
{
foreach (DataRow row in dt.Rows)
{
if (row["DataType"].ToString() == "STRUCTURE")
{
String structurName = row["DataTypeName"].ToString();
String Documentation = row[FuncFieldText.DOCUMENTATION].ToString();
if (_parsedStructure.Contains(structurName))
{
return;
}
DataTable dt2 = m_function.FunctionMeta.StructureDetail[structurName];
if (dt2 != null)
{
if (dt2.Rows.Count > 0)
{
_parsedStructure.Add(structurName);
range = (Microsoft.Office.Interop.Excel.Range)ws.get_Range(ws.Cells[rowsOffset - 4, colsOffset + 1], ws.Cells[rowsOffset - 4, colsOffset + 5]);
range.Select();
range.Merge(false);
range = (Microsoft.Office.Interop.Excel.Range)ws.get_Range(ws.Cells[rowsOffset - 3, colsOffset + 1], ws.Cells[rowsOffset - 3, colsOffset + 5]);
range.Select();
range.Merge(false);
range = (Microsoft.Office.Interop.Excel.Range)ws.get_Range(ws.Cells[rowsOffset - 2, colsOffset + 1], ws.Cells[rowsOffset - 2, colsOffset + 5]);
range.Select();
range.Merge(false);
ws.Cells.set_Item(rowsOffset - 4, colsOffset - 1, "STRUCTURE");
ws.Cells.set_Item(rowsOffset - 4, colsOffset, "结构");
ws.Cells.set_Item(rowsOffset - 3, colsOffset, "名称");
ws.Cells.set_Item(rowsOffset - 2, colsOffset, "备注");
ws.Cells.set_Item(rowsOffset - 4, colsOffset + 1, structurName);
ws.Cells.set_Item(rowsOffset - 3, colsOffset + 1, Documentation);
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 0, "字段编号");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 1, "字段");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 2, "数据类型");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 3, "长度");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 4, "小数位");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 5, "短文本");
ParseTypeDefinitionToExcel(dt2, rowsOffset, colsOffset);
rowsOffset += dt2.Rows.Count + 7;
}
}
}
else if (row["DataType"].ToString() == "TABLE")
{
String tableName = row["DataTypeName"].ToString();
String Documentation = row[FuncFieldText.DOCUMENTATION].ToString();
if (_parsedTable.Contains(tableName))
{
return;
}
DataTable dt2 = m_function.FunctionMeta.StructureDetail[tableName];
if (dt2 != null)
{
if (dt2.Rows.Count > 0)
{
_parsedTable.Add(tableName);
range = (Microsoft.Office.Interop.Excel.Range)ws.get_Range(ws.Cells[rowsOffset - 4, colsOffset + 1], ws.Cells[rowsOffset - 4, colsOffset + 5]);
range.Select();
range.Merge(false);
range = (Microsoft.Office.Interop.Excel.Range)ws.get_Range(ws.Cells[rowsOffset - 3, colsOffset + 1], ws.Cells[rowsOffset - 3, colsOffset + 5]);
range.Select();
range.Merge(false);
range = (Microsoft.Office.Interop.Excel.Range)ws.get_Range(ws.Cells[rowsOffset - 2, colsOffset + 1], ws.Cells[rowsOffset - 2, colsOffset + 5]);
range.Select();
range.Merge(false);
ws.Cells.set_Item(rowsOffset - 4, colsOffset - 1, "TABLE");
ws.Cells.set_Item(rowsOffset - 4, colsOffset, "表");
ws.Cells.set_Item(rowsOffset - 3, colsOffset, "名称");
ws.Cells.set_Item(rowsOffset - 2, colsOffset, "备注");
ws.Cells.set_Item(rowsOffset - 4, colsOffset + 1, tableName);
ws.Cells.set_Item(rowsOffset - 3, colsOffset + 1, Documentation);
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 0, "字段编号");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 1, "字段");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 2, "数据类型");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 3, "长度");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 4, "小数位");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 5, "短文本");
ParseTypeDefinitionToExcel(dt2, rowsOffset, colsOffset);
rowsOffset += dt2.Rows.Count + 7;
}
}
}
}
}
public static void ExportExcel2(string title, string dw, GridControl gc)
{
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
string fname = "";
saveFileDialog1.Filter = "Microsoft Excel (*.xls)|*.xls";
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
try
{
fname = saveFileDialog1.FileName;
gc.ExportToExcelOld(fname);
FarPoint.Win.Spread.FpSpread fps = new FarPoint.Win.Spread.FpSpread();
fps.OpenExcel(fname);
SheetView sv = fps.Sheets[0];
int ColumnCount = sv.NonEmptyColumnCount;
int RowCount = sv.NonEmptyRowCount;
//sv.ColumnCount = ColumnCount;
//sv.RowCount = RowCount;
sv.AddRows(0, 2);
sv.Cells[0, 0].Text = title;
sv.Cells[0, 0].Font = new System.Drawing.Font("宋体", 16F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134)));
sv.Cells[0, 0].HorizontalAlignment = CellHorizontalAlignment.Center;
sv.Cells[0, 0].VerticalAlignment = CellVerticalAlignment.Center;
sv.Cells[0, 0].Row.Height = 50;
sv.Cells[0, 0].ColumnSpan = ColumnCount;
sv.Cells[1, 0].Text = dw;
sv.Cells[1, 0].Font = new System.Drawing.Font("宋体", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134)));
sv.Cells[1, 0].HorizontalAlignment = CellHorizontalAlignment.Right;
sv.Cells[1, 0].VerticalAlignment = CellVerticalAlignment.Center;
sv.Cells[1, 0].ColumnSpan = ColumnCount;
for (int i = 0; i < ColumnCount; i++)
{
sv.Cells[2, i].Row.Height = 30;
sv.Cells[2, i].Font = new System.Drawing.Font("宋体", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134)));
if (i == 0)
{
sv.Columns[i].Width = 30;
}
else if (i == 1)
{
sv.Columns[i].Width = 120;
}
else
{
sv.Columns[i].Width = 90;
}
}
for (int i = 3; i < RowCount; i++)
{
sv.Rows[i].Height = 30;
if (sv.Cells[i, 1].Text == "已立项目" || sv.Cells[i, 1].Text == "规划项目")
{
sv.Rows[i].Height = 90;
}
}
sv.AddRows(RowCount + 2, 2);
sv.Cells[RowCount + 2, 0].Text = "建表时间:" + DateTime.Now.Year + "-" + DateTime.Now.Month + "-" + DateTime.Now.Day;
sv.Cells[RowCount + 2, 0].Font = new System.Drawing.Font("宋体", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134)));
sv.Cells[RowCount + 2, 0].HorizontalAlignment = CellHorizontalAlignment.Right;
sv.Cells[RowCount + 2, 0].VerticalAlignment = CellVerticalAlignment.Center;
sv.Cells[RowCount + 2, 0].ColumnSpan = ColumnCount;
fps.SaveExcel(fname);
// 定义要使用的Excel 组件接口
// 定义Application 对象,此对象表示整个Excel 程序
Microsoft.Office.Interop.Excel.Application excelApp = null;
// 定义Workbook对象,此对象代表工作薄
Microsoft.Office.Interop.Excel.Workbook workBook;
// 定义Worksheet 对象,此对象表示Execel 中的一张工作表
Microsoft.Office.Interop.Excel.Worksheet ws = null;
Microsoft.Office.Interop.Excel.Range range = null;
excelApp = new Microsoft.Office.Interop.Excel.Application();
workBook = excelApp.Workbooks.Open(fname, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
for (int i = 1; i <= workBook.Worksheets.Count; i++)
{
ws = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[i];
//取消保护工作表
ws.Unprotect(Missing.Value);
//有数据的行数
int row = ws.UsedRange.Rows.Count;
//有数据的列数
int col = ws.UsedRange.Columns.Count;
//创建一个区域
range = ws.get_Range(ws.Cells[1, 1], ws.Cells[row, col]);
//设区域内的单元格自动换行
range.Select();
range.NumberFormatLocal = "G/通用格式";
//保护工作表
ws.Protect(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
//保存工作簿
workBook.Save();
//关闭工作簿
excelApp.Workbooks.Close();
if (MsgBox.ShowYesNo("导出成功，是否打开该文档？") != DialogResult.Yes)
{
return;
}
System.Diagnostics.Process.Start(fname);
}
catch
{
MsgBox.Show("无法保存" + fname + "。请用其他文件名保存文件，或将文件存至其他位置。");
return;
}
}
}
public static bool ExportDataToExcel(System.Data.DataTable srcDataTable, string excelFilePath)
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
object missing = System.Reflection.Missing.Value;
//导出到execl
try
{
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的电脑未安装Excel!");
return(false);
}
Microsoft.Office.Interop.Excel.Workbooks xlBooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook xlBook = xlBooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets[1];
//让后台执行设置为不可见，为true的话会看到打开一个Excel，然后数据在往里写
xlApp.Visible = false;
object[,] objData = new object[srcDataTable.Rows.Count + 1, srcDataTable.Columns.Count];
//首先将数据写入到一个二维数组中
for (int i = 0; i < srcDataTable.Columns.Count; i++)
{
objData[0, i] = srcDataTable.Columns[i].ColumnName;
}
if (srcDataTable.Rows.Count > 0)
{
for (int i = 0; i < srcDataTable.Rows.Count; i++)
{
for (int j = 0; j < srcDataTable.Columns.Count; j++)
{
objData[i + 1, j] = srcDataTable.Rows[i][j];
}
}
}
string startCol = "A";
int iCnt = (srcDataTable.Columns.Count / 26);
string endColSignal = (iCnt == 0 ? "" : ((char)('A' + (iCnt - 1))).ToString());
string endCol = endColSignal + ((char)('A' + srcDataTable.Columns.Count - iCnt * 26 - 1)).ToString();
Microsoft.Office.Interop.Excel.Range range = xlSheet.get_Range(startCol + "1", endCol + (srcDataTable.Rows.Count - iCnt * 26 + 1).ToString());
range.Value = objData; //给Exccel中的Range整体赋值
range.EntireColumn.AutoFit(); //设定Excel列宽度自适应
xlSheet.get_Range(startCol + "1", endCol + "1").Font.Bold = 1; //Excel文件列名 字体设定为Bold
//设置禁止弹出保存和覆盖的询问提示框
xlApp.DisplayAlerts = false;
xlApp.AlertBeforeOverwriting = false;
//if (File.Exists(excelFilePath))
// File.Delete(excelFilePath);
if (xlSheet != null)
{
xlSheet.SaveAs(excelFilePath, missing, missing, missing, missing, missing, missing, missing, missing, missing);
xlApp.Quit();
//KillProcess(xlApp);
//SystemUnit.KillProcess(xlApp);
}
}
catch (Exception ex)
{
Log.WriteLog("SystemUnit", "ERROR", "【ExportDataToExcel】 " + ex.Message);
xlApp.Quit();
//KillProcess(xlApp);
//throw ex;
return(false);
}
return(true);
}
/// <summary>
/// 导出文本
/// </summary>
private void ExoprtText()
{
Microsoft.Office.Interop.Excel.Application xApp = null;
Microsoft.Office.Interop.Excel.Workbook xBook = null;
Microsoft.Office.Interop.Excel.Worksheet xSheet = null;
// 设定保存的文件名
string fileName = @"E:\My\Hanhua\testFile\bioCv\BioCvTextNgc.xls";
//string fileName = @"D:\game\iso\wii\生化危机维罗妮卡汉化\Bio0Text_" + this.exportName + ".xls";
// 先删除原来的文件
File.Delete(fileName);
// 显示进度条
this.ResetProcessBar(this.fileList.Items.Count);
try
{
// 创建Application对象
xApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
//xApp.Visible = true;
// 追加一个WorkBook
xBook = xApp.Workbooks.Add(Missing.Value);
for (int j = 0; j < this.fileList.Items.Count; j++)
{
// 追加一个Sheet
FilePosInfo filePosInfo = this.textFiles[j];
// 更新当前文本
this.fileList.SelectedIndex = j;
// 取得日文、中文文本
string jpText = this.txtJp.Text;
string cnText = this.txtCn.Text;
string sheetName = Util.GetShortFileName(filePosInfo.File);
int sameNameCount = 0;
for (int i = 0; i < j; i++)
{
if (Util.GetShortFileName(this.textFiles[i].File).IndexOf(sheetName) >= 0)
{
sameNameCount++;
}
}
xSheet = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
xSheet.Name = sheetName + (sameNameCount > 0 ? "_" + sameNameCount.ToString().PadLeft(2, '0') : string.Empty);
// 将每行文本保存到Sheet中
string[] jpTexts = jpText.Split('\n');
string[] cnTexts = cnText.Split('\n');
for (int i = 0; i < jpTexts.Length; i++)
{
// 写入日文文本
Microsoft.Office.Interop.Excel.Range rngJp = xSheet.get_Range("A" + (i + 1), Missing.Value);
rngJp.Value2 = jpTexts[i];
}
for (int i = 0; i < cnTexts.Length; i++)
{
// 写入中文文本
Microsoft.Office.Interop.Excel.Range rngCn = xSheet.get_Range("G" + (i + 1), Missing.Value);
rngCn.Value2 = cnTexts[i];
}
// 更新进度条
this.ProcessBarStep();
}
// 保存
xSheet.SaveAs(
fileName,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
// 隐藏进度条
this.CloseProcessBar();
// 显示保存完成信息
MessageBox.Show("导出完成！");
}
catch (Exception me)
{
MessageBox.Show(this.baseFile + "\n" + me.Message);
}
finally
{
// 隐藏进度条
this.CloseProcessBar();
// 清空各种对象
xSheet = null;
xBook = null;
if (xApp != null)
{
xApp.Quit();
xApp = null;
}
}
}
//输出文件到xlsx
/// <summary>
/// 导出文件。该方法使用的数据源为DataTable,导出Excel文件。
/// </summary>
/// <param name="dt"></param>
public static void ExportToExcel(DataTable dt, string path)
{
Microsoft.Office.Interop.Excel.Application xlxsApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlxsApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
Microsoft.Office.Interop.Excel.Range range = null;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
string fileName = path;
//写入标题
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 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();//自动调整行高
}
//写入内容
for (int r = 0; r < dt.DefaultView.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = dt.DefaultView[r][i];
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + 2, i + 1];
range.Font.Size = 9; //字体大小
//加边框
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();
//进度条
exportThread(r);
}
//满格显示
exportThread(dt.DefaultView.Count);
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 (xlxsApp != null)
{
xlxsApp.Workbooks.Close();
xlxsApp.Quit();
int generation = System.GC.GetGeneration(xlxsApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlxsApp);
xlxsApp = 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("导出成功!");
}
protected override void Execute(NativeActivityContext context)
{
base.Execute(context);
var cell = Cell.Get(context);
Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(cell);
Formula.Set(context, range.Formula);
Range.Set(context, range);
object value = range.Value;
if (range.Value2 is TResult val2)
{
context.SetValue(Result, val2);
}
else if (range.Value is TResult val)
{
context.SetValue(Result, val);
}
else
{
if (value != null && typeof(TResult) == typeof(bool))
{
if (value is double d)
{
value = (d > 0);
}
if (value is int i)
{
value = (i > 0);
}
if (value is string s)
{
value = (s == "1" || s.ToLower() == "true");
}
}
if (value != null && value.GetType() == typeof(double) && typeof(TResult) == typeof(int))
{
if (value != null)
{
value = int.Parse(value.ToString());
}
if (value == null)
{
value = int.Parse("0");
}
}
if (value != null && value.GetType() == typeof(DateTime) && typeof(TResult) == typeof(string))
{
value = value.ToString();
}
if (value != null && value.GetType() == typeof(int) && typeof(TResult) == typeof(string))
{
value = value.ToString();
}
if (value != null && value.GetType() == typeof(double) && typeof(TResult) == typeof(string))
{
value = value.ToString();
}
if (value != null)
{
context.SetValue(Result, (TResult)value);
}
if (value == null)
{
context.SetValue(Result, default(TResult));
}
}
var sheetPassword = SheetPassword.Get(context);
if (string.IsNullOrEmpty(sheetPassword))
{
sheetPassword = null;
}
if (!string.IsNullOrEmpty(sheetPassword) && worksheet != null)
{
worksheet.Protect(sheetPassword);
}
}
private void fill_excel(Microsoft.Office.Interop.Excel._Worksheet my)
{
int ind = 0;
int i插入行数 = 0;
my.Cells[3, 1].Value = "生产指令编号：" + CODE;
my.Cells[3, 4].Value = "产品代码：" + dtOuter.Rows[0]["产品代码"].ToString();
my.Cells[3, 6].Value = "产品批号：" + dtOuter.Rows[0]["产品批号"].ToString();
DateTime.Parse(DateTime.Now.ToString("yyyy年MM月dd日"));
if (dtOuter.Rows[0]["生产班次"].ToString().Equals("白班"))
{
my.Cells[3, 7].Value = String.Format("生产日期：{0}\n生产班次： 白班☑ 夜班□", Convert.ToDateTime(dtOuter.Rows[0]["生产日期"]).ToString("yyyy年MM月dd日"));
}
else
{
my.Cells[3, 7].Value = String.Format("生产日期：{0}\n生产班次： 白班□ 夜班☑", Convert.ToDateTime(dtOuter.Rows[0]["生产日期"]).ToString("yyyy年MM月dd日"));
}
//插入新行
if (dtInner.Rows.Count > 14)
{
i插入行数 = dtInner.Rows.Count - 14;
for (int i = 0; i < i插入行数; i++)
{
//在第6行插入
Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)my.Rows[18 + i, Type.Missing];
range.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown,
Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
}
ind = i插入行数;
}
//写内表数据
for (int i = 0; i < dtInner.Rows.Count; i++)
{
my.Cells[5 + i, 1].Value = i + 1;
my.Cells[5 + i, 2].Value = dtInner.Rows[i]["清场项目"].ToString();
my.Cells[5 + i, 3].Value = dtInner.Rows[i]["清场要点"].ToString();
if (dtInner.Rows[0]["清洁操作"].ToString() == "完成")
{
my.Cells[5 + i, 6].Value = "完成☑ 不适用□";
}
else if (dtOuter.Rows[0]["清洁操作"].ToString() == "不适用")
{
my.Cells[5 + i, 6].Value = "完成□ 不适用☑";
}
else
{
my.Cells[5 + i, 6].Value = "完成□ 不适用□";
}
// my.Cells[5 + i, 6].Value = dtInner.Rows[i]["清洁操作"].ToString();
}
//my.Cells[5, 7].Value = dtOuter.Rows[0]["检查结果"].ToString();
if (dtOuter.Rows[0]["检查结果"].ToString() == "合格")
{
my.Cells[5, 7].Value = "合格☑\n不合格□";
}
else if (dtOuter.Rows[0]["检查结果"].ToString() == "不合格")
{
my.Cells[5, 7].Value = "合格□\n不合格☑";
}
else
{
my.Cells[5, 7].Value = "合格□\n不合格□";
}
my.Cells[5, 8].Value = dtOuter.Rows[0]["操作员"].ToString();
my.Cells[5, 9].Value = dtOuter.Rows[0]["审核员"].ToString();
my.Cells[19 + ind, 1].Value = "备注：" + dtOuter.Rows[0]["备注"].ToString();
}
private void ExportExcelWithInsertTemplate(string filename, string ext)
{
string templateFilename = string.Format("{0}\\{1}\\{2}.{3}", Application.StartupPath, UiUtility.ExcelTemplatePath, this.TPL_FILE_NAME, ext);
try
{
//declare for using Ms.Excel Object
Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application();
ObjExcel.DisplayAlerts = false;
//for template excel
Microsoft.Office.Interop.Excel.Workbooks objTempbooks = ObjExcel.Workbooks;
//for current excel
Microsoft.Office.Interop.Excel.Workbooks objWorkbooks = ObjExcel.Workbooks;
//-----------------------------------------------------------------------------------------------------------------------------
Microsoft.Office.Interop.Excel.Workbook book = objWorkbooks.Open(filename, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Microsoft.Office.Interop.Excel.Sheets sheets = book.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets[this.SheetName];
workSheet.Select(Type.Missing);
//Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)workSheet.UsedRange;
Microsoft.Office.Interop.Excel.Range insertRange = workSheet.get_Range(UiUtility.ExcelHeaderRange, Type.Missing);
insertRange.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
//insert last row
//Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)workSheet.UsedRange;
//int lastRow = range.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, System.Type.Missing).Row;
//string strRange = string.Format("A{0}", lastRow + 1);
//Microsoft.Office.Interop.Excel.Range insertLastRange = workSheet.get_Range(strRange, Type.Missing);
//insertLastRange.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromRightOrBelow);
//-----------------------------------------------------------------------------------------------------------------------------
Microsoft.Office.Interop.Excel.Workbook tmpBook = objTempbooks.Open(templateFilename, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Microsoft.Office.Interop.Excel.Sheets tmpSheets = tmpBook.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet tmpWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)tmpSheets[this.SheetName];
tmpWorkSheet.Select(Type.Missing);
Microsoft.Office.Interop.Excel.Range copyRange = tmpWorkSheet.get_Range(UiUtility.ExcelHeaderRange, Type.Missing);
copyRange.EntireRow.Copy(Type.Missing);
Microsoft.Office.Interop.Excel.Range pasteRange = workSheet.get_Range(UiUtility.ExcelHeaderRange, Type.Missing);
pasteRange.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteAll, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
//-----------------------------------------------------------------------------------------------------------------------------
//Microsoft.Office.Interop.Excel.Range copyLastRange = tmpWorkSheet.get_Range("A11", Type.Missing);
//copyLastRange.EntireRow.Copy(Type.Missing);
//Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)workSheet.UsedRange;
//int lastRow = range.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, System.Type.Missing).Row;
//string strRange = string.Format("A{0}", lastRow + 1);
//Microsoft.Office.Interop.Excel.Range insertLastRange = workSheet.get_Range(strRange, Type.Missing);
//insertLastRange.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteAllUsingSourceTheme, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
//-------------------------------------------------------------------------------------------------------------------------------------
book.Application.CutCopyMode = Microsoft.Office.Interop.Excel.XlCutCopyMode.xlCopy;
//book.CheckCompatibility = false;
tmpBook.Close(false, Type.Missing, Type.Missing);
if (this._ShippingOrder_Hdr != null)
{
//Set Shipping Order Information.
//SO_NO
Microsoft.Office.Interop.Excel.Range sonoRange = workSheet.get_Range("K2", Type.Missing);
sonoRange.set_Value(System.Type.Missing, this._ShippingOrder_Hdr.SO_NO);
//SO_DATE
Microsoft.Office.Interop.Excel.Range sodateRange = workSheet.get_Range("K3", Type.Missing);
if (this._ShippingOrder_Hdr.SO_DATE.HasValue)
{
sodateRange.set_Value(System.Type.Missing, string.Format("{0:dd-MM-yyyy}", this._ShippingOrder_Hdr.SO_DATE.Value));
}
else
{
sodateRange.set_Value(System.Type.Missing, string.Empty);
}
//Customer
Microsoft.Office.Interop.Excel.Range custRange = workSheet.get_Range("B4", Type.Missing);
custRange.set_Value(System.Type.Missing, string.Format("{0} - {1}", this._ShippingOrder_Hdr.PARTY_ID, this._ShippingOrder_Hdr.PARTY_NAME));
//PO_REF_NO
Microsoft.Office.Interop.Excel.Range porefRange = workSheet.get_Range("B5", Type.Missing);
porefRange.set_Value(System.Type.Missing, this._ShippingOrder_Hdr.REF_NO);
//PO_REF_DATE
Microsoft.Office.Interop.Excel.Range podateRange = workSheet.get_Range("G5", Type.Missing);
if (this._ShippingOrder_Hdr.REF_DATE.HasValue)
{
podateRange.set_Value(System.Type.Missing, string.Format("{0:dd-MM-yyyy}", this._ShippingOrder_Hdr.REF_DATE.Value));
}
else
{
podateRange.set_Value(System.Type.Missing, string.Empty);
}
//ETD
Microsoft.Office.Interop.Excel.Range etdRange = workSheet.get_Range("K5", Type.Missing);
if (this._ShippingOrder_Hdr.ETA.HasValue)
{
etdRange.set_Value(System.Type.Missing, string.Format("{0:dd-MM-yyyy HH:mm}", this._ShippingOrder_Hdr.ETA.Value));
}
else
{
etdRange.set_Value(System.Type.Missing, string.Empty);
}
//REMARK
Microsoft.Office.Interop.Excel.Range remarkRange = workSheet.get_Range("B6", Type.Missing);
remarkRange.set_Value(System.Type.Missing, this._ShippingOrder_Hdr.REMARK);
var focusRange = workSheet.get_Range("A1", "A1").Select();
book.Save();
book.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges, Type.Missing, Type.Missing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(focusRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sonoRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sodateRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(custRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(porefRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(podateRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(etdRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(remarkRange);
}
else
{
var focusRange = workSheet.get_Range("A1", "A1").Select();
book.Save();
book.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges, Type.Missing, Type.Missing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(focusRange);
}
//-------------------------------------------------------------------------------------
//System.Runtime.InteropServices.Marshal.ReleaseComObject(insertLastRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(copyRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(tmpSheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(tmpBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(insertRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
ObjExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(ObjExcel);
}
catch (Exception ex)
{
//
}
}
/// <summary>
/// 处理固定的4个参数表
/// </summary>
/// <param name="dt"></param>
/// <param name="name"></param>
/// <param name="rowsOffset"></param>
/// <param name="colsOffset"></param>
private void ParseParameterList(DataTable dt, String name, int rowsOffset, int colsOffset)
{
range = (Microsoft.Office.Interop.Excel.Range)ws.get_Range(ws.Cells[rowsOffset - 2, colsOffset + 1], ws.Cells[rowsOffset - 2, colsOffset + 8]);
range.Select();
range.Merge(false);
ws.Cells.set_Item(rowsOffset - 2, colsOffset - 1, "PARAMETERLIST");
ws.Cells.set_Item(rowsOffset - 2, colsOffset, name);
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 0, "字段编号");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 1, "字段");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 2, "数据类型");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 3, "类型名称");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 4, "长度");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 5, "小数位");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 6, "默认值");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 7, "必输");
ws.Cells.set_Item(rowsOffset - 1, colsOffset + 8, "短文本");
if (dt.Rows.Count > 0)
{
ParseParameterlistToExcel(dt, rowsOffset, colsOffset);
}
}
private void btnOK_Click(object sender, EventArgs e)
{
helperClass.log.Info("Calendars button OK is clicked");
helperClass.origin = false;
if (date1 == null & date2 == null)
{
DateTime dateValue = dateTimePicker1.Value;
date1 = dateValue.ToString("yyyy-MM-dd");
DateTime dateValue2 = dateTimePicker2.Value;
date2 = dateValue2.ToString("yyyy-MM-dd");
}
if (selectedCountryLstBx.Items.Count != 0 & selectedIndicatorLstBx.Items.Count == 0)
{
List <string> isoValues = new List <string>();
foreach (string item in selectedCountryLstBx.Items)
{
if (helperClass.myCountrysDict.ContainsKey(item))
{
isoValues.Add(helperClass.myCountrysDict[item]);
}
else
{
isoValues.Add(item.ToString());
}
}
selectedIsoCntry = String.Join(",", isoValues);
if (selectedIsoCntry.Length > 255)
{
MessageBox.Show("You selected too many countries. Please remove some of them.");
return;
}
}
else if (selectedCountryLstBx.Items.Count == 0 & selectedIndicatorLstBx.Items.Count != 0)
{
List <string> values = new List <string>();
foreach (string item in selectedIndicatorLstBx.Items)
{
values.Add(item.ToString());
}
selectedIndic = String.Join(",", values);
if (selectedIndic.Length > 255)
{
MessageBox.Show("You selected too many indicators. Please remove some of them.");
return;
}
}
else if (selectedCountryLstBx.Items.Count != 0 & selectedIndicatorLstBx.Items.Count != 0)
{
List <string> isoValues = new List <string>();
foreach (string item in selectedCountryLstBx.Items)
{
if (helperClass.myCountrysDict.ContainsKey(item))
{
isoValues.Add(helperClass.myCountrysDict[item]);
}
else
{
isoValues.Add(item.ToString());
}
}
selectedIsoCntry = String.Join(",", isoValues);
List <string> values2 = new List <string>();
foreach (string item in selectedIndicatorLstBx.Items)
{
values2.Add(item.ToString());
}
selectedIndic = String.Join(",", values2);
if (selectedIsoCntry.Length > 255)
{
MessageBox.Show("You selected too many countries. Please remove some of them.");
return;
}
if (selectedIndic.Length > 255)
{
MessageBox.Show("You selected too many indicators. Please remove some of them.");
return;
}
}
List <string> columns = new List <string>();
foreach (string item in columnsListBox.CheckedItems)
{
columns.Add(item.ToString());
}
string newColumns = String.Join(",", columns);
helperClass.runFormula = "RunAutomatically = 1";
Microsoft.Office.Interop.Excel.Range dateCell = helperClass.CellAddress(activeCellPositionBox.Text);
string clndrFm = string.Format($"=TECalendar( \"{selectedIsoCntry}\", \"{selectedIndic}\", \"{date1}\", \"{date2}\", \"{newColumns}\", {dateCell[2, 2].Address[false, false, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1]})");
helperClass.log.Info("Formula {0}", clndrFm);
MyRibbon.cellRange = helperClass.CellAddress(activeCellPositionBox.Text);
MyRibbon.cellRange.Formula = clndrFm;
Close();
}
/// <summary>
/// 导出Excel 的方法
/// </summary>
private void tslExport_Excel(string fileName, DataGridView myDGV)
{
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; //被点了取消
}
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(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1
Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range("A1", "Z" + (myDGV.SelectedRows.Count + 10)); //把Execl设置问文本格式
range.NumberFormatLocal = "@";
//写入标题
for (int i = 1; i < myDGV.ColumnCount; i++)
{
worksheet.Cells[1, i] = myDGV.Columns[i].HeaderText;
}
//写入数值
int s = 0;
for (int r = 0; r < myDGV.Rows.Count; r++)
{
if (Convert.ToBoolean(myDGV.Rows[r].Cells[0].Value))
{
for (int i = 1; i < myDGV.ColumnCount; i++)
{
worksheet.Cells[s + 2, i] = myDGV.Rows[r].Cells[i].Value;
}
System.Windows.Forms.Application.DoEvents();
s++;
}
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
Microsoft.Office.Interop.Excel.Range rang = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[myDGV.Rows.Count + 2, 2]);
rang.NumberFormat = "000000000000";
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
//fileSaved = true;
}
catch
{
//fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + "");
}
}
//else
//{
// fileSaved = false;
//}
xlApp.Quit();
GC.Collect();//强行销毁
MessageBox.Show(fileName + ",保存成功", "提示", MessageBoxButtons.OK);
}
public static void Export_To_Excel_Interop(DataTable dtexportdata, string[] HeaderCaptions, string[] DataColumnsName, string FileHeader, string FullFileName, string ExportTempFilePath, string RelativeName = "")
{
object Opt = Type.Missing;
Microsoft.Office.Interop.Excel.Application ExlApp = new Microsoft.Office.Interop.Excel.Application();
try
{
ExlWrkBook = ExlApp.Workbooks.Open(ExportTempFilePath + "\\BlankExcelFormat.xls", 0,
true,
5,
"",
"",
true,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
"\t",
false,
false,
0,
true,
1,
0);
ExlWrkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExlWrkBook.ActiveSheet;
ExlWrkSheet.Cells[1, 1] = FileHeader;
string Exlhead = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
Microsoft.Office.Interop.Excel.Range rangeTitle = ExlWrkSheet.get_Range("A1", Exlhead[HeaderCaptions.Length - 1] + "1");
rangeTitle.Cells.Merge();
// rangeTitle.Cells.Interior.Color = System.Drawing.Color.Gray;
int irow = 2, icol = 1;
for (int i = 0; i < HeaderCaptions.Length; i++)
{
ExlWrkSheet.Cells[irow, icol] = Convert.ToString(HeaderCaptions[i]);
Microsoft.Office.Interop.Excel.Range rangHeader = ExlWrkSheet.get_Range(Exlhead[i] + "2", Exlhead[i] + "2");
ExlWrkSheet.Columns[i + 1].ColumnWidth = HeaderCaptions[i].Length + 10;
ExlWrkSheet.Cells[irow, icol].Font.FontStyle = "Bold";
ExlWrkSheet.Cells[irow, icol].Font.Size = 12;
//ExlWrkSheet.Cells[irow, icol].Font.ColorIndex = 9;
// ExlWrkSheet.Cells[irow, icol].Interior.Color = System.Drawing.Color.LightGray;
icol++;
}
irow = 3; icol = 1;
foreach (DataRow dr in dtexportdata.Rows)
{
for (int i = 0; i < DataColumnsName.Length; i++)
{
ExlWrkSheet.Cells[irow, icol] = Convert.ToString(dr[DataColumnsName[i]]);
icol++;
}
irow++;
icol = 1;
}
Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("A2", Exlhead[DataColumnsName.Length - 1] + (irow - 1).ToString());
range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
range.WrapText = true;
ExlWrkBook.SaveAs(FullFileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
ResponseHelper.Redirect(RelativeName, "_self", "");
}
catch (Exception ex)
{
throw ex;
}
finally
{
ExlWrkBook.Close(null, null, null);
ExlApp.Workbooks.Close();
ExlApp.Quit();
Marshal.ReleaseComObject(ExlApp);
Marshal.ReleaseComObject(ExlWrkSheet);
Marshal.ReleaseComObject(ExlWrkBook);
}
}
/// <summary>
/// 导出Excel 的方法
/// </summary>
private void tslExport_Excel(string fileName, DataGridView myDGV)
{
string saveFileName = "";
//bool fileSaved = false;
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
return; //被点了取消
}
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(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
//写入标题
for (int i = 1; i < lvwUserList.ColumnCount; i++)
{
worksheet.Cells[1, i] = myDGV.Columns[i].HeaderText;
}
//写入数值
for (int r = 0; r < lvwUserList.Rows.Count; r++)
{
for (int i = 1; i < lvwUserList.ColumnCount; i++)
{
worksheet.Cells[r + 2, i] = lvwUserList.Rows[r].Cells[i].Value;
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
Microsoft.Office.Interop.Excel.Range rang = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[myDGV.Rows.Count + 2, 2]);
rang.NumberFormat = "000000000000";
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
MessageBox.Show(fileName + ",保存成功", "提示", MessageBoxButtons.OK);
}
private void ExcelEintragen(Mitarbeiter ma)
{
Microsoft.Office.Interop.Excel.Application excel = null;
Microsoft.Office.Interop.Excel.Workbook workbook = null;
try
{
var fn = @"C:\Users\tn.DESKTOP-91L17BK\Documents\Visual Studio 2015\Projects\PersonalVerwaltung\PersonalVerwaltung\bin\Debug\auswertung_fehlzeiten2.xlsx";
excel = new Microsoft.Office.Interop.Excel.Application();
//excel.Visible = true;
// Arbeitsmappe öffnen
workbook = excel.Workbooks.Open(fn);
// Arbeitsblatt referenzieren
// Tabelle öffnen
Microsoft.Office.Interop.Excel.Worksheet worksheet = workbook.Worksheets[1];
worksheet.Name = $"{ma.vorname} {ma.nachname}";
/*
* // Hintergrundfarbe, Linienart, -staerke, -farbe, Format
* aRange.Cells.Borders.LineStyle = DataGridLineStyle.Solid;
* bRange.Cells.Interior.Color = System.Drawing.ColorTranslator.ToOle(col);
* bRange.Borders.Weight = 2;
* bRange.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
* bRange.Borders.Color = Color.Black.ToArgb();
* String[] cellFormat = { "#####", "dd/mm/yyyy", "@", "#######0.00", "@", "@", "#######0.00 €" };
* bRange.NumberFormat = cellFormat[2];
*/
// Ueberschrift: Mitarbeitername
worksheet.Cells.ClearContents();
Microsoft.Office.Interop.Excel.Range bRange = worksheet.get_Range("B2:B2");
bRange.Value = $"{ma.vorname} {ma.nachname}";
//bRange.Font.Size = 15;
// Zelleneinstellungen
//bRange.Cells.Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.Red);
//bRange.Borders.Weight = 3;
//bRange.Cells.Borders.LineStyle = DataGridLineStyle.Solid;
//bRange.Borders.Color = Color.Black.ToArgb();
// Tabellenkopf
bRange = worksheet.get_Range("B4:B4");
bRange.Value = "Datum von";
bRange = worksheet.get_Range("C4:C4");
bRange.Value = "Datum bis";
bRange = worksheet.get_Range("D4:D4");
bRange.Value = "Fehltage";
bRange = worksheet.get_Range("E4:E4");
bRange.Value = "Fehlgrund";
// Eintraege
int i = 5;
char c = 'B';
Dictionary <string, int> sum = new Dictionary <string, int>();
//Hashtable sum = new Hashtable();
foreach (var x in listFG)
{
sum.Add(x.fehlgrund, 0);
}
sum.Add("gesamt", 0);
//int[] sum = new int[listFG.Count()];
foreach (var fz in listFZ)
{
if (ma.ma_id == fz.ma_id)
{
bRange = worksheet.get_Range(incrementRange(c++, i));
bRange.Value = fz.fz_von.ToShortDateString();
bRange = worksheet.get_Range(incrementRange(c++, i));
bRange.Value = fz.fz_bis.ToShortDateString();
bRange = worksheet.get_Range(incrementRange(c++, i));
bRange.Value = fz.fehltage.ToString();
bRange = worksheet.get_Range(incrementRange(c++, i));
bRange.Value = listFG[listFG.FindIndex(fg => fg.fg_id == fz.fehlgrund)].fehlgrund;
// todo
sum[bRange.Value] += fz.fehltage;
sum["gesamt"] += fz.fehltage;
c = 'B';
i++;
}
}
i += 2;
bRange = worksheet.get_Range(incrementRange('C', i));
bRange.Value = "Summe:";
bRange = worksheet.get_Range(incrementRange('D', i));
bRange.Value = sum["gesamt"];
sum.Remove("gesamt");
i += 2;
foreach (var x in sum)
{
bRange = worksheet.get_Range(incrementRange('C', i));
bRange.Value = x.Key;
bRange = worksheet.get_Range(incrementRange('D', i));
bRange.Value = x.Value;
i++;
}
bRange = worksheet.get_Range(incrementRange('C', ++i));
bRange.Value = "Resturlaub";
bRange = worksheet.get_Range(incrementRange('D', i));
bRange.Value = ma.urlaubsAnspruch - sum["Urlaub"];
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
// Arbeitsmappe speichern
workbook.Save();
// Excel beenden
excel.Quit();
}
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnExcel_Click(object sender, EventArgs e)
{
RecordingBLL recordingBLL = new RecordingBLL();
DataTable dt = recordingBLL.GetAllUser(this.label1.Text);
//下载Nuget包 Microsoft.Office.Interop
Microsoft.Office.Interop.Excel.Application appexcel = new Microsoft.Office.Interop.Excel.Application();
SaveFileDialog savefiledialog = new SaveFileDialog();
System.Reflection.Missing miss = System.Reflection.Missing.Value;
appexcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbookdata;
Microsoft.Office.Interop.Excel.Worksheet worksheetdata;
Microsoft.Office.Interop.Excel.Range rangedata;
//设置对象不可见
appexcel.Visible = false;
System.Globalization.CultureInfo currentci = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-us");
workbookdata = appexcel.Workbooks.Add(miss);
worksheetdata = (Microsoft.Office.Interop.Excel.Worksheet)workbookdata.Worksheets.Add(miss, miss, miss, miss);
//给工作表赋名称
worksheetdata.Name = "saved";
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheetdata.Cells[1, i + 1] = dt.Columns[i].ColumnName.ToString();
}
//因为第一行已经写了表头，所以所有数据都应该从a2开始
rangedata = worksheetdata.get_Range("a2", miss);
Microsoft.Office.Interop.Excel.Range xlrang = null;
//irowcount为实际行数，最大行
int irowcount = dt.Rows.Count;
int iparstedrow = 0, icurrsize = 0;
//ieachsize为每次写行的数值，可以自己设置
int ieachsize = 1000;
//icolumnaccount为实际列数，最大列数
int icolumnaccount = dt.Columns.Count;
//在内存中声明一个ieachsize×icolumnaccount的数组，ieachsize是每次最大存储的行数，icolumnaccount就是存储的实际列数
object[,] objval = new object[ieachsize, icolumnaccount];
icurrsize = ieachsize;
while (iparstedrow < irowcount)
{
if ((irowcount - iparstedrow) < ieachsize)
{
icurrsize = irowcount - iparstedrow;
}
//用for循环给数组赋值
for (int i = 0; i < icurrsize; i++)
{
for (int j = 0; j < icolumnaccount; j++)
{
objval[i, j] = dt.Rows[i + iparstedrow][j].ToString();
}
System.Windows.Forms.Application.DoEvents();
}
string X = "A" + ((int)(iparstedrow + 2)).ToString();
string col = "";
if (icolumnaccount <= 26)
{
col = ((char)('A' + icolumnaccount - 1)).ToString() + ((int)(iparstedrow + icurrsize + 1)).ToString();
}
else
{
col = ((char)('A' + (icolumnaccount / 26 - 1))).ToString() + ((char)('A' + (icolumnaccount % 26 - 1))).ToString() + ((int)(iparstedrow + icurrsize + 1)).ToString();
}
xlrang = worksheetdata.get_Range(X, col);
// 调用range的value2属性，把内存中的值赋给excel
xlrang.Value2 = objval;
iparstedrow = iparstedrow + icurrsize;
}
//保存工作表
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlrang);
xlrang = null;
//调用方法关闭excel进程
appexcel.Visible = true;
}
public void ExcelExport(System.Data.DataTable dt, string SheetName)
{
Microsoft.Office.Interop.Excel.Application appexcel = new Microsoft.Office.Interop.Excel.Application();
SaveFileDialog savefiledialog = new SaveFileDialog();
System.Reflection.Missing miss = System.Reflection.Missing.Value;
appexcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbookdata = appexcel.Workbooks.Add(System.Type.Missing);
Microsoft.Office.Interop.Excel.Worksheet worksheetdata = (Microsoft.Office.Interop.Excel.Worksheet)workbookdata.Worksheets[1];
//Microsoft.Office.Interop.Excel.Range rangedata;
//创建Excel
//Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
//Microsoft.Office.Interop.Excel.Workbook ExcelBook = appexcel.Workbooks.Add(System.Type.Missing);
////创建工作表（即Excel里的子表sheet） 1表示在子表sheet1里进行数据导出
//Microsoft.Office.Interop.Excel.Worksheet ExcelSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelBook.Worksheets[1];
////设置Sheet标题
string start = "A1";
string end = ChangeASC(dt.Columns.Count) + "1";
Microsoft.Office.Interop.Excel.Range _Range = (Microsoft.Office.Interop.Excel.Range)worksheetdata.get_Range(start, end);
_Range.Merge(0); //单元格合并动作(要配合上面的get_Range()进行设计)
_Range = worksheetdata.get_Range(start, end);
_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
_Range.Font.Size = 22; //设置字体大小
_Range.Font.Name = "宋体"; //设置字体的种类
worksheetdata.Cells[1, 1] = SheetName; //Excel单元格赋值
_Range.EntireColumn.AutoFit(); //自动调整列宽
//设置对象不可见
appexcel.Visible = false;
System.Globalization.CultureInfo currentci = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-us");
//workbookdata = appexcel.Workbooks.Add(miss);
//worksheetdata = (Microsoft.Office.Interop.Excel.Worksheet)workbookdata.Worksheets.Add(miss, miss, miss, miss);
//给工作表赋名称
worksheetdata.Name = SheetName;
start = "A2";
end = ChangeASC(dt.Columns.Count) + "2";
_Range = worksheetdata.get_Range(start, end);
_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheetdata.Cells[2, i + 1] = dt.Columns[i].ColumnName.ToString();
}
//因为第一行已经写了表头，所以所有数据都应该从a2开始
//rangedata = worksheetdata.get_Range("a3", miss);
//Microsoft.Office.Interop.Excel.Range xlrang = null;
//irowcount为实际行数，最大行
int irowcount = dt.Rows.Count;
int iparstedrow = 1, icurrsize = 0;
//ieachsize为每次写行的数值，可以自己设置
int ieachsize = 1000;
//icolumnaccount为实际列数，最大列数
int icolumnaccount = dt.Columns.Count;
//在内存中声明一个ieachsize×icolumnaccount的数组，ieachsize是每次最大存储的行数，icolumnaccount就是存储的实际列数
object[,] objval = new object[ieachsize + 1, icolumnaccount];
icurrsize = ieachsize;
while (iparstedrow <= irowcount)
{
if ((irowcount - iparstedrow) < ieachsize)
{
icurrsize = irowcount - iparstedrow + 1;
}
//用for循环给数组赋值
for (int i = 0; i < icurrsize; i++)
{
for (int j = 0; j < icolumnaccount; j++)
{
objval[i, j] = dt.Rows[i + iparstedrow - 1][j].ToString();
}
//System.Windows.Forms.Application.DoEvents();
}
string X = "A" + ((int)(iparstedrow + 2)).ToString();
string col = "";
if (icolumnaccount <= 26)
{
col = ((char)('A' + icolumnaccount - 1)).ToString() + ((int)(iparstedrow + icurrsize + 1)).ToString();
}
else
{
col = ((char)('A' + (icolumnaccount / 26 - 1))).ToString() + ((char)('A' + (icolumnaccount % 26 - 1))).ToString() + ((int)(iparstedrow + icurrsize + 1)).ToString();
}
_Range = worksheetdata.get_Range(X, col);
// 调用range的value2属性，把内存中的值赋给excel
_Range.Value2 = objval;
_Range.EntireColumn.AutoFit(); //自动调整列宽
_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
iparstedrow = iparstedrow + icurrsize;
}
//保存工作表
System.Runtime.InteropServices.Marshal.ReleaseComObject(_Range);
_Range = null;
//调用方法关闭excel进程
appexcel.Visible = true;
}
public Range(Microsoft.Office.Interop.Excel.Range range)
{
_range = range;
}
//打印功能
private Microsoft.Office.Interop.Excel._Worksheet printValue(Microsoft.Office.Interop.Excel._Worksheet mysheet, Microsoft.Office.Interop.Excel._Workbook mybook)
{
int ind = 0;
//外表信息
mysheet.Cells[3, 1].Value = "生产指令编号：" + dt记录.Rows[0]["生产指令编号"].ToString();
mysheet.Cells[3, 5].Value = "纸箱代码：" + dt记录.Rows[0]["纸箱代码"].ToString();
mysheet.Cells[3, 9].Value = "外包标签：" + dt记录.Rows[0]["外包标签"].ToString();
mysheet.Cells[3, 12].Value = dt记录.Rows[0]["包装规格每包千克"].ToString() + " Kg/包";
mysheet.Cells[4, 1].Value = "产品代码：" + dt记录.Rows[0]["产品代码"].ToString();
mysheet.Cells[4, 5].Value = "纸箱批号：" + dt记录.Rows[0]["纸箱批号"].ToString();
mysheet.Cells[4, 9].Value = "标签代码：" + dt记录.Rows[0]["标签代码"].ToString();
mysheet.Cells[4, 12].Value = dt记录.Rows[0]["包装规格每箱千克"].ToString() + " Kg/箱";
mysheet.Cells[5, 1].Value = "产品批号：" + dt记录.Rows[0]["产品批号"].ToString();
mysheet.Cells[5, 6].Value = " " + dt记录.Rows[0]["领用数量"].ToString();
mysheet.Cells[5, 8].Value = " " + dt记录.Rows[0]["退库数量"].ToString();
mysheet.Cells[5, 9].Value = "标签领用数量：" + dt记录.Rows[0]["标签领用数量"].ToString();
mysheet.Cells[5, 12].Value = dt记录.Rows[0]["包装规格每箱只数"].ToString() + " 只/箱";
//内表信息
int rownum = dt记录详情.Rows.Count;
//无需插入的部分
for (int i = 0; i < (rownum > 13 ? 13 : rownum); i++)
{
mysheet.Cells[7 + i, 1].Value = dt记录详情.Rows[i]["序号"].ToString();
mysheet.Cells[7 + i, 2].Value = Convert.ToDateTime(dt记录详情.Rows[i]["包装日期"].ToString()).ToString("yyyy/MM/dd");
mysheet.Cells[7 + i, 3].Value = Convert.ToDateTime(dt记录详情.Rows[i]["时间"].ToString()).ToString("HH:mm:ss");
mysheet.Cells[7 + i, 4].Value = dt记录详情.Rows[i]["包装箱号"].ToString();
mysheet.Cells[7 + i, 5].Value = dt记录详情.Rows[i]["包装明细"].ToString();
mysheet.Cells[7 + i, 6].Value = dt记录详情.Rows[i]["包装数量箱数"].ToString();
mysheet.Cells[7 + i, 7].Value = dt记录详情.Rows[i]["产品数量只数"].ToString();
mysheet.Cells[7 + i, 8].Value = dt记录详情.Rows[i]["是否贴标签"].ToString() == "Yes" ? "√" : "×";
mysheet.Cells[7 + i, 9].Value = dt记录详情.Rows[i]["是否打包封箱"].ToString() == "Yes" ? "√" : "×";
mysheet.Cells[7 + i, 10].Value = dt记录详情.Rows[i]["操作员"].ToString();
mysheet.Cells[7 + i, 11].Value = dt记录详情.Rows[i]["审核员"].ToString();
mysheet.Cells[7 + i, 12].Value = dt记录详情.Rows[i]["备注"].ToString();
}
//需要插入的部分
if (rownum > 13)
{
for (int i = 13; i < rownum; i++)
{
Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)mysheet.Rows[7 + i, Type.Missing];
range.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown,
Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
mysheet.Cells[7 + i, 1].Value = dt记录详情.Rows[i]["序号"].ToString();
mysheet.Cells[7 + i, 2].Value = Convert.ToDateTime(dt记录详情.Rows[i]["包装日期"].ToString()).ToString("yyyy/MM/dd");
mysheet.Cells[7 + i, 3].Value = Convert.ToDateTime(dt记录详情.Rows[i]["时间"].ToString()).ToString("HH:mm:ss");
mysheet.Cells[7 + i, 4].Value = dt记录详情.Rows[i]["包装箱号"].ToString();
mysheet.Cells[7 + i, 5].Value = dt记录详情.Rows[i]["包装明细"].ToString();
mysheet.Cells[7 + i, 6].Value = dt记录详情.Rows[i]["包装数量箱数"].ToString();
mysheet.Cells[7 + i, 7].Value = dt记录详情.Rows[i]["产品数量只数"].ToString();
mysheet.Cells[7 + i, 8].Value = dt记录详情.Rows[i]["是否贴标签"].ToString() == "Yes" ? "√" : "×";
mysheet.Cells[7 + i, 9].Value = dt记录详情.Rows[i]["是否打包封箱"].ToString() == "Yes" ? "√" : "×";
mysheet.Cells[7 + i, 10].Value = dt记录详情.Rows[i]["操作员"].ToString();
mysheet.Cells[7 + i, 11].Value = dt记录详情.Rows[i]["审核员"].ToString();
mysheet.Cells[7 + i, 12].Value = dt记录详情.Rows[i]["备注"].ToString();
}
ind = rownum - 13;
}
mysheet.Cells[21 + ind, 6].Value = dt记录.Rows[0]["包装数量箱数合计"].ToString();
mysheet.Cells[21 + ind, 7].Value = dt记录.Rows[0]["产品数量只数合计"].ToString();
//mysheet.Cells[22+ind, 1].Value = "审核员：" + dt记录.Rows[0]["审核员"].ToString();
//加页脚
int sheetnum;
SqlDataAdapter da = new SqlDataAdapter("select ID from " + table + " where 生产指令ID=" + InstruID.ToString(), mySystem.Parameter.conn);
DataTable dt = new DataTable("temp");
da.Fill(dt);
List <Int32> sheetList = new List <Int32>();
for (int i = 0; i < dt.Rows.Count; i++)
{
sheetList.Add(Convert.ToInt32(dt.Rows[i]["ID"].ToString()));
}
sheetnum = sheetList.IndexOf(Convert.ToInt32(dt记录.Rows[0]["ID"])) + 1;
mysheet.PageSetup.RightFooter = Instruction + "-09-" + sheetnum.ToString("D3") + " &P/" + mybook.ActiveSheet.PageSetup.Pages.Count.ToString(); // "生产指令-步骤序号- 表序号 /&P"; // &P 是页码
//返回
return(mysheet);
}
public static void generateExcel()
{
packages = DBConnector.getInstance().getPackages();
buckets = DBConnector.getInstance().getBuckets();
generatePackageList();
checkProcess();
generateBucketList();
/******************** create a workbook *************************/
excel = new Microsoft.Office.Interop.Excel.Application();
excel.Visible = false;
excel.DisplayAlerts = false;
excelworkBook = excel.Workbooks.Add(Type.Missing);
/********************* create new sheet (Activity List) ***************************/
excelSheetAll = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
excelSheetAll.Name = "Activity List";
int row = 1;
int tb1_start_x = row;
int tb1_start_y = 1;
excelSheetAll.Cells[row, 1] = "Process Name";
excelSheetAll.Cells[row, 2] = "Duration";
excelSheetAll.Cells[row, 3] = "Main Window Title";
row++;
foreach (KeyValuePair<string, Activity> pair in activityList)
{
excelSheetAll.Cells[row, 1] = pair.Value.processName;
excelSheetAll.Cells[row, 2] = pair.Value.duration.ToString("g");
excelSheetAll.Cells[row, 3] = pair.Key;
row++;
}
int tb1_end_x = row - 1;
int tb1_end_y = 3;
excelCellrange = excelSheetAll.Range[excelSheetAll.Cells[tb1_start_x, tb1_start_y], excelSheetAll.Cells[tb1_end_x, tb1_end_y]];
excelCellrange.NumberFormat = "hh:mm:ss.000";
excelCellrange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
excelCellrange = excelSheetAll.Range[excelSheetAll.Cells[tb1_start_x, tb1_start_y], excelSheetAll.Cells[tb1_start_x, tb1_end_y]];
FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);
/*************************** create new sheet (Packaged Activity List) ****************************/
excelSheetPackaged = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.Worksheets.Add();
excelSheetPackaged.Name = "Packaged Activity List";
row = 1;
int tb2_start_x = row;
int tb2_start_y = 1;
excelSheetPackaged.Cells[row, 1] = "Package Name";
excelSheetPackaged.Cells[row, 2] = "Duration";
row++;
foreach (KeyValuePair<string, TimeSpan> pair in packagedList)
{
excelSheetPackaged.Cells[row, 1] = pair.Key;
excelSheetPackaged.Cells[row, 2] = pair.Value.ToString("g");
row++;
}
int tb2_end_x = row - 1;
int tb2_end_y = 2;
excelCellrange = excelSheetPackaged.Range[excelSheetPackaged.Cells[tb2_start_x, tb2_start_y], excelSheetPackaged.Cells[tb2_end_x, tb2_end_y]];
excelCellrange.NumberFormat = "hh:mm:ss.000";
excelCellrange.EntireColumn.AutoFit();
border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
excelCellrange = excelSheetPackaged.Range[excelSheetPackaged.Cells[tb2_start_x, tb2_start_y], excelSheetPackaged.Cells[tb2_start_x, tb2_end_y]];
FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);
chartObjects = (Microsoft.Office.Interop.Excel.ChartObjects)excelSheetPackaged.ChartObjects(Type.Missing);
chartObject = (Microsoft.Office.Interop.Excel.ChartObject)chartObjects.Add(220, 0, 400, 300);
chart = chartObject.Chart;
chart.HasTitle = true;
chart.ChartTitle.Text = "Packaged Activity List";
chartRange = excelSheetPackaged.get_Range("A" + tb2_start_x, "B" + tb2_end_x);
chart.SetSourceData(chartRange, System.Reflection.Missing.Value);
chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlPie;
chartObjects = (Microsoft.Office.Interop.Excel.ChartObjects)excelSheetPackaged.ChartObjects(Type.Missing);
chartObject = (Microsoft.Office.Interop.Excel.ChartObject)chartObjects.Add(220, 320, 400, 300);
chart = chartObject.Chart;
chart.HasTitle = true;
chart.ChartTitle.Text = "Packaged Activity List";
chartRange = excelSheetPackaged.get_Range("A" + tb2_start_x, "B" + tb2_end_x);
chart.SetSourceData(chartRange, System.Reflection.Missing.Value);
chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered;
/************************* create new sheet (Bucketed Activity List) ******************************/
excelSheetBucketed = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.Worksheets.Add();
excelSheetBucketed.Name = "Bucketed Activity List";
row = 1;
int tb3_start_x = row;
int tb3_start_y = 1;
excelSheetBucketed.Cells[row, 1] = "Bucket Name";
excelSheetBucketed.Cells[row, 2] = "Duration";
row++;
foreach (KeyValuePair<string, TimeSpan> pair in bucketedList)
{
excelSheetBucketed.Cells[row, 1] = pair.Key;
excelSheetBucketed.Cells[row, 2] = pair.Value.ToString("g");
row++;
}
int tb3_end_x = row - 1;
int tb3_end_y = 2;
excelCellrange = excelSheetBucketed.Range[excelSheetBucketed.Cells[tb3_start_x, tb3_start_y], excelSheetBucketed.Cells[tb3_end_x, tb3_end_y]];
excelCellrange.NumberFormat = "hh:mm:ss.000";
excelCellrange.EntireColumn.AutoFit();
border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
excelCellrange = excelSheetBucketed.Range[excelSheetBucketed.Cells[tb3_start_x, tb3_start_y], excelSheetBucketed.Cells[tb3_start_x, tb3_end_y]];
FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);
chartObjects = (Microsoft.Office.Interop.Excel.ChartObjects)excelSheetBucketed.ChartObjects(Type.Missing);
chartObject = (Microsoft.Office.Interop.Excel.ChartObject)chartObjects.Add(220, 0, 400, 300);
chart = chartObject.Chart;
chart.HasTitle = true;
chart.ChartTitle.Text = "Buckted Activity List";
chartRange = excelSheetBucketed.get_Range("A" + tb3_start_x, "B" + tb3_end_x);
chart.SetSourceData(chartRange, System.Reflection.Missing.Value);
chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlPie;
chartObjects = (Microsoft.Office.Interop.Excel.ChartObjects)excelSheetBucketed.ChartObjects(Type.Missing);
chartObject = (Microsoft.Office.Interop.Excel.ChartObject)chartObjects.Add(220, 320, 400, 300);
chart = chartObject.Chart;
chart.HasTitle = true;
chart.ChartTitle.Text = "Buckted Activity List";
chartRange = excelSheetBucketed.get_Range("A" + tb3_start_x, "B" + tb3_end_x);
chart.SetSourceData(chartRange, System.Reflection.Missing.Value);
chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered;
/*************** save excel *******************/
//UserPrincipal.Current.DisplayName
String filePath = "C:\\Users\\" + Environment.UserName + "\\Desktop\\ActivityList-" + Environment.UserName + "-" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss") + ".xlsx";
excelworkBook.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, true, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//excelworkBook.SaveAs(filePath);
excelworkBook.Close();
excel.Quit();
Console.WriteLine("-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------");
Console.WriteLine("Export to Excel");
Thread.Sleep(1000);
System.Diagnostics.Process.Start(filePath);
}
public void addData(int row, int col, string data,
string cell1, string cell2,string format)
{
worksheet.Cells[row, col] = data;
workSheet_range = worksheet.get_Range(cell1, cell2);
workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb();
workSheet_range.NumberFormat = format;
}
