public void NutritionPrint(string savePath)
{
try
{
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath+@"report\nutrition.xls",
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 = (Excel.Sheets)m_objBook.Worksheets;
int number = m_objSheets.Count;
writeCover();
writeStuAmount();
writeStuConvert();
writeACC1();
writeACC2();
writeElement();
m_objBook.SaveAs(savePath, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
}
catch(Exception ex)
{
Util.WriteLog(ex.Message,Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
public void WriteStuCardInfoXLS(DataSet dsStuInfo,string getPath)
{
KillProcess();
try
{
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)(m_objBooks.Open( //打开该文件
getPath,Type.Missing,Type.Missing,Type.Missing,
Type.Missing,Type.Missing,Type.Missing,Type.Missing,
Type.Missing,Type.Missing,Type.Missing,Type.Missing,
Type.Missing));
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
object[,] objData = null;
if ( dsStuInfo.Tables[0].Rows.Count > 0 )
{
objData = new object[dsStuInfo.Tables[0].Rows.Count,2];
for ( int i=0; i<dsStuInfo.Tables[0].Rows.Count; i++ )
{
objData[i,0] = dsStuInfo.Tables[0].Rows[i][0].ToString();
objData[i,1] = dsStuInfo.Tables[0].Rows[i][1].ToString();
}
}
m_objRange = m_objSheet.get_Range("A2",m_objOpt);
m_objRange = m_objRange.get_Resize(dsStuInfo.Tables[0].Rows.Count,2);
m_objRange.Value = objData;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.Font.Size = 10;
m_objExcel.Visible = true;
}
catch(Exception e)
{
Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
}
}
public void FinanceStatPrint(DataTable data, string className, DateTime date, string savePath)
{
DataTable dtPresents = new FinanInfoDataAccess().GetStudentPresents(date, className);
if (dtPresents == null || dtPresents.Rows.Count == 0)
{
throw new Exception("没有要使用的数据！");
}
else
{
data.Columns.AddRange(new DataColumn[]{ new DataColumn("小计", Type.GetType("System.Double")),
new DataColumn("stuPresent", Type.GetType("System.String")),
new DataColumn("stuAbsent", Type.GetType("System.String"))});
if (dtPresents.Rows.Count != data.Rows.Count)
{
throw new Exception("检测到数据完整性错误，请尝试重新生成数据！");
}
else
{
for (int i = 0; i < dtPresents.Rows.Count; i++)
{
data.Rows[i]["小计"] = 0;
data.Rows[i]["stuPresent"] = dtPresents.Rows[i]["times"];
data.Rows[i]["stuAbsent"] = dtPresents.Rows[i]["times_abs"];
}
}
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath + @"report\FinanceStat.xls",
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 = (Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
object[,] objData = new object[data.Rows.Count + 1, data.Columns.Count + 4];
for(int row = 0; row < data.Rows.Count; row++)
{
objData[row, 0] = row + 1;
for (int column = 0; column < data.Columns.Count - 3; column++)
{
if (column <= 2)
{
objData[row, column + 1] = data.Rows[row][column];
}
else if(column == 3)
{
objData[row, column + 1] = data.Rows[row][data.Columns.Count - 2];
objData[row, column + 2] = data.Rows[row][data.Columns.Count - 1];
double temp = Convert.ToDouble(data.Rows[row][column]);
objData[row, column + 3] = temp;
objData[data.Rows.Count, column + 3] = temp + (objData[data.Rows.Count, column + 3] == null ? 0 :
Convert.ToDouble(objData[data.Rows.Count, column + 3]));
}
else if (column >= 4 && column < 7)
{
double temp = Convert.ToDouble(data.Rows[row][column]);
objData[row, column + 3] = temp;
objData[data.Rows.Count, column + 3] = temp + (objData[data.Rows.Count, column + 3] == null ? 0 :
Convert.ToDouble(objData[data.Rows.Count, column + 3]));
}
else if (column == 7)
{
double temp1 = Convert.ToDouble(data.Rows[row][column - 4]) +
Convert.ToDouble(data.Rows[row][column - 3]) + Convert.ToDouble(data.Rows[row][column - 2]) +
Convert.ToDouble(data.Rows[row][column - 1]);
objData[row, column + 3] = temp1;
double temp2 = Convert.ToDouble(data.Rows[row][column]);
objData[row, column + 4] = temp2;
objData[data.Rows.Count, column + 3] = temp1 + (objData[data.Rows.Count, column + 3] == null ? 0 :
Convert.ToDouble(objData[data.Rows.Count, column + 3]));
objData[data.Rows.Count, column + 4] = temp2 + (objData[data.Rows.Count, column + 4] == null ? 0 :
Convert.ToDouble(objData[data.Rows.Count, column + 4]));
}
else
{
double temp = Convert.ToDouble(data.Rows[row][column]);
objData[row, column + 4] = temp;
objData[data.Rows.Count, column + 4] = temp + (objData[data.Rows.Count, column + 4] == null ? 0 :
Convert.ToDouble(objData[data.Rows.Count, column + 4]));
}
}
}
m_objRange = m_objSheet.get_Range("A6", m_objOpt);
m_objRange = m_objRange.get_Resize(data.Rows.Count + 1, data.Columns.Count + 1);
m_objRange.Value = objData;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
m_objRange.Font.Size = 10;
m_objRange = m_objSheet.get_Range("G3", m_objOpt);
m_objRange.Value = "各项费用";
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[3, 7], m_objSheet.Cells[4, data.Columns.Count]);
m_objRange.Merge(m_objOpt);
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
m_objRange.Font.Size = 12;
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[3, data.Columns.Count + 1], m_objSheet.Cells[4, data.Columns.Count + 1]);
m_objRange.Merge(m_objOpt);
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
object[, ] objColumn = new object[1, data.Columns.Count - 5];
for (int column = 3; column < data.Columns.Count - 3; column++ )
{
if (column < 7)
{
objColumn[0, column - 3] = data.Columns[column].ColumnName;
}
if (column == 7)
{
objColumn[0, column - 3] = data.Columns[data.Columns.Count - 3].ColumnName;
objColumn[0, column - 2] = data.Columns[column].ColumnName;
}
else
{
objColumn[0, column - 2] = data.Columns[column].ColumnName;
}
}
m_objRange = m_objSheet.get_Range("G5", m_objOpt);
m_objRange = m_objRange.get_Resize(1, data.Columns.Count - 5);
m_objRange.Value = objColumn;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
m_objRange.Font.Size = 10.5;
for (int column = 5; column < data.Columns.Count - 3; column++)
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[6, column + 2], m_objSheet.Cells[5 + data.Rows.Count, column + 2]);
object i = m_objRange.Calculate();
string s = "asf";
}
m_objBook.SaveAs(savePath, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
}
}
}//end ArrayToExcel
/// <summary>
/// 将二维数组数据写入Excel文件（不分页），合并指定列的相同行
/// </summary>
/// <param name="arr">二维数组</param>
/// <param name="top">行索引</param>
/// <param name="left">列索引</param>
/// <param name="isFormula">填充的数据是否需要计算</param>
/// <param name="mergeColumnIndex">需要合并行的列索引</param>
public void ArrayToExcel(string[,] arr, int top, int left, bool isFormula, int mergeColumnIndex)
{
int rowCount = arr.GetLength(0); //二维数组行数（一维长度）
int colCount = arr.GetLength(1); //二维数据列数（二维长度）
range = (Excel.Range)workSheet.Cells[top, left];
range = range.get_Resize(rowCount, colCount);
//注意：使用range.FormulaArray写合并的单元格会出问题
if (isFormula)
range.FormulaArray = arr;
else
range.Value = arr;
this.MergeRows(workSheet, mergeColumnIndex, top, rowCount);
}//end ArrayToExcel
private void Dispose()
{
workBook.Close(null, null, null);
app.Workbooks.Close();
app.Quit();
if (range != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
range = null;
}
if (range1 != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range1);
range1 = null;
}
if (range2 != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range2);
range2 = null;
}
if (textBox != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(textBox);
textBox = 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();
this.KillExcelProcess();
}//end Dispose
/// <summary>
/// 将指定范围区域拷贝到目标区域
/// </summary>
/// <param name="sheetIndex">WorkSheet索引</param>
/// <param name="startCell">要拷贝区域的开始Cell位置（比如：A10）</param>
/// <param name="endCell">要拷贝区域的结束Cell位置（比如：F20）</param>
/// <param name="targetCell">目标区域的开始Cell位置（比如：H10）</param>
public void RangeCopy(int sheetIndex, string startCell, string endCell, string targetCell)
{
if (sheetIndex > this.WorkSheetCount)
{
this.KillExcelProcess();
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
try
{
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);
range1 = workSheet.get_Range(startCell, endCell);
range2 = workSheet.get_Range(targetCell, this.missing);
range1.Copy(range2);
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
/// <summary>
/// 复制列（在指定WorkSheet指定列右边复制指定数量列）
/// </summary>
/// <param name="sheetIndex"></param>
/// <param name="columnIndex"></param>
/// <param name="count"></param>
public void CopyColumns(int sheetIndex, int columnIndex, int count)
{
if (sheetIndex > this.WorkSheetCount)
{
this.KillExcelProcess();
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
try
{
workSheet = (Excel.Worksheet)workBook.Worksheets[sheetIndex];
// range1 = (Excel.Range)workSheet.Columns[Type.Missing,columnIndex];
range1 = (Excel.Range)workSheet.get_Range(this.IntToLetter(columnIndex) + "1", this.IntToLetter(columnIndex) + "10000");
for (int i = 1; i <= count; i++)
{
// range2 = (Excel.Range)workSheet.Columns[Type.Missing,columnIndex + i];
range2 = (Excel.Range)workSheet.get_Range(this.IntToLetter(columnIndex + i) + "1", this.IntToLetter(columnIndex + i) + "10000");
range1.Copy(range2);
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
/// <summary>
/// 插行（在指定WorkSheet指定行上面插入指定数量行）
/// </summary>
/// <param name="sheetIndex"></param>
/// <param name="rowIndex"></param>
/// <param name="count"></param>
public void InsertRows(int sheetIndex, int rowIndex, int count)
{
if (sheetIndex > this.WorkSheetCount)
{
this.KillExcelProcess();
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
try
{
workSheet = (Excel.Worksheet)workBook.Worksheets[sheetIndex];
range = (Excel.Range)workSheet.Rows[rowIndex, this.missing];
for (int i = 0; i < count; i++)
{
range.Insert(Excel.XlDirection.xlDown);
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
public bool WriteExcelFile(string sReportName, string sTableName, string sSheetName, string date1, string date2, string constr)
{
StringBuilder sAddrModel = new StringBuilder(40);
StringBuilder sAddrReport = new StringBuilder(40);
string sFileModel = "";
string sFileDer = "";
string colA = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
char[] colABC = new char[26] {
'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'
};
GetPrivateProfileString("Addr", "AddrModel", "", sAddrModel, sAddrModel.Capacity, FILE_NAME);
GetPrivateProfileString("Addr", "AddrReport", "", sAddrReport, sAddrReport.Capacity, FILE_NAME);
sFileModel = sAddrModel.ToString() + sReportName.Trim() + ".xls";
if (date1.CompareTo(date2) == 0)
{
sReportName = sReportName.Trim() + "(" + date1 + ")";
}
else
{
sReportName = sReportName.Trim() + "(" + date1 + "-" + date2 + ")";
}
sFileDer = sAddrReport.ToString() + sReportName.Trim() + ".xls";
if (System.IO.File.Exists(sFileModel) == false)
{
//MessageBox.Show("模板文件不存在");
return(false);
}
if (System.IO.File.Exists(sFileDer) == false)
{
//不存在
System.IO.File.Copy(sFileModel, sFileDer, true);
}
else
{
//存在
//System.IO.File.Delete(sFileDer);
//System.IO.File.Copy(sFileModel,sFileDer,true);
}
//从表中把数据导入SHEET中
string FileName;
Excel.Workbooks eworkbooks = null;
Excel._Workbook eworkbook = null;
Excel.Sheets esheets = null;
Excel._Worksheet esheet = null;
Excel.Range range = null;
excelApp.Visible = false;
excelApp.UserControl = true;
//用Excel应用程序创建Workbooks
eworkbooks = excelApp.Workbooks;
FileName = sFileDer;
eworkbooks.Open(FileName, 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);
//用Workbooks创建Workbook
eworkbook = eworkbooks.get_Item(1);
//用Workbook创建sheets
esheets = eworkbook.Worksheets;
int iSheetNumber = esheets.Count;
int index = 0;
for (int i = 1; i <= iSheetNumber; i++)
{
esheet = (Excel._Worksheet)esheets.get_Item(i);
if (esheet.Name.Trim() == sSheetName.Trim())
{
index++;
break;
}
}
if (index == 0)
{
esheet = null;
//没有找到相应的SHEET页
}
else
{
DataTable dt = CreateDataSet(sTableName.Trim(), constr);
int _rows = dt.Rows.Count;
int _cols = dt.Columns.Count;
int j = 4;
object[] date = new object[4];
date[0] = "日期:";
date[1] = date1;
date[2] = "至";
date[3] = date2;
range = esheet.get_Range("A" + 2, "D" + 2);
range.set_Value(Missing.Value, date);
date = null;
for (int i = 0; i < _rows; i++)
{
j = i + 5;//指定从哪一行开始写入数据
object[] currentRow = new object[_cols];
currentRow = dt.Rows[i].ItemArray;
range = esheet.get_Range("A" + j, colA.Substring(_cols - 1, 1) + j);
range.set_Value(Missing.Value, currentRow);
currentRow = null;
}
}
eworkbook.Save();
range = null;
eworkbook = null;
esheet = null;
esheets = null;
eworkbooks.Close();
return(true);
}
// file read
public void ReadExcel(string path)
{
// path는 Excel파일의 전체 경로입니다.
// 예. D:\test\test.xslx
Excel.Application excelApp = null;
Excel.Workbook wb = null;
Excel.Worksheet ws = null;
try
{
Clear();
excelApp = new Excel.Application();
wb = excelApp.Workbooks.Add(path);
// path 대신 문자열도 가능합니다
// 예. Open(@"D:\test\test.xslx");
ws = wb.Worksheets.get_Item(1) as Excel.Worksheet;
savepath = path;
className = ws.Name;
// 첫번째 Worksheet를 선택합니다.
Excel.Range rng = ws.UsedRange; // '여기'
// 현재 Worksheet에서 사용된 셀 전체를 선택합니다.
object[,] data = rng.Value;
// path line
unityReadPath = data[1, 2].ToString();
for (int r = 2; r <= data.GetLength(0); r++)
{
for (int c = 1; c <= data.GetLength(1); c++)
{
if (data[r, c] == null)
{
continue;
}
string exceldata = data[r, c].ToString();
if (r == 2) // 변수 이름
{
// data name
dataName.Add(exceldata);
}
else if (r == 3) // 타입 이름
{
typeData.Add(exceldata);
}
else
{
if (datainfo.ContainsKey(r) == false)
{
datainfo.Add(r, new List <string>());
datainfo[r].Add(exceldata);
}
else
{
datainfo[r].Add(exceldata);
}
}
exceldata = string.Empty;
// Data 빼오기
// data[r, c] 는 excel의 (r, c) 셀 입니다.
// data.GetLength(0)은 엑셀에서 사용되는 행의 수를 가져오는 것이고,
// data.GetLength(1)은 엑셀에서 사용되는 열의 수를 가져오는 것입니다.
// GetLength와 [ r, c] 의 순서를 바꿔서 사용할 수 있습니다.
}
}
ReleaseExcelObject(rng);
wb.Close(0);
excelApp.Quit();
}
catch (Exception ex)
{
ReleaseExcelObject(ws);
ReleaseExcelObject(wb);
ReleaseExcelObject(excelApp);
throw ex;
}
finally
{
ReleaseExcelObject(ws);
ReleaseExcelObject(wb);
ReleaseExcelObject(excelApp);
System.Windows.Forms.MessageBox.Show("File Load Completed");
}
}
public string ExcelToJson(string path, bool horizontalheader)
{
// bool horizontalheader:
// For Future applications: Allow to read excel with header horizontal or Vertical
// Now By Default its horizontal header only
var excelAplication = new Excel.Application();
excelAplication.Visible = false;
string path2 = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
path2 += "/DataConverter";
try
{
//var excelworkbook = excelAplication.Workbooks.Open(textBox_Excel.Text);
var excelworkbook = excelAplication.Workbooks.Open(path);
var excelworksheet = (Excel.Worksheet)excelworkbook.ActiveSheet;
Boolean stop = false;
Excel.Range range = excelworksheet.UsedRange;
Excel.Range beginCell = range.Cells[1, 1];
Excel.Range endCell = range.Cells[range.Rows.Count, range.Columns.Count];
if (!beginCell.HasFormula)
{
var beginCellRow = range.Find(
"*",
beginCell,
XlFindLookIn.xlFormulas,
XlLookAt.xlPart,
XlSearchOrder.xlByRows,
XlSearchDirection.xlNext,
false);
var beginCellCol = range.Find(
"*",
beginCell,
XlFindLookIn.xlFormulas,
XlLookAt.xlPart,
XlSearchOrder.xlByColumns,
XlSearchDirection.xlNext,
false);
if (null == beginCellRow || null == beginCellCol)
{
stop = true;
}
beginCell = range.Worksheet.Cells[beginCellRow.Row, beginCellCol.Column];
}
if (!endCell.HasFormula)
{
var endCellRow = range.Find(
"*",
endCell,
XlFindLookIn.xlFormulas,
XlLookAt.xlPart,
XlSearchOrder.xlByRows,
XlSearchDirection.xlPrevious,
false);
var endCellCol = range.Find(
"*",
endCell,
XlFindLookIn.xlFormulas,
XlLookAt.xlPart,
XlSearchOrder.xlByColumns,
XlSearchDirection.xlPrevious,
false);
if (endCellRow == null || endCellCol == null)
{
stop = true;
}
endCell = range.Worksheet.Cells[endCellRow.Row, endCellCol.Column];
}
if (endCell == null || beginCell == null)
{
stop = true;
}
if (!stop)
{
Excel.Range finalRng = range.Worksheet.Range[beginCell, endCell];
int endRow = endCell.Row;
int endCol = endCell.Column;
int beginRow = beginCell.Row;
int beginCol = beginCell.Column;
string fileJSN = "";
//Boolean ctrl = false;
Boolean flag = true;
for (int i = beginRow; i < endRow; i++)
{
Dictionary <string, string> js = new Dictionary <string, string>();
for (int j = beginCol; j < endCol; j++)
{
if (range.Cells[i, j] == null || range.Cells[i, j].Value == null)
{
js.Add(range.Cells[1, j].Value.ToString(), " ");
}
else
{
js.Add(range.Cells[1, j].Value.ToString(), range.Cells[i, j].Value.ToString());
}
}
if (i == endRow - 1)
{
fileJSN += JsonConvert.SerializeObject(js);
}
else
{
fileJSN += JsonConvert.SerializeObject(js) + ",";
}
}
//System.IO.File.WriteAllText(path + "/result" + DateTime.Now.ToString(@"MM\_dd\_yyyy_h\_mm") + ".txt", fileJSN);
return(fileJSN);
Marshal.ReleaseComObject(range);
RealeaseCOMObjects(excelworkbook);
RealeaseCOMObjects(excelAplication);
}
else
{
MessageBox.Show("Please insert a Valid Excel: " + path + "is not valid");
}
}
catch (Exception ex)
{
RealeaseCOMObjects(excelAplication);
MessageBox.Show(ex.Message.ToString());
MessageBox.Show("Please insert a Valid Excel Exception: '" + ex.Message + "'");
}
return(null);
}
public void ExceptTransFileExt(DataTable up_dt, DataTable down_dt, int currentPage, bool isFirstTimes, transExcel transExcel)
{
if (transExcel == null)
{
transExcel = new transExcel();
}
DataTable copyDown_dt = down_dt.Copy();
bool isCleanSheet = false;//是否清空没用的Sheet
double sumRowHeight = 0;
if (isFirstTimes)
{
totalPage = 0;
transResult = true;
}
try
{
string partNumberExt = up_dt.Rows[0][1].ToString(); //品号_面次:DEC-21458-00-1A_B
string partNumber = up_dt.Rows[0][1].ToString().Substring(0, 15); //品号:DEC-21458-00
string partDesc = mGetPartData(partNumber, imsapi, sessionContext); //品名:北京现代ADc倒车雷达SMT半成品
string programName = partNumberExt.Replace("_", " ") + " " + up_dt.Rows[1][1].ToString(); //程序名称:DEC-21458-00-1A B V1.4-S1E
string machineGroup = up_dt.Rows[1][1].ToString().Substring(up_dt.Rows[1][1].ToString().IndexOf('-') + 1); //机械别:S1E
string todayDate = DateTime.Now.ToString("yyyy-M-d");
string side = up_dt.Rows[9][1].ToString().ToUpper().Substring(0, 3); //面次:TOP/BOT
int totalPositionCount = 0; //合计点数
#region 创建excel文件
//1,验证目录
if (!Directory.Exists(config.GenerateFolder))
{
Directory.CreateDirectory(config.GenerateFolder);
errorHandler(0, "创建目录:" + config.GenerateFolder);
}
//2,验证文件
string filePath = config.GenerateFolder + partNumber + ".xlsm";
if (!File.Exists(filePath))
{
//ExcelHelper.CreateExcel(filePath);
ExcelHelper.CopyFile(@"D:\Z_files\Template\Z_Template_02.xlsm", filePath);
errorHandler(0, "创建文件:" + filePath);
isCleanSheet = true;
}
#endregion
#region 制Sheet
GC.Collect();
//模板页Sheet
transExcel.xlsBook = transExcel.xlsApp.Workbooks.Open(config.TemplateFolder, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
transExcel.xlsSheet = (Excel.Worksheet)transExcel.xlsBook.ActiveSheet;
//生成文件Sheet
transExcel.xlsBook2 = transExcel.xlsApp.Workbooks.Open(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
transExcel.xlsSheet2 = (Excel.Worksheet)transExcel.xlsBook2.Sheets[1];
//将模板的格式复制过来
transExcel.xlsSheet.Copy(transExcel.xlsSheet2, Type.Missing);
#endregion
#region 填充Sheet
//填充表头部分
transExcel.xlsSheet2 = (Excel.Worksheet)transExcel.xlsBook2.ActiveSheet;
transExcel.xlsSheet2.Cells[4, 2] = machineGroup;
transExcel.xlsSheet2.Cells[5, 3] = programName;
transExcel.xlsSheet2.Cells[6, 3] = partDesc;
transExcel.xlsSheet2.Cells[7, 3] = partNumber;
transExcel.xlsSheet2.Cells[7, 7] = todayDate;
//表身部分
int idx = 9;//表身开始行(这个值是根据原始数据文档出来的)
foreach (DataRow row in down_dt.Rows)
{
string zAxisNumber = row["pos."].ToString(); //Z轴编号
string partNumberSource = row["Part Num"].ToString(); //品号
//料架型号(FUJI8mm*16PH) 要求:1,只要大于"8mm"不需后面的直接设置为"FJ24mm";2,只要Z轴编号为6-A或6-B开头的料架型号为TRAY
string prefixRackType = "FUJI"; //料架型号前缀
string suffixRackType = row["Width"].ToString() + "*" + row["Feed Pitch"].ToString() + "PH"; //料架型号后缀
string valueRackType = row["Width"].ToString().TrimEnd('m');
Match canTransInt = Regex.Match(valueRackType, "\\d");
if (canTransInt.Success)
{
if (Convert.ToInt32(valueRackType) > 8)
{
prefixRackType = "FJ";
suffixRackType = "24mm";
}
}
if (zAxisNumber.ToUpper().Trim().StartsWith("6-A") || zAxisNumber.ToUpper().Trim().StartsWith("6-B"))
{
prefixRackType = "TRAY";
suffixRackType = "";
}
string rackType = prefixRackType + suffixRackType;
//材料规格(这个有点复杂)
string materialSpec = GenerateMaterialSpec(partNumberSource.Trim());
//部品贴装位置,点数
string position = string.Empty;
int positionCount = 0;
GeneratePosition(row["Reference"].ToString(), out position, out positionCount);
totalPositionCount += positionCount;
//自动调衡行高(根据装贴位置数,19个位置一行)
double rowHeight = position.Length < 21 ? 21 : Math.Ceiling((position.Length - Math.Ceiling(position.Length / 20.00)) / 19.00) * 15;
sumRowHeight += rowHeight;
if (rowHeight > Convert.ToDouble(config.MaxRow))
{
errorHandler(2, "设置的最大行高小于单行行高");
}
if (sumRowHeight > Convert.ToDouble(config.MaxRow))
{
transExcel.xlsBook2.Save();
//如果加上当前行后大于设置的最大行高,那么就用迭代
ExceptTransFileExt(up_dt, copyDown_dt, currentPage + 1, false, transExcel);
break;
}
//插入数据
Excel.Range range = (Excel.Range)transExcel.xlsSheet2.Rows[idx, Missing.Value];
range.Rows.Insert(Excel.XlDirection.xlDown, Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
transExcel.xlsSheet2.Cells[idx, 1] = zAxisNumber;
transExcel.xlsSheet2.Cells[idx, 2] = rackType;
transExcel.xlsSheet2.Cells[idx, 3] = partNumberSource;
transExcel.xlsSheet2.Cells[idx, 4] = materialSpec;
transExcel.xlsSheet2.Cells[idx, 5] = position;
transExcel.xlsSheet2.Cells[idx, 8] = positionCount;
//部品贴装位置要合并单元格
ExcelHelper.MerageCell(transExcel.xlsBook2, idx, 5, idx, 7);
Excel.Range range1 = (Excel.Range)transExcel.xlsSheet2.Rows[idx, Missing.Value];
//设置行高
range1.RowHeight = rowHeight;
copyDown_dt.Rows.RemoveAt(0);
idx++;
}
//添加行"以下空白"
if (copyDown_dt == null || copyDown_dt.Rows.Count < 1)
{
Excel.Range range = (Excel.Range)transExcel.xlsSheet2.Rows[idx, Missing.Value];
range.Rows.Insert(Excel.XlDirection.xlDown, Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
transExcel.xlsSheet2.Cells[idx, 2] = "以下空白";
//部品贴装位置要合并单元格
ExcelHelper.MerageCell(transExcel.xlsBook2, idx, 5, idx, 7);
idx++;
}
//总页数
totalPage = totalPage > currentPage ? totalPage : currentPage;
string page = currentPage + "/" + totalPage;
transExcel.xlsSheet2 = (Excel.Worksheet)transExcel.xlsBook2.Sheets[totalPage - currentPage + 1];
//填充点数合并
transExcel.xlsSheet2.Cells[idx + 1, 8] = totalPositionCount;
//填充页数
transExcel.xlsSheet2.Cells[4, 9] = page;
#endregion
#region 改Sheet名称
string sheetName = machineGroup + "-" + side + ((totalPage == 1) ? "" : "(" + currentPage + ")");//Sheet名称
//1,修改Sheet名称之前先将先前同名的删除掉
Excel.Worksheet xlsSheet_clean = null;
for (int i = transExcel.xlsBook2.Sheets.Count; i >= 1; i--)
{
xlsSheet_clean = (Excel.Worksheet)transExcel.xlsBook2.Sheets[i];
if (xlsSheet_clean.Name.Contains(sheetName))
{
errorHandler(0, "清除Sheet:" + xlsSheet_clean.Name);
xlsSheet_clean.Delete();
}
}
transExcel.xlsBook2.Save();
//2,修改Sheet名称
((Excel.Worksheet)transExcel.xlsBook2.Sheets[totalPage - currentPage + 1]).Name = sheetName;
transExcel.xlsBook2.Save();
#endregion
if (isCleanSheet)
{
ExcelHelper.CleanSheet(transExcel.xlsApp, transExcel.xlsBook2);
}
errorHandler(0, sheetName + "生成成功");
}
catch (Exception ex)
{
errorHandler(2, ex.Message);
transResult = false;
}
finally
{
//如果是第一次就要关闭
if (isFirstTimes)
{
transExcel.closeExcel();
}
//transExcel.xlsBook.Close(false, Type.Missing, Type.Missing);
//transExcel.xlsBook2.Close(false, Type.Missing, Type.Missing);
//transExcel.xlsApp.Quit();
//KillSpecialExcel(transExcel.xlsApp);
//System.Runtime.InteropServices.Marshal.ReleaseComObject(transExcel.xlsApp);
//System.Runtime.InteropServices.Marshal.ReleaseComObject(transExcel.xlsBook);
//System.Runtime.InteropServices.Marshal.ReleaseComObject(transExcel.xlsSheet);
//System.Runtime.InteropServices.Marshal.ReleaseComObject(transExcel.xlsBook2);
//System.Runtime.InteropServices.Marshal.ReleaseComObject(transExcel.xlsSheet2);
//transExcel.xlsSheet = null;
//transExcel.xlsBook = null;
//transExcel.xlsApp = null;
//transExcel.xlsSheet2 = null;
//transExcel.xlsBook2 = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
/// <summary>
/// 复制Sheet,填充Sheet
/// </summary>
public bool ExceptTransFile(DataTable Up_dt, DataTable Down_dt, int currentPage, int totalPages)
{
Excel.Application xlsApp = new Excel.Application();
Excel.Workbook xlsBook = null;
Excel.Worksheet xlsSheet = null;
Excel.Workbook xlsBook_trans = null;
Excel.Worksheet xlsSheet_trans = null;
bool isCleanSheet = false;//是否清空没用的Sheet
try
{
string partNumberExt = Up_dt.Rows[0][1].ToString(); //品号_面次:DEC-21458-00-1A_B
string partNumber = Up_dt.Rows[0][1].ToString().Substring(0, 15); //品号:DEC-21458-00
string partDesc = mGetPartData(partNumber, imsapi, sessionContext); //品名:北京现代ADc倒车雷达SMT半成品
string programName = partNumberExt.Replace("_", " ") + " " + Up_dt.Rows[1][1].ToString(); //程序名称:DEC-21458-00-1A B V1.4-S1E
string machineGroup = Up_dt.Rows[1][1].ToString().Substring(Up_dt.Rows[1][1].ToString().IndexOf('-') + 1); //机械别:S1E
string todayDate = DateTime.Now.ToString("yyyy-M-d");
string side = Up_dt.Rows[9][1].ToString().ToUpper().Substring(0, 3); //面次:TOP/BOT
int totalPositionCount = 0; //合计点数
string sheetName = machineGroup + "-" + side + ((totalPages == 1) ? "" : "(" + currentPage + ")"); //Sheet名称
string page = currentPage + "/" + totalPages;
#region 创建excel文件
//1,验证目录
if (!Directory.Exists(config.GenerateFolder))
{
Directory.CreateDirectory(config.GenerateFolder);
errorHandler(0, "创建目录:" + config.GenerateFolder);
}
//2,验证文件
string filePath = config.GenerateFolder + partNumber + ".xlsm";
if (!File.Exists(filePath))
{
ExcelHelper.CopyFile(config.TemplateFolder, filePath);
errorHandler(0, "创建文件:" + filePath);
isCleanSheet = true;
}
#endregion
#region 制Sheet
GC.Collect();
//模板页Sheet
xlsBook = xlsApp.Workbooks.Open(config.TemplateFolder, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
xlsSheet = (Excel.Worksheet)xlsBook.ActiveSheet;
//生成文件Sheet
xlsBook_trans = xlsApp.Workbooks.Open(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
xlsSheet_trans = (Excel.Worksheet)xlsBook_trans.Sheets[1];
//将模板的格式复制过来
xlsSheet.Copy(xlsSheet_trans, Type.Missing);
#endregion
#region 填充Sheet
//填充表头部分
xlsSheet_trans = (Excel.Worksheet)xlsBook_trans.ActiveSheet;
xlsSheet_trans.Cells[4, 2] = machineGroup;
xlsSheet_trans.Cells[4, 9] = page;
xlsSheet_trans.Cells[5, 3] = programName;
xlsSheet_trans.Cells[6, 3] = partDesc;
xlsSheet_trans.Cells[7, 3] = partNumber;
xlsSheet_trans.Cells[7, 7] = todayDate;
//表身部分
int idx = 9;//表身开始行(这个值是根据原始数据文档出来的)
foreach (DataRow row in Down_dt.Rows)
{
string zAxisNumber = row["pos."].ToString(); //Z轴编号
string partNumberSource = row["Part Num"].ToString(); //品号
//料架型号(FUJI8mm*16PH) 要求:1,只要大于"8mm"不需后面的直接设置为"FJ24mm";2,只要Z轴编号为6-A或6-B开头的料架型号为TRAY
string prefixRackType = "FUJI"; //料架型号前缀
string suffixRackType = row["Width"].ToString() + "*" + row["Feed Pitch"].ToString() + "PH"; //料架型号后缀
string valueRackType = row["Width"].ToString().TrimEnd('m');
Match canTransInt = Regex.Match(valueRackType, "\\d");
if (canTransInt.Success)
{
if (Convert.ToInt32(valueRackType) > 8)
{
prefixRackType = "FJ";
suffixRackType = "24mm";
}
}
if (zAxisNumber.ToUpper().Trim().StartsWith("6-A") || zAxisNumber.ToUpper().Trim().StartsWith("6-B"))
{
prefixRackType = "TRAY";
suffixRackType = "";
}
string rackType = prefixRackType + suffixRackType;
//材料规格(这个有点复杂)
string materialSpec = GenerateMaterialSpec(partNumberSource.Trim());
//部品贴装位置,点数
string position = string.Empty;
int positionCount = 0;
GeneratePosition(row["Reference"].ToString(), out position, out positionCount);
totalPositionCount += positionCount;
//插入数据(用固定的模板来做)
xlsSheet_trans.Cells[idx, 1] = zAxisNumber;
xlsSheet_trans.Cells[idx, 2] = rackType;
xlsSheet_trans.Cells[idx, 3] = partNumberSource;
xlsSheet_trans.Cells[idx, 4] = materialSpec;
xlsSheet_trans.Cells[idx, 5] = position;
xlsSheet_trans.Cells[idx, 8] = positionCount;
//部品贴装位置要合并单元格
ExcelHelper.MerageCell(xlsBook_trans, idx, 5, idx, 7);
//自动调衡行高(根据装贴位置数,4个一行)
Excel.Range range1 = (Excel.Range)xlsSheet_trans.Rows[idx, Missing.Value];
range1.RowHeight = position.Length < 21 ? 21 : Math.Ceiling((position.Length - Math.Ceiling(position.Length / 20.00)) / 19.00) * 15;
idx++;
}
//添加行"以下空白"
if (isOver)
{
xlsSheet_trans.Cells[idx, 2] = "以下空白";
//部品贴装位置要合并单元格
ExcelHelper.MerageCell(xlsBook_trans, idx, 5, idx, 7);
idx++;
}
//填充点数合并
xlsSheet_trans.Cells[32, 8] = totalPositionCount;
#endregion
//修改Sheet名称之前先将先前同名的删除掉
Excel.Worksheet xlsSheet_clean = null;
for (int i = xlsBook_trans.Sheets.Count; i >= 1; i--)
{
xlsSheet_clean = (Excel.Worksheet)xlsBook_trans.Sheets[i];
if (xlsSheet_clean.Name.Contains(sheetName))
{
errorHandler(0, "清除Sheet:" + xlsSheet_clean.Name);
xlsSheet_clean.Delete();
}
}
xlsBook_trans.Save();
//修改Sheet名称
((Excel.Worksheet)xlsBook_trans.ActiveSheet).Name = sheetName;
xlsBook_trans.Save();
if (isCleanSheet)
{
ExcelHelper.CleanSheet(xlsApp, xlsBook_trans);
}
errorHandler(0, "生成成功");
return(true);
}
catch (Exception ex)
{
errorHandler(2, ex.Message);
return(false);
}
finally
{
xlsBook.Close(false, Type.Missing, Type.Missing);
xlsBook_trans.Close(false, Type.Missing, Type.Missing);
xlsApp.Quit();
KillSpecialExcel(xlsApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsBook_trans);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsSheet_trans);
xlsSheet = null;
xlsBook = null;
xlsApp = null;
xlsSheet_trans = null;
xlsBook_trans = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
public ParamMapForm(Excel.Range cellRange)
{
this.range = cellRange;
InitializeComponent();
}
public void RealtimeMorningPrint(DataSet dsRealtimeMorning, string savePath)
{
KillProcess();
try
{
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath + @"report\RealtimeMorningInfo.xls",
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 = (Excel.Sheets)m_objBook.Worksheets;
//获取第一个打印页
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
for (int row = 0; row < dsRealtimeMorning.Tables[0].Rows.Count; row++)
{
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 7, 1], (Excel.Range)m_objSheet.Cells[row + 7, 1]);
m_objRange.Value = dsRealtimeMorning.Tables[0].Rows[row][1].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.Font.Size = 10;
for (int column = 3; column < dsRealtimeMorning.Tables[0].Columns.Count; column++)
{
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 7, column - 1], (Excel.Range)m_objSheet.Cells[row + 7, column - 1]);
m_objRange.set_Item(1, 1, dsRealtimeMorning.Tables[0].Rows[row][column].ToString());
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.Font.Size = 10;
}
if (row == dsRealtimeMorning.Tables[0].Rows.Count - 1)
{
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 9, 1], (Excel.Range)m_objSheet.Cells[row + 9, 1]);
m_objRange.Value = "园所信息：";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 12;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 9, 2], (Excel.Range)m_objSheet.Cells[row + 9, 2]);
m_objRange.Value = new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString();
m_objRange.Font.Size = 12;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 10, 1], (Excel.Range)m_objSheet.Cells[row + 10, 1]);
m_objRange.Value = "统计日期：";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 12;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 10, 2], (Excel.Range)m_objSheet.Cells[row + 10, 2]);
m_objRange.Value = DateTime.Now.ToString("yyyy-MM-dd");
m_objRange.Font.Size = 12;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
}
}
m_objBook.SaveAs(savePath, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
}
catch (Exception e)
{
Util.WriteLog(e.Message, Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
/// <summary>
/// 合并单元格，并赋值，对指定WorkSheet操作
/// </summary>
/// <param name="sheetIndex">WorkSheet索引</param>
/// <param name="beginRowIndex">开始行索引</param>
/// <param name="beginColumnIndex">开始列索引</param>
/// <param name="endRowIndex">结束行索引</param>
/// <param name="endColumnIndex">结束列索引</param>
/// <param name="text">合并后Range的值</param>
public void MergeCells(int sheetIndex, int beginRowIndex, int beginColumnIndex, int endRowIndex, int endColumnIndex, string text)
{
if (sheetIndex > this.WorkSheetCount)
{
this.KillExcelProcess();
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);
range = workSheet.get_Range(workSheet.Cells[beginRowIndex, beginColumnIndex], workSheet.Cells[endRowIndex, endColumnIndex]);
range.ClearContents(); //先把Range内容清除，合并才不会出错
range.MergeCells = true;
range.Value = text;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
}
/// <summary>
/// 将指定索引列的数据相同的行合并，对指定WorkSheet操作
/// </summary>
/// <param name="sheetIndex">WorkSheet索引</param>
/// <param name="columnIndex">列索引</param>
/// <param name="beginRowIndex">开始行索引</param>
/// <param name="endRowIndex">结束行索引</param>
public void MergeRows(int sheetIndex, int columnIndex, int beginRowIndex, int endRowIndex)
{
if (sheetIndex > this.WorkSheetCount)
{
this.KillExcelProcess();
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
if (endRowIndex - beginRowIndex < 1)
return;
int beginIndex = beginRowIndex;
int count = 0;
string text1;
string text2;
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);
for (int j = beginRowIndex; j <= endRowIndex; j++)
{
range = (Excel.Range)workSheet.Cells[j, columnIndex];
text1 = range.Text.ToString();
range = (Excel.Range)workSheet.Cells[j + 1, columnIndex];
text2 = range.Text.ToString();
if (text1 == text2)
{
++count;
}
else
{
if (count > 0)
{
this.MergeCells(workSheet, beginIndex, columnIndex, beginIndex + count, columnIndex, text1);
}
beginIndex = j + 1; //设置开始合并行索引
count = 0; //计数器清0
}
}
}
public void MergeExcel(string FileName, C1.Win.C1FlexGrid.C1FlexGrid fg)
{
try
{
oXL = new Excel.Application();
oWB = (Excel._Workbook)(oXL.Workbooks.Open(FileName, 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));
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
oSheet.UsedRange.Cells.Borders.Value = 1;
// Thực hiện Merge
C1.Win.C1FlexGrid.CellRange cr;
object obj;
for (int i = 0; i < fg.Rows.Count; i++)
{
if (fg.Rows[i].Visible)
{
for (int j = 0; j < fg.Cols.Count; j++)
{
if (fg.Cols[j].Visible)
{
cr = fg.GetMergedRange(i, j);
if ((!cr.IsSingleCell && (cr.r1 == i)) && (cr.c1 == j))
{
oRng = oSheet.get_Range(TinhToaDo(cr.r1 + 1, cr.c1 + 1), TinhToaDo(cr.r2 + 1, cr.c2 + 1));
obj = fg[i, j];
oRng.Value2 = string.Empty;
oRng.Merge(Missing.Value);
oRng.Value2 = ReturnValue(obj);
}
}
}
}
}
// Xóa các cột, hàng đã bị ẩn
for (int i = oSheet.UsedRange.Rows.Count - 1; i > 0; i--)
{
oRng = oSheet.get_Range("A" + i.ToString(), Missing.Value);
if ((bool)oRng.EntireRow.Hidden == true)
{
oRng.EntireRow.Delete(Excel.XlDirection.xlToRight);
}
}
for (int i = oSheet.UsedRange.Columns.Count - 1; i > 0; i--)
{
oRng = oSheet.get_Range(TinhToaDo(1, i), Missing.Value);
if ((bool)oRng.EntireColumn.Hidden == true)
{
oRng.EntireColumn.Delete(Excel.XlDirection.xlDown);
}
}
oWB.Save();
oXL.Visible = true;
}
catch (Exception ex)
{
//oXL.Quit();
throw ex;
}
}
/// <summary>
/// 复制行（在指定WorkSheet指定行下面复制指定数量行）
/// </summary>
/// <param name="sheetIndex"></param>
/// <param name="rowIndex"></param>
/// <param name="count"></param>
public void CopyRows(int sheetIndex, int rowIndex, int count)
{
if (sheetIndex > this.WorkSheetCount)
{
this.KillExcelProcess();
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
try
{
workSheet = (Excel.Worksheet)workBook.Worksheets[sheetIndex];
range1 = (Excel.Range)workSheet.Rows[rowIndex, this.missing];
for (int i = 1; i <= count; i++)
{
range2 = (Excel.Range)workSheet.Rows[rowIndex + i, this.missing];
range1.Copy(range2);
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
public void RealtimeMorningPrint(DataSet dsRealtimeInfoStat_Teacher, string savePath)
{
KillProcess();
try
{
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath + @"report\RealtimeMorningInfoStat_Teacher.xls",
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 = (Excel.Sheets)m_objBook.Worksheets;
//获取第一个打印页
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
object[,] objData = null;
if (dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count > 0)
{
objData = new object[dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count, 7];
for (int i = 0; i < dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count; i++)
{
objData[i, 0] = dsRealtimeInfoStat_Teacher.Tables[0].Rows[i][0].ToString();
objData[i, 1] = dsRealtimeInfoStat_Teacher.Tables[0].Rows[i][1].ToString();
objData[i, 2] = dsRealtimeInfoStat_Teacher.Tables[0].Rows[i][2].ToString();
objData[i, 3] = dsRealtimeInfoStat_Teacher.Tables[0].Rows[i][3].ToString();
objData[i, 4] = dsRealtimeInfoStat_Teacher.Tables[0].Rows[i][4].ToString();
objData[i, 5] = dsRealtimeInfoStat_Teacher.Tables[0].Rows[i][5].ToString();
objData[i, 6] = dsRealtimeInfoStat_Teacher.Tables[0].Rows[i][6].ToString();
}
}
m_objRange = m_objSheet.get_Range("A7", m_objOpt);
m_objRange = m_objRange.get_Resize(dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count, 7);
m_objRange.Value = objData;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
m_objRange.Font.Size = 10;
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count + 8, 1], (Excel.Range)m_objSheet.Cells[dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count + 8, 1]);
m_objRange.Value = "园所信息:";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 12;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count + 8, 2], (Excel.Range)m_objSheet.Cells[dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count + 8, 3]);
m_objRange.MergeCells = true;;
m_objRange.Value = " " + new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString();
m_objRange.Font.Size = 12;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count + 9, 1], (Excel.Range)m_objSheet.Cells[dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count + 9, 1]);
m_objRange.Value = "统计日期:";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 12;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count + 9, 2], (Excel.Range)m_objSheet.Cells[dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count + 9, 3]);
m_objRange.MergeCells = true;
m_objRange.Value = " " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
m_objRange.Font.Size = 12;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objBook.SaveAs(savePath, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
}
catch (Exception e)
{
Util.WriteLog(e.Message, Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
/// <summary>
/// 删除列
/// </summary>
/// <param name="sheetIndex"></param>
/// <param name="columnIndex"></param>
/// <param name="count"></param>
public void DeleteColumns(int sheetIndex, int columnIndex, int count)
{
if (sheetIndex > this.WorkSheetCount)
{
this.KillExcelProcess();
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
try
{
workSheet = (Excel.Worksheet)workBook.Worksheets[sheetIndex];
range = (Excel.Range)workSheet.Columns[this.missing, columnIndex];
for (int i = 0; i < count; i++)
{
range.Delete(Excel.XlDirection.xlDown);
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
/// <summary>
/// 将datatable导出excel文件
/// </summary>
/// <param name="dt">需要导出的datatable</param>
/// <param name="AbosultedFilePath">导出文件的绝对路径</param>
/// <returns></returns>
public bool ExportToExcel(System.Data.DataTable dt, string AbosultedFilePath)
{
//检查数据表是否为空，如果为空，则退出
if (dt == null)
{
return(false);
}
//创建Excel应用程序对象，如果未创建成功则退出
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
System.Web.HttpContext.Current.Response.Write("无法创建Excel对象，可能你的电脑未装Excel");
return(false);
}
//创建Excel的工作簿
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1
Excel.Range range = null;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
range = (Excel.Range)worksheet.get_Range("A1", "O1"); //获取表格中第一行
range.Merge(0); //合并第一行
worksheet.Cells[1, 1] = this.selectyear.SelectedValue + "年度报销费用-部门报销费用按月度汇总"; //大标题
range.Font.Size = 22;
range.Font.Bold = true;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.EntireColumn.AutoFit();
range.EntireRow.AutoFit();
//写入标题
for (int i = 0; i < dt.Columns.Count + 1; i++)
{
//写入标题名称
if (i == 0)
{
worksheet.Cells[2, i + 1] = "序号";
}
else
{
worksheet.Cells[2, i + 1] = di[dt.Columns[i - 1].ColumnName];//从第二行的第一格开始写数据
}
//设置标题的样式
range = (Excel.Range)worksheet.Cells[2, i + 1];
range.Font.Bold = true; //粗体
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //居中
range.Interior.ColorIndex = 15;
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null); //背景色
if (i == 0) //序号列宽度自动
{
range.EntireColumn.AutoFit();
}
else
{
//设置单元格的宽度，如果小于9就设置为9，如果大于。则设置为自动
if (range.EntireColumn.ColumnWidth <= 8.5)
{
range.EntireColumn.ColumnWidth = 8.5;
}
else
{
range.EntireColumn.AutoFit();//自动设置列宽
}
}
}
//写入DataTable中数据的内容
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int c = 0; c < dt.Columns.Count + 1; c++)
{
range = (Excel.Range)worksheet.Cells[r + 3, c + 1];
//写入内容
if (c == 0)
{
if (r == dt.Rows.Count - 1)
{
worksheet.Cells[r + 3, c + 1] = "";
}
else
{
worksheet.Cells[r + 3, c + 1] = (r + 1).ToString(); //得到序号
}
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //居中
}
else if (c == 1)
{
worksheet.Cells[r + 3, c + 1] = dt.Rows[r][c - 1].ToString();
}
else
{
worksheet.Cells[r + 3, c + 1] = dt.Rows[r][c - 1].ToString().Split(',')[0];
range.NumberFormat = "#,##0.00";
}
//设置样式
range.Font.Size = 9; //字体大小
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null); //加边框
//设置单元格的宽度，如果小于8.5就设置为8.5，如果大于。则设置为自动
if (c == 0) //序号列宽度设为自动
{
range.EntireColumn.AutoFit();
}
else
{
if (range.EntireColumn.ColumnWidth <= 8.5)
{
range.EntireColumn.ColumnWidth = 8.5;
}
else
{
range.EntireColumn.AutoFit();//自动设置列宽
}
}
}
if (r == dt.Rows.Count - 1)
{
range = (Excel.Range)worksheet.get_Range("A" + (r + 3).ToString(), "B" + (r + 3).ToString());
range.Merge(0);
range.Value = "合计";
range.Font.Size = 9;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//居中
range.EntireColumn.AutoFit();
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
System.Windows.Forms.Application.DoEvents();
}
try
{
workbook.Saved = true;
workbook.SaveCopyAs(AbosultedFilePath);
}
catch (Exception ex)
{
System.Web.HttpContext.Current.Response.Write("导出文件时出错，文件可能正被打开！\n" + ex.ToString());
return(false);
}
workbook.Close();
if (xlApp != null)
{
xlApp.Workbooks.Close();
xlApp.Quit();
int generation = System.GC.GetGeneration(xlApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
System.GC.Collect(generation);
}
GC.Collect();//强行销毁
#region 强行杀死最近打开的Excel进程
System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL");
System.DateTime startTime = new DateTime();
int m, killID = 0;
for (m = 0; m < excelProc.Length; m++)
{
if (startTime < excelProc[m].StartTime)
{
startTime = excelProc[m].StartTime;
killID = m;
}
}
if (excelProc[killID].HasExited == false)
{
excelProc[killID].Kill();
}
#endregion
return(true);
}
/// <summary>
/// 合并单元格，并赋值，对指定WorkSheet操作
/// </summary>
/// <param name="beginRowIndex">开始行索引</param>
/// <param name="beginColumnIndex">开始列索引</param>
/// <param name="endRowIndex">结束行索引</param>
/// <param name="endColumnIndex">结束列索引</param>
/// <param name="text">合并后Range的值</param>
private void MergeCells(Excel.Worksheet sheet, int beginRowIndex, int beginColumnIndex, int endRowIndex, int endColumnIndex, string text)
{
if (sheet == null)
return;
range = sheet.get_Range(sheet.Cells[beginRowIndex, beginColumnIndex], sheet.Cells[endRowIndex, endColumnIndex]);
range.ClearContents(); //先把Range内容清除，合并才不会出错
range.MergeCells = true;
range.Value = text;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
}
public void ExportToExcel(System.Data.DataTable dt)
{
if (dt == null)
{
return;
}
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return;
}
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
Excel.Range range = null;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
//写入标题
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
range = (Excel.Range)worksheet.Cells[1, i + 1];
//range.Interior.ColorIndex = 15;//背景颜色
range.Font.Bold = true; //粗体
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //居中
//加边框
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
//range.ColumnWidth = 4.63;//设置列宽
//range.EntireColumn.AutoFit();//自动调整列宽
//r1.EntireRow.AutoFit();//自动调整行高
}
//写入内容
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i];
range = (Excel.Range)worksheet.Cells[r + 2, i + 1];
range.Font.Size = 9;//字体大小
//加边框
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
range.EntireColumn.AutoFit();//自动调整列宽
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
System.Windows.Forms.Application.DoEvents();
}
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
if (dt.Columns.Count > 1)
{
range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
}
try
{
workbook.Saved = true;
workbook.SaveCopyAs("C:\\datatable" + "" + ".xls");
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
xlApp.Quit();
GC.Collect();//强行销毁
//这个是从服务器中下载文件,(请参考我另外一个文章)
//参考网址http://www.cnblogs.com/ghostljj/archive/2007/01/24/629293.html
//BIClass.BusinessLogic.Util.ResponseFile(Page.Request, Page.Response, "ReportToExcel.xls"
// , System.Web.HttpRuntime.AppDomainAppPath + "XMLFiles\\EduceWordFiles\\" + this.Context.User.Identity.Name + ".xls", 1024000);
}
/// <summary>
/// 将DataTable数据写入Excel文件（自动分页，并指定要合并的列索引）
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="rows">每个WorkSheet写入多少行数据</param>
/// <param name="top">表格数据起始行索引</param>
/// <param name="left">表格数据起始列索引</param>
/// <param name="mergeColumnIndex">DataTable中要合并相同行的列索引，从0开始</param>
public void DataTableToExcel(DataTable dt, int rows, int top, int left, int mergeColumnIndex)
{
int rowCount = dt.Rows.Count; //源DataTable行数
int colCount = dt.Columns.Count; //源DataTable列数
sheetCount = this.GetSheetCount(rowCount, rows); //WorkSheet个数
// StringBuilder sb;
//复制sheetCount-1个WorkSheet对象
for (int i = 1; i < sheetCount; i++)
{
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
workSheet.Copy(missing, workBook.Worksheets[i]);
}
for (int i = 1; i <= sheetCount; i++)
{
int startRow = (i - 1) * rows; //记录起始行索引
int endRow = i * rows; //记录结束行索引
//若是最后一个WorkSheet，那么记录结束行索引为源DataTable行数
if (i == sheetCount)
endRow = rowCount;
//获取要写入数据的WorkSheet对象，并重命名
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
workSheet.Name = sheetPrefixName + "-" + i.ToString();
//将dt中的数据写入WorkSheet
// for(int j=0;j<endRow-startRow;j++)
// {
// for(int k=0;k<colCount;k++)
// {
// workSheet.Cells[top + j,left + k] = dt.Rows[startRow + j][k].ToString();
// }
// }
//利用二维数组批量写入
int row = endRow - startRow;
string[,] ss = new string[row, colCount];
for (int j = 0; j < row; j++)
{
for (int k = 0; k < colCount; k++)
{
ss[j, k] = dt.Rows[startRow + j][k].ToString();
}
}
range = (Excel.Range)workSheet.Cells[top, left];
range = range.get_Resize(row, colCount);
range.Value = ss;
//合并相同行
this.MergeRows(workSheet, left + mergeColumnIndex, top, rows);
}
}
public SubTitleTable(int SBCol, int col, Excel.Range range1)
{
this.BCol = SBCol;
this.ECol = col;
this.range1 = range1;
}
}//end ArrayToExcel
/// <summary>
/// 将二维数组数据写入Excel文件（自动分页，并指定要合并的列索引）
/// </summary>
/// <param name="arr">二维数组</param>
/// <param name="rows">每个WorkSheet写入多少行数据</param>
/// <param name="top">行索引</param>
/// <param name="left">列索引</param>
/// <param name="mergeColumnIndex">数组的二维索引，相当于DataTable的列索引，索引从0开始</param>
public void ArrayToExcel(string[,] arr, int rows, int top, int left, int mergeColumnIndex)
{
int rowCount = arr.GetLength(0); //二维数组行数（一维长度）
int colCount = arr.GetLength(1); //二维数据列数（二维长度）
sheetCount = this.GetSheetCount(rowCount, rows); //WorkSheet个数
//复制sheetCount-1个WorkSheet对象
for (int i = 1; i < sheetCount; i++)
{
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
workSheet.Copy(missing, workBook.Worksheets[i]);
}
//将二维数组数据写入Excel
for (int i = sheetCount; i >= 1; i--)
{
int startRow = (i - 1) * rows; //记录起始行索引
int endRow = i * rows; //记录结束行索引
//若是最后一个WorkSheet，那么记录结束行索引为源DataTable行数
if (i == sheetCount)
endRow = rowCount;
//获取要写入数据的WorkSheet对象，并重命名
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
workSheet.Name = sheetPrefixName + "-" + i.ToString();
//将二维数组中的数据写入WorkSheet
for (int j = 0; j < endRow - startRow; j++)
{
for (int k = 0; k < colCount; k++)
{
workSheet.Cells[top + j, left + k] = arr[startRow + j, k];
}
}
//利用二维数组批量写入
int row = endRow - startRow;
string[,] ss = new string[row, colCount];
for (int j = 0; j < row; j++)
{
for (int k = 0; k < colCount; k++)
{
ss[j, k] = arr[startRow + j, k];
}
}
range = (Excel.Range)workSheet.Cells[top, left];
range = range.get_Resize(row, colCount);
range.Value = ss;
//合并相同行
this.MergeRows(workSheet, left + mergeColumnIndex, top, rows);
}
}//end ArrayToExcel
private void btnExport_Click(object sender, EventArgs e)
{
try
{
Excel.Application myExcel = new Excel.ApplicationClass();
//设置不显示
myExcel.ScreenUpdating = false;
//添加工作簿
myExcel.Workbooks.Add(true);
//设置工作表
Excel.Worksheet mySheet = (Excel.Worksheet)myExcel.ActiveWorkbook.ActiveSheet;
mySheet.Name = "TrasenHis";
//设置标题
Excel.Range myTitle = mySheet.get_Range(mySheet.Cells[1, 1], mySheet.Cells[1, dgvMzzffp.Columns.Count]);
myTitle.Merge(true);
if (rdoSk.Checked == true)
{
myTitle.Value2 = this.Text + "(" + rdoSk.Text + ")";
}
else
{
myTitle.Value2 = this.Text + "(" + rdoJk.Text + ")";
}
myTitle.Font.Name = "黑体";
myTitle.Font.Size = 20;
myTitle.Font.Bold = true;
myTitle.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
myTitle.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
//设置条件
Excel.Range myNote = mySheet.get_Range(mySheet.Cells[2, 1], mySheet.Cells[2, dgvMzzffp.Columns.Count]);
myNote.Merge(true);
myNote.Value2 = dateTimePickerBegin.Value.ToLongDateString() + " " + dateTimePickerBegin.Value.ToLongTimeString() + " —— " + dateTimePickerEnd.Value.ToLongDateString() + " " + dateTimePickerEnd.Value.ToLongTimeString();
myNote.Font.Name = "宋体";
myNote.Font.Size = 12;
myNote.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
myNote.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
//写入表头
for (int i = 0; i < dgvMzzffp.Columns.Count; i++)
{
myExcel.Cells[3, i + 1] = dgvMzzffp.Columns[i].HeaderText;
}
//设置表头
Excel.Range myHeader = mySheet.get_Range(mySheet.Cells[3, 1], mySheet.Cells[3, dgvMzzffp.Columns.Count]);
myHeader.Font.Size = 12;
myHeader.Font.Bold = true;
myHeader.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//写入数据
for (int i = 0; i < dgvMzzffp.Rows.Count; i++)
{
for (int j = 0; j < dgvMzzffp.Columns.Count; j++)
{
myExcel.Cells[i + 4, j + 1] = dgvMzzffp[j, i].Value;
}
}
//设置表格
Excel.Range myTable = mySheet.get_Range(mySheet.Cells[3, 1], mySheet.Cells[dgvMzzffp.Rows.Count + 3, dgvMzzffp.Columns.Count]);
myTable.Columns.AutoFit();
myTable.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
myTable.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
myTable.Borders.Weight = Excel.XlBorderWeight.xlMedium;
//设置显示
myExcel.Visible = true;
myExcel.ScreenUpdating = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// 将数据填充到Excel工作表的单元格中
/// </summary>
/// <param name="startRange"></param>
/// <param name="rowCount"></param>
/// <param name="colCount"></param>
/// <param name="values"></param>
private void AddExcelRows(string startRange, int rowCount, int colCount, object values)
{
_range = _sheet.get_Range(startRange, _optionalValue);
_range = _range.get_Resize(rowCount, colCount);
_range.set_Value(_optionalValue, values);
}
public static List <CarDetails> ParseCatalogue_StoredFile(string FileName)
{
Excel.Application ExcelObj = null;
Excel.Workbook theWorkbook = null;
List <CarDetails> returnDetails = new List <CarDetails>();
try {
ExcelObj = new Excel.Application();
theWorkbook = ExcelObj.Workbooks.Open(FileName, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true);
Excel.Sheets sheets = theWorkbook.Worksheets;
Excel.Worksheet catalogueWorksheet = (Excel.Worksheet)sheets.get_Item(1);
const int C_COL_NUM__LOT = 2; // B
const int C_COL_NUM__MAKE = 4; // D
const int C_COL_NUM__MODEL = 6; // F
const int C_COL_NUM__TYPE = 7; // G
const int C_COL_NUM__REGISTERED = 8; // H
const int C_COL_NUM__FUEL = 10; // J
const int C_COL_NUM__TRANS = 11; // K
const int C_COL_NUM__DOORS = 13; // M
const int C_COL_NUM__MILES = 14; // N
Excel.Range range = catalogueWorksheet.get_Range("A1", "R50");
System.Array myvalues = (System.Array)range.Cells.Value;
// loop through each depot row
for (int rowIndex = 1; rowIndex <= range.Cells.Rows.Count; rowIndex++)
{
//Console.WriteLine("**********************************");
//Console.WriteLine("MAKE:" + (myvalues.GetValue(rowIndex, C_COL_NUM__MAKE) ?? "").ToString().Trim());
//Console.WriteLine("MODEL:" + (myvalues.GetValue(rowIndex, C_COL_NUM__MODEL) ?? "").ToString().Trim());
//Console.WriteLine("TYPE:" + (myvalues.GetValue(rowIndex, C_COL_NUM__TYPE) ?? "").ToString().Trim());
//Console.WriteLine("REGDATE:" + (myvalues.GetValue(rowIndex, C_COL_NUM__REGISTERED) ?? "").ToString().Trim());
//Console.WriteLine("FUEL:" + (myvalues.GetValue(rowIndex, C_COL_NUM__FUEL) ?? "").ToString().Trim());
//Console.WriteLine("TRANS:" + (myvalues.GetValue(rowIndex, C_COL_NUM__TRANS) ?? "").ToString().Trim());
//Console.WriteLine("DOORS:" + (myvalues.GetValue(rowIndex, C_COL_NUM__DOORS) ?? "").ToString().Trim());
//Console.WriteLine("MILES:" + (myvalues.GetValue(rowIndex, C_COL_NUM__MILES) ?? "").ToString().Trim());
//Console.WriteLine("**********************************");
CarDetails newCar = new CarDetails();
newCar.FromCatalogue = "NEWARK";
newCar.Lot_Number = (myvalues.GetValue(rowIndex, C_COL_NUM__LOT) ?? "").ToString().Trim();
newCar.Make = (myvalues.GetValue(rowIndex, C_COL_NUM__MAKE) ?? "").ToString().Trim();
newCar.Model = (myvalues.GetValue(rowIndex, C_COL_NUM__MODEL) ?? "").ToString().Trim();
newCar.Type = (myvalues.GetValue(rowIndex, C_COL_NUM__TYPE) ?? "").ToString().Trim();
DateTime regDate;
if (DateTime.TryParse((myvalues.GetValue(rowIndex, C_COL_NUM__REGISTERED) ?? "").ToString().Trim(), out regDate))
{
newCar.RegDate = regDate;
} // end if
newCar.Fuel = (myvalues.GetValue(rowIndex, C_COL_NUM__FUEL) ?? "").ToString().Trim();
newCar.Trans = (myvalues.GetValue(rowIndex, C_COL_NUM__TRANS) ?? "").ToString().Trim();
newCar.Doors = (myvalues.GetValue(rowIndex, C_COL_NUM__DOORS) ?? "").ToString().Trim();
newCar.Mileage = (myvalues.GetValue(rowIndex, C_COL_NUM__MILES) ?? "").ToString().Trim();
returnDetails.Add(newCar);
} // end for
if (ExcelObj != null)
{
ExcelObj.Visible = false;
ExcelObj.UserControl = false;
} // end if
if (theWorkbook != null)
{
// Close the document and avoid user prompts to save if our method failed.
theWorkbook.Close(false, null, null);
ExcelObj.Workbooks.Close();
} // end if
}
finally
{
if (theWorkbook != null)
{
Marshal.ReleaseComObject(theWorkbook);
}
if (ExcelObj != null)
{
ExcelObj.Quit();
}
if (ExcelObj != null)
{
Marshal.ReleaseComObject(ExcelObj);
}
theWorkbook = null;
ExcelObj = null;
GC.Collect();
}
return(returnDetails);
}
/// <summary>
/// 合并单元格，并赋值，对每个WorkSheet操作
/// </summary>
/// <param name="beginRowIndex">开始行索引</param>
/// <param name="beginColumnIndex">开始列索引</param>
/// <param name="endRowIndex">结束行索引</param>
/// <param name="endColumnIndex">结束列索引</param>
/// <param name="text">合并后Range的值</param>
public void MergeCells(int beginRowIndex, int beginColumnIndex, int endRowIndex, int endColumnIndex, string text)
{
for (int i = 1; i <= this.WorkSheetCount; i++)
{
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
range = workSheet.get_Range(workSheet.Cells[beginRowIndex, beginColumnIndex], workSheet.Cells[endRowIndex, endColumnIndex]);
range.ClearContents(); //先把Range内容清除，合并才不会出错
range.MergeCells = true;
range.Value = text;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
}
}
private void butexcel_Click(object sender, EventArgs e)
{
if (this.dataGridView1.DataSource == null)
{
return;
}
try
{
DataTable tb = (DataTable)this.dataGridView1.DataSource;
// 创建Excel对象 --LeeWenjie 2006-11-29
Excel.Application xlApp = new Excel.ApplicationClass();
if (xlApp == null)
{
MessageBox.Show("Excel无法启动");
return;
}
// 创建Excel工作薄
Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[1];
// 列索引，行索引，总列数，总行数
int colIndex = 0;
int RowIndex = 0;
int colCount = 0;
int RowCount = tb.Rows.Count;
for (int i = 0; i <= dataGridView1.ColumnCount - 1; i++)
{
if (dataGridView1.Columns[i].Visible == true)
{
colCount = colCount + 1;
}
}
// 设置标题
Excel.Range range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, colCount]);
range.MergeCells = true;
xlApp.ActiveCell.FormulaR1C1 = this.Text;
xlApp.ActiveCell.Font.Size = 20;
xlApp.ActiveCell.Font.Bold = true;
xlApp.ActiveCell.HorizontalAlignment = Excel.Constants.xlCenter;
// 创建缓存数据
object[,] objData = new object[RowCount + 1, colCount + 1];
// 获取列标题
for (int i = 0; i <= dataGridView1.ColumnCount - 1; i++)
{
if (dataGridView1.Columns[i].Visible == true)
{
objData[0, colIndex++] = dataGridView1.Columns[i].HeaderText;
}
}
// 获取数据
for (int i = 0; i <= tb.Rows.Count - 1; i++)
{
colIndex = 0;
for (int j = 0; j <= dataGridView1.ColumnCount - 1; j++)
{
if (dataGridView1.Columns[j].Visible == true)
{
if (dataGridView1.Columns[j].HeaderText == "门诊号" || dataGridView1.Columns[j].HeaderText == "身份证号" || dataGridView1.Columns[j].HeaderText == "卡号")
{
objData[i + 1, colIndex++] = "'" + tb.Rows[i][j].ToString();
}
else
{
objData[i + 1, colIndex++] = "" + tb.Rows[i][j].ToString();
}
}
}
Application.DoEvents();
}
// 写入Excel
range = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]);
range.Value2 = objData;
//
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Borders.LineStyle = 1;
//设置报表表格为最适应宽度
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Select();
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Columns.AutoFit();
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Font.Size = 9;
xlApp.Visible = true;
}
catch (System.Exception err)
{
MessageBox.Show(err.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// 将指定索引列的数据相同的行合并，对每个WorkSheet操作
/// </summary>
/// <param name="columnIndex">列索引</param>
/// <param name="beginRowIndex">开始行索引</param>
/// <param name="endRowIndex">结束行索引</param>
public void MergeRows(int columnIndex, int beginRowIndex, int endRowIndex)
{
if (endRowIndex - beginRowIndex < 1)
return;
for (int i = 1; i <= this.WorkSheetCount; i++)
{
int beginIndex = beginRowIndex;
int count = 0;
string text1;
string text2;
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
for (int j = beginRowIndex; j <= endRowIndex; j++)
{
range = (Excel.Range)workSheet.Cells[j, columnIndex];
text1 = range.Text.ToString();
range = (Excel.Range)workSheet.Cells[j + 1, columnIndex];
text2 = range.Text.ToString();
if (text1 == text2)
{
++count;
}
else
{
if (count > 0)
{
this.MergeCells(workSheet, beginIndex, columnIndex, beginIndex + count, columnIndex, text1);
}
beginIndex = j + 1; //设置开始合并行索引
count = 0; //计数器清0
}
}
}
}
/// <summary>
/// 将DataTable数据导出到Excel表
/// </summary>
/// <param name="tmpDataTable">要导出的DataTable</param>
/// <param name="strFileName">Excel的保存路径及名称</param>
public void DataTabletoExcel(System.Data.DataTable tmpDataTable, string strFileName)
{
if (tmpDataTable == null)
{
return;
}
long rowNum = tmpDataTable.Rows.Count; //行数
int columnNum = tmpDataTable.Columns.Count; //列数
Excel.Application m_xlApp = new Excel.Application();
m_xlApp.DisplayAlerts = true; //不显示更改提示
m_xlApp.Visible = true; //false;//
Excel.Workbooks workbooks = m_xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
//worksheet.SetBackgroundPicture("d:\\26.jpg");
try
{
//查询条件
string rq = "日期:" + this.dtpBegin.Value.ToString() + " 到 " + this.dtpEnd.Value.ToString();
string ks = "";
string swhere = rq + ks;
int SumColCount = tmpDataTable.Columns.Count;
//for (int j = 0; j < tmpDataTable.Columns.Count; j++)
//{
// if (this.dataGridView1.Columns[j].Visible)
// {
// SumColCount = SumColCount + 1;
// m_xlApp.Cells[5, SumColCount] = "" + tmpDataTable.Columns[j].Caption;
// }
//}
//报表名称
string ss = InstanceForm.BCurrentDept.DeptName + " " + "科室收入";
m_xlApp.Cells[1, 1] = ss;
m_xlApp.get_Range(m_xlApp.Cells[1, 1], m_xlApp.Cells[1, SumColCount]).Font.Bold = true;
m_xlApp.get_Range(m_xlApp.Cells[1, 1], m_xlApp.Cells[1, SumColCount]).Font.Size = 16;
//报表名称跨行居中
m_xlApp.get_Range(m_xlApp.Cells[1, 1], m_xlApp.Cells[1, SumColCount]).Select();
m_xlApp.get_Range(m_xlApp.Cells[1, 1], m_xlApp.Cells[1, SumColCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;
//报表条件
m_xlApp.Cells[3, 1] = swhere.Trim();
m_xlApp.get_Range(m_xlApp.Cells[3, 1], m_xlApp.Cells[3, SumColCount]).Font.Size = 10;
m_xlApp.get_Range(m_xlApp.Cells[3, 1], m_xlApp.Cells[3, SumColCount]).Select();
m_xlApp.get_Range(m_xlApp.Cells[3, 1], m_xlApp.Cells[5, SumColCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;
if (rowNum > 65536) //单张Excel表格最大行数
{
long pageRows = 65535; //定义每页显示的行数,行数必须小于65536
int scount = (int)(rowNum / pageRows); //导出数据生成的表单数
if (scount * pageRows < rowNum) //当总行数不被pageRows整除时，经过四舍五入可能页数不准
{
scount = scount + 1;
}
for (int sc = 1; sc <= scount; sc++)
{
if (sc > 1)
{
object missing = System.Reflection.Missing.Value;
worksheet = (Excel.Worksheet)workbook.Worksheets.Add(
missing, missing, missing, missing); //添加一个sheet
}
else
{
worksheet = (Excel.Worksheet)workbook.Worksheets[sc];//取得sheet1
}
string[,] datas = new string[pageRows + 1, columnNum];
for (int i = 0; i < columnNum; i++) //写入字段
{
datas[0, i] = tmpDataTable.Columns[i].Caption; //表头信息
}
Excel.Range range = worksheet.get_Range(worksheet.Cells[5, 1], worksheet.Cells[5, columnNum]);
range.Interior.ColorIndex = 15;//15代表灰色
range.Font.Bold = true;
range.Font.Size = 9;
int init = int.Parse(((sc - 1) * pageRows).ToString());
int r = 0;
int index = 0;
int result;
if (pageRows * sc >= rowNum)
{
result = (int)rowNum;
}
else
{
result = int.Parse((pageRows * sc).ToString());
}
for (r = init; r < result; r++)
{
index = index + 1;
for (int i = 0; i < columnNum; i++)
{
object obj = tmpDataTable.Rows[r][tmpDataTable.Columns[i].ToString()];
datas[index, i] = obj == null ? "" : "" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式
}
System.Windows.Forms.Application.DoEvents();
//添加进度条
}
Excel.Range fchR = worksheet.get_Range(worksheet.Cells[5, 1], worksheet.Cells[index + 5, columnNum]);
fchR.Value2 = datas;
worksheet.Columns.EntireColumn.AutoFit(); //列宽自适应。
m_xlApp.WindowState = Excel.XlWindowState.xlMaximized; //Sheet表最大化
range = worksheet.get_Range(worksheet.Cells[5, 1], worksheet.Cells[index + 5, columnNum]);
//range.Interior.ColorIndex = 15;//15代表灰色
range.Font.Size = 9;
range.RowHeight = 14.25;
range.Borders.LineStyle = 1;
range.HorizontalAlignment = 1;
}
}
else
{
string[,] datas = new string[rowNum + 1, columnNum];
for (int i = 0; i < columnNum; i++) //写入字段
{
datas[0, i] = tmpDataTable.Columns[i].Caption;
}
Excel.Range range = worksheet.get_Range(worksheet.Cells[5, 1], worksheet.Cells[5, columnNum]);
range.Interior.ColorIndex = 15;//15代表灰色
range.Font.Bold = true;
range.Font.Size = 9;
int r = 0;
for (r = 0; r < rowNum; r++)
{
for (int i = 0; i < columnNum; i++)
{
object obj = tmpDataTable.Rows[r][tmpDataTable.Columns[i].ToString()];
datas[r + 1, i] = obj == null ? "" : "" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式
}
System.Windows.Forms.Application.DoEvents();
//添加进度条
}
Excel.Range fchR = worksheet.get_Range(worksheet.Cells[5, 1], worksheet.Cells[rowNum + 5, columnNum]);
fchR.Value2 = datas;
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
m_xlApp.WindowState = Excel.XlWindowState.xlMaximized;
range = worksheet.get_Range(worksheet.Cells[5, 1], worksheet.Cells[rowNum + 5, columnNum]);
//range.Interior.ColorIndex = 15;//15代表灰色
range.Font.Size = 9;
range.RowHeight = 14.25;
range.Borders.LineStyle = 1;
range.HorizontalAlignment = 1;
}
//workbook.Saved = true;
// workbook.SaveCopyAs(strFileName);
}
catch (Exception ex)
{
MessageBox.Show("导出异常：" + ex.Message, "导出异常", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
finally
{
if (workbook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
}
if (m_xlApp != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp);
m_xlApp = null;
//xlApp.Quit();
}
GC.Collect();
//EndReport();
}
}
/// <summary>
/// 插行（在指定行上面插入指定数量行）
/// </summary>
/// <param name="rowIndex"></param>
/// <param name="count"></param>
public void InsertRows(int rowIndex, int count)
{
try
{
for (int n = 1; n <= this.WorkSheetCount; n++)
{
workSheet = (Excel.Worksheet)workBook.Worksheets[n];
range = (Excel.Range)workSheet.Rows[rowIndex, this.missing];
for (int i = 0; i < count; i++)
{
range.Insert(Excel.XlDirection.xlDown);
}
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
private void btnPrint_Click(object sender, EventArgs e)
{
try
{
DataTable tb = (DataTable)this.dataGridView1.DataSource;
// 创建Excel对象
Excel.Application xlApp = new Excel.ApplicationClass();
if (xlApp == null)
{
MessageBox.Show("Excel无法启动");
return;
}
// 创建Excel工作薄
Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[1];
// 列索引，行索引，总列数，总行数
int colIndex = 0;
int RowIndex = 0;
int colCount = 0;
int RowCount = tb.Rows.Count;
for (int i = 0; i <= tb.Columns.Count - 1; i++)
{
if (dataGridView1.Columns[tb.Columns[i].ColumnName].Width > 0)
{
colCount = colCount + 1;
}
}
// 设置标题
Excel.Range range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, colCount]);
range.MergeCells = true;
xlApp.ActiveCell.FormulaR1C1 = this.Text;
xlApp.ActiveCell.Font.Size = 20;
xlApp.ActiveCell.Font.Bold = true;
xlApp.ActiveCell.HorizontalAlignment = Excel.Constants.xlCenter;
//查询条件
string bz = "";
bz = "累计DDD数:" + txtDDD.Text + " 总出院人数:" + txtoutPatient.Text + " 平均住院天数:" + txtAvgOutpatient.Text +
" 同期收治患者人天数:" + txtpatientCount.Text;
string swhere = "抗菌药物使用强度:" + txtqiangdu.Text +
"使用抗菌药物出院总人数:" + txtkjywrc.Text + " 抗菌药物使用率:" + txtksssyl.Text;
// 设置条件
Excel.Range rangeT = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, colCount]);
rangeT.MergeCells = true;
object[,] objDataT = new object[1, 1];
range = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]);
objDataT[0, 0] = bz;
rangeT.Value2 = objDataT;
rangeT = xlSheet.get_Range(xlApp.Cells[3, 1], xlApp.Cells[3, colCount]);
rangeT.MergeCells = true;
object[,] objDataT2 = new object[1, 1];
objDataT2[0, 0] = swhere;
rangeT.Value2 = objDataT2;
// 创建缓存数据
object[,] objData = new object[RowCount + 1, colCount + 1];
// 获取列标题
for (int i = 0; i <= tb.Columns.Count - 1; i++)
{
if (dataGridView1.Columns[tb.Columns[i].ColumnName].Width > 0)
{
objData[0, colIndex++] = dataGridView1.Columns[tb.Columns[i].ColumnName].HeaderText;
}
}
// 获取数据
for (int i = 0; i <= tb.Rows.Count - 1; i++)
{
colIndex = 0;
for (int j = 0; j <= tb.Columns.Count - 1; j++)
{
if (dataGridView1.Columns[tb.Columns[j].ColumnName].Width > 0)
{
objData[i + 1, colIndex++] = "" + tb.Rows[i][j].ToString();
}
}
Application.DoEvents();
}
// 写入Excel
range = xlSheet.get_Range(xlApp.Cells[4, 1], xlApp.Cells[RowCount + 4, colCount]);
range.Value2 = objData;
//
xlApp.get_Range(xlApp.Cells[4, 1], xlApp.Cells[RowCount + 4, colCount]).Borders.LineStyle = 1;
//设置报表表格为最适应宽度
xlApp.get_Range(xlApp.Cells[4, 1], xlApp.Cells[RowCount + 4, colCount]).Select();
xlApp.get_Range(xlApp.Cells[4, 1], xlApp.Cells[RowCount + 4, colCount]).Columns.AutoFit();
xlApp.get_Range(xlApp.Cells[4, 1], xlApp.Cells[RowCount + 4, colCount]).Font.Size = 9;
xlApp.Visible = true;
}
catch (System.Exception err)
{
MessageBox.Show(err.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
this.Cursor = Cursors.Arrow;
}
}
/// <summary>
/// 复制行（在指定行下面复制指定数量行）
/// </summary>
/// <param name="rowIndex"></param>
/// <param name="count"></param>
public void CopyRows(int rowIndex, int count)
{
try
{
for (int n = 1; n <= this.WorkSheetCount; n++)
{
workSheet = (Excel.Worksheet)workBook.Worksheets[n];
range1 = (Excel.Range)workSheet.Rows[rowIndex, this.missing];
for (int i = 1; i <= count; i++)
{
range2 = (Excel.Range)workSheet.Rows[rowIndex + i, this.missing];
range1.Copy(range2);
}
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
public void FinanceStatPrint(DataTable data, string className, DateTime date, string savePath)
{
DataTable dtPresents = new FinanInfoDataAccess().GetStudentPresents(date, className);
if (dtPresents == null || dtPresents.Rows.Count == 0)
{
throw new Exception("没有要使用的数据！");
}
else
{
data.Columns.AddRange(new DataColumn[] { new DataColumn("小计", Type.GetType("System.Double")),
new DataColumn("stuPresent", Type.GetType("System.String")),
new DataColumn("stuAbsent", Type.GetType("System.String")) });
if (dtPresents.Rows.Count != data.Rows.Count)
{
throw new Exception("检测到数据完整性错误，请尝试重新生成数据！");
}
else
{
for (int i = 0; i < dtPresents.Rows.Count; i++)
{
data.Rows[i]["小计"] = 0;
data.Rows[i]["stuPresent"] = dtPresents.Rows[i]["times"];
data.Rows[i]["stuAbsent"] = dtPresents.Rows[i]["times_abs"];
}
}
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath + @"report\FinanceStat.xls",
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 = (Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
object[,] objData = new object[data.Rows.Count + 1, data.Columns.Count + 4];
for (int row = 0; row < data.Rows.Count; row++)
{
objData[row, 0] = row + 1;
for (int column = 0; column < data.Columns.Count - 3; column++)
{
if (column <= 2)
{
objData[row, column + 1] = data.Rows[row][column];
}
else if (column == 3)
{
objData[row, column + 1] = data.Rows[row][data.Columns.Count - 2];
objData[row, column + 2] = data.Rows[row][data.Columns.Count - 1];
double temp = Convert.ToDouble(data.Rows[row][column]);
objData[row, column + 3] = temp;
objData[data.Rows.Count, column + 3] = temp + (objData[data.Rows.Count, column + 3] == null ? 0 :
Convert.ToDouble(objData[data.Rows.Count, column + 3]));
}
else if (column >= 4 && column < 7)
{
double temp = Convert.ToDouble(data.Rows[row][column]);
objData[row, column + 3] = temp;
objData[data.Rows.Count, column + 3] = temp + (objData[data.Rows.Count, column + 3] == null ? 0 :
Convert.ToDouble(objData[data.Rows.Count, column + 3]));
}
else if (column == 7)
{
double temp1 = Convert.ToDouble(data.Rows[row][column - 4]) +
Convert.ToDouble(data.Rows[row][column - 3]) + Convert.ToDouble(data.Rows[row][column - 2]) +
Convert.ToDouble(data.Rows[row][column - 1]);
objData[row, column + 3] = temp1;
double temp2 = Convert.ToDouble(data.Rows[row][column]);
objData[row, column + 4] = temp2;
objData[data.Rows.Count, column + 3] = temp1 + (objData[data.Rows.Count, column + 3] == null ? 0 :
Convert.ToDouble(objData[data.Rows.Count, column + 3]));
objData[data.Rows.Count, column + 4] = temp2 + (objData[data.Rows.Count, column + 4] == null ? 0 :
Convert.ToDouble(objData[data.Rows.Count, column + 4]));
}
else
{
double temp = Convert.ToDouble(data.Rows[row][column]);
objData[row, column + 4] = temp;
objData[data.Rows.Count, column + 4] = temp + (objData[data.Rows.Count, column + 4] == null ? 0 :
Convert.ToDouble(objData[data.Rows.Count, column + 4]));
}
}
}
m_objRange = m_objSheet.get_Range("A6", m_objOpt);
m_objRange = m_objRange.get_Resize(data.Rows.Count + 1, data.Columns.Count + 1);
m_objRange.Value = objData;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
m_objRange.Font.Size = 10;
m_objRange = m_objSheet.get_Range("G3", m_objOpt);
m_objRange.Value = "各项费用";
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[3, 7], m_objSheet.Cells[4, data.Columns.Count]);
m_objRange.Merge(m_objOpt);
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
m_objRange.Font.Size = 12;
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[3, data.Columns.Count + 1], m_objSheet.Cells[4, data.Columns.Count + 1]);
m_objRange.Merge(m_objOpt);
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
object[, ] objColumn = new object[1, data.Columns.Count - 5];
for (int column = 3; column < data.Columns.Count - 3; column++)
{
if (column < 7)
{
objColumn[0, column - 3] = data.Columns[column].ColumnName;
}
if (column == 7)
{
objColumn[0, column - 3] = data.Columns[data.Columns.Count - 3].ColumnName;
objColumn[0, column - 2] = data.Columns[column].ColumnName;
}
else
{
objColumn[0, column - 2] = data.Columns[column].ColumnName;
}
}
m_objRange = m_objSheet.get_Range("G5", m_objOpt);
m_objRange = m_objRange.get_Resize(1, data.Columns.Count - 5);
m_objRange.Value = objColumn;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
m_objRange.Font.Size = 10.5;
for (int column = 5; column < data.Columns.Count - 3; column++)
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[6, column + 2], m_objSheet.Cells[5 + data.Rows.Count, column + 2]);
object i = m_objRange.Calculate();
string s = "asf";
}
m_objBook.SaveAs(savePath, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
}
}
/// <summary>
/// 复制列（在指定列右边复制指定数量列）
/// </summary>
/// <param name="columnIndex"></param>
/// <param name="count"></param>
public void CopyColumns(int columnIndex, int count)
{
try
{
for (int n = 1; n <= this.WorkSheetCount; n++)
{
workSheet = (Excel.Worksheet)workBook.Worksheets[n];
// range1 = (Excel.Range)workSheet.Columns[columnIndex,this.missing];
range1 = (Excel.Range)workSheet.get_Range(this.IntToLetter(columnIndex) + "1", this.IntToLetter(columnIndex) + "10000");
for (int i = 1; i <= count; i++)
{
// range2 = (Excel.Range)workSheet.Columns[this.missing,columnIndex + i];
range2 = (Excel.Range)workSheet.get_Range(this.IntToLetter(columnIndex + i) + "1", this.IntToLetter(columnIndex + i) + "10000");
range1.Copy(range2);
}
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
public void FinanMgmtInfoPrint(DataSet dsFinanInfo, string savePath)
{
int rowStart = 6;
try
{
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath + @"report\FinanMgmtInfo.xls",
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 = (Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
m_objSheet.Copy(m_objSheet, m_objOpt);
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
m_objSheet.Name = dsFinanInfo.Tables[0].Rows[0][2].ToString();
for (int finanRow = 0; finanRow < dsFinanInfo.Tables[0].Rows.Count; finanRow++)
{
if (!dsFinanInfo.Tables[0].Rows[finanRow][2].ToString().Equals(m_objSheet.Name))
{
SetJumpBoard(rowStart, dsFinanInfo.Tables[0].Rows[finanRow - 1][2].ToString());
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(m_objSheets.Count);
m_objSheet.Copy(m_objSheet, m_objOpt);
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(m_objSheets.Count - 1);
m_objSheet.Name = dsFinanInfo.Tables[0].Rows[finanRow][2].ToString();
rowStart = 6;
}
for (int column = 1; column < dsFinanInfo.Tables[0].Columns.Count; column++)
{
switch (column)
{
case 1: m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[rowStart, column], (Excel.Range)m_objSheet.Cells[rowStart + 1, column]);
m_objRange.MergeCells = true;
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 3: m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[rowStart, column - 1], (Excel.Range)m_objSheet.Cells[rowStart + 1, column - 1]);
m_objRange.MergeCells = true;
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 4: m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[rowStart, column - 1], (Excel.Range)m_objSheet.Cells[rowStart + 1, column - 1]);
m_objRange.MergeCells = true;
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 5: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart, column - 1], m_objSheet.Cells[rowStart, column - 1]);
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 6: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart, column - 1], m_objSheet.Cells[rowStart, column - 1]);
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 7: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart, column - 1], m_objSheet.Cells[rowStart, column - 1]);
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 8: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart + 1, column - 4], m_objSheet.Cells[rowStart + 1, column - 4]);
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 9: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart + 1, column - 4], m_objSheet.Cells[rowStart + 1, column - 4]);
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 10: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart + 1, column - 4], m_objSheet.Cells[rowStart + 1, column - 4]);
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 11: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart, column - 4], m_objSheet.Cells[rowStart, column - 4]);
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 12: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart + 1, column - 5], m_objSheet.Cells[rowStart + 1, column - 5]);
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 13: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart, column - 5], m_objSheet.Cells[rowStart + 1, column - 5]);
m_objRange.MergeCells = true;
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
}
}
rowStart += 2;
}
SetJumpBoard(rowStart, dsFinanInfo.Tables[0].Rows[dsFinanInfo.Tables[0].Rows.Count - 1][2].ToString());
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(m_objSheets.Count);
m_objSheet.Visible = Excel.XlSheetVisibility.xlSheetHidden;
m_objBook.SaveAs(savePath, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
}
catch (Exception e)
{
Util.WriteLog(e.Message, Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
/// <summary>
/// 删除列
/// </summary>
/// <param name="columnIndex"></param>
/// <param name="count"></param>
public void DeleteColumns(int columnIndex, int count)
{
try
{
for (int n = 1; n <= this.WorkSheetCount; n++)
{
workSheet = (Excel.Worksheet)workBook.Worksheets[n];
range = (Excel.Range)workSheet.Columns[this.missing, columnIndex];
for (int i = 0; i < count; i++)
{
range.Delete(Excel.XlDirection.xlDown);
}
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
public void ShowFields()
{
Excel._Worksheet sheet = (Excel._Worksheet)ExcelApp.ActiveSheet;
_xlsCell = (Excel.Range)ExcelApp.ActiveCell;
string _address = _xlsCell.get_AddressLocal(1, 1, Excel.XlReferenceStyle.xlA1, 0, 0).ToString();
_address = _address.Replace("$", "");
string formular = _xlsCell.Formula.ToString();
if (formular.Contains("TVC_QUERY") && formular.Contains("USER TABLE"))
{
string tablename = "data";
try
{
Excel.Range rangeTableName = ExcelApp.get_Range("A" + _xlsCell.Row);
tablename = rangeTableName.Value.ToString();
}
catch
{
}
string tmp = formular.Replace("USER TABLE(", "");
formular = tmp.Substring(0, tmp.Length - 1);
SQLBuilder _sqlBuilder = new SQLBuilder(processingMode.Details);
if (!formular.Contains("TVC_QUERY"))
{
Parsing.Formular2SQLBuilder(formular, ref _sqlBuilder);
}
else
{
Parsing.TVCFormular2SQLBuilder(formular, ref _sqlBuilder);
}
DataTable dt_list = new DataTable();
if (_sqlBuilder.SelectedNodes.Count > 0)
{
//CommoControl commo = new CommoControl();
//string connnectString = commo.CreateConnectString(Properties.Settings.Default.Server
// , Properties.Settings.Default.User
// , Properties.Settings.Default.Pass
// , Properties.Settings.Default.DBName);
//a.THEME = this.THEME;
dt_list.TableName = tablename;
dt_list.Columns.Add("Name");
dt_list.Columns.Add("Code");
for (int i = 0; i < _sqlBuilder.SelectedNodes.Count; i++)
{
Node colum = _sqlBuilder.SelectedNodes[i];
string desc = colum.Description;
int dem = 0;
for (int j = i - 1; j >= 0; j--)
{
Node node = _sqlBuilder.SelectedNodes[j];
if (node.Description == colum.Description)
{
dem++;
}
}
if (dem > 0)
{
desc = colum.Description + dem;
}
dt_list.Rows.Add(new string[] { colum.Description, desc });
}
TVCDesigner.MainForm frm = new TVCDesigner.MainForm(dt_list, null, null);
frm.Show(new WindowWrapper((IntPtr)ExcelApp.DDEAppReturnCode));
}
}
}
/// <summary>
/// 将DataTable数据写入Excel文件（自动分页）
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="rows">每个WorkSheet写入多少行数据</param>
/// <param name="top">表格数据起始行索引</param>
/// <param name="left">表格数据起始列索引</param>
public void DataTableToExcel(DataTable dt, int rows, int top, int left)
{
int rowCount = dt.Rows.Count; //DataTable行数
int colCount = dt.Columns.Count; //DataTable列数
sheetCount = this.GetSheetCount(rowCount, rows); //WorkSheet个数
// StringBuilder sb;
//复制sheetCount-1个WorkSheet对象
for (int i = 1; i < sheetCount; i++)
{
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
workSheet.Copy(missing, workBook.Worksheets[i]);
}
for (int i = 1; i <= sheetCount; i++)
{
int startRow = (i - 1) * rows; //记录起始行索引
int endRow = i * rows; //记录结束行索引
//若是最后一个WorkSheet，那么记录结束行索引为源DataTable行数
if (i == sheetCount)
endRow = rowCount;
//获取要写入数据的WorkSheet对象，并重命名
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
workSheet.Name = sheetPrefixName + "-" + i.ToString();
//将dt中的数据写入WorkSheet
// for(int j=0;j<endRow-startRow;j++)
// {
// for(int k=0;k<colCount;k++)
// {
// workSheet.Cells[top + j,left + k] = dt.Rows[startRow + j][k].ToString();
// }
// }
//利用二维数组批量写入
int row = endRow - startRow;
string[,] ss = new string[row, colCount];
for (int j = 0; j < row; j++)
{
for (int k = 0; k < colCount; k++)
{
ss[j, k] = dt.Rows[startRow + j][k].ToString();
}
}
range = (Excel.Range)workSheet.Cells[top, left];
range = range.get_Resize(row, colCount);
range.Value = ss;
#region 利用Windwo粘贴板批量拷贝数据（在Web下面行不通）
/*sb = new StringBuilder();
for(int j=0;j<endRow-startRow;j++)
{
for(int k=0;k<colCount;k++)
{
sb.Append( dt.Rows[startRow + j][k].ToString() );
sb.Append("\t");
}
sb.Append("\n");
}
System.Windows.Forms.Clipboard.SetDataObject(sb.ToString());
range = (Excel.Range)workSheet.Cells[top,left];
workSheet.Paste(range,false);*/
#endregion
}
}
void frm_FormClosed(object sender, System.Windows.Forms.FormClosedEventArgs e)
{
if (frm.DialogResult == System.Windows.Forms.DialogResult.OK)
{
Excel._Worksheet a = ExcelApp.ActiveSheet as Excel.Worksheet;
if (frm.Status == "C")
{
try
{
a.get_Range(frm.Pos, type).ClearComments();
}
catch
{
}
a.get_Range(frm.Pos, type).AddComment(frm.TTFormular);
}
else
if (frm.Status == "T")
{
a.get_Range(frm.Pos, type).Value = String.Format("<#{0}>", frm.TTFormular);
}
else
if (frm.Status == "L")
{
try
{
//DataTable dt = frm.DataReturn;
System.Data.DataTable dt = frm.DataReturn;
//Excel.DataTable dtEx;
//Excel.Workbook _wbook = (Excel.Workbook)Application.ActiveWorkbook;
//_wbook.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
Excel.Worksheet _wsheet = ExcelApp.ActiveSheet as Excel.Worksheet;
Excel.Range currentRange = _wsheet.get_Range(_address, Type.Missing);
for (int i = 0; i < dt.Columns.Count; i++)
{
Excel.Range _range = (Excel.Range)_wsheet.Cells[currentRange.Row, i + currentRange.Column];
_range.Font.Bold = true;
_range.Value = dt.Columns[i].ColumnName;
}
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
Excel.Range _range = (Excel.Range)_wsheet.Cells[i + currentRange.Row + 1, j + currentRange.Column];
_range.Value = dt.Rows[i][j];
}
}
//string add = _wsheet.Name + "!R1C1:R" + (dt.Rows.Count + 1) + "C" + dt.Columns.Count;
//_wbook.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
//Excel.Worksheet _wpivotsheet = (Excel.Worksheet)Application.ActiveWorkbook.ActiveSheet;
//string des = _wpivotsheet.Name + "!R3C1";
//_wbook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, add).CreatePivotTable(des, "PivotTable1", Type.Missing, Excel.XlPivotTableVersionList.xlPivotTableVersion10);
}
catch (Exception ex)
{
BUS.CommonControl.AddLog("ErroLog", __documentDirectory + "\\Log", "[Addin] [" + DateTime.Now.ToString() + "] : " + ex.Message + "\n\t" + ex.Source + "\n\t" + ex.StackTrace);
}
//a.get_Range(frm.Pos, type).AddComment(frm.TTFormular);
}
else
if (frm.Status == "F")
{
a.get_Range(frm.Pos, type).Value = "=" + frm.TTFormular;
}
else
if (frm.Status == "U")
{
a.get_Range(frm.Pos, type).Value = frm.TTFormular;
}
}
}
/// <summary>
/// 将指定范围区域拷贝到目标区域
/// </summary>
/// <param name="sheetName">WorkSheet名称</param>
/// <param name="startCell">要拷贝区域的开始Cell位置（比如：A10）</param>
/// <param name="endCell">要拷贝区域的结束Cell位置（比如：F20）</param>
/// <param name="targetCell">目标区域的开始Cell位置（比如：H10）</param>
public void RangeCopy(string sheetName, string startCell, string endCell, string targetCell)
{
try
{
Excel.Worksheet sheet = null;
for (int i = 1; i <= this.WorkSheetCount; i++)
{
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
if (workSheet.Name == sheetName)
{
sheet = workSheet;
}
}
if (sheet != null)
{
for (int i = sheetCount; i >= 1; i--)
{
range1 = sheet.get_Range(startCell, endCell);
range2 = sheet.get_Range(targetCell, this.missing);
range1.Copy(range2);
}
}
else
{
this.KillExcelProcess();
throw new Exception("名称为\"" + sheetName + "\"的工作表不存在");
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
private void adxExcelEvents_SheetBeforeDoubleClick(object sender, ADXExcelSheetBeforeEventArgs e)
{
Excel.Range Target = e.Range as Excel.Range;
_address = Target.get_AddressLocal(Target.Row, Target.Column, Excel.XlReferenceStyle.xlA1, 0, 0).Replace("$", "");
if (Target.Formula != null)
{
string formular = Target.Formula.ToString();
if (formular.Contains("TT_XLB_EB") || formular.Contains("USER TABLE"))
{
//Target.set_Value(Type.Missing, formular);
//Application.Undo();
if (frm != null)
{
frm.Close();
frm = new QDAddIn(_config, _address, ExcelApp, formular, _strConnect, _strConnectDes, _user);
frm.Config = _config;
frm.FormClosed += new System.Windows.Forms.FormClosedEventHandler(frm_FormClosed);
//frm.Pos = _address;
//if (value.Contains("TT_XLB_ED"))
// frm.GetQueryBuilderFromFomular(value);
//frm.TopMost = true;
frm.Show(new WindowWrapper((IntPtr)ExcelApp.DDEAppReturnCode));//new WindowWrapper((IntPtr)ExcelApp.Hwnd)
}
else
{
frm = new QDAddIn(_config, _address, ExcelApp, formular, _strConnect, _strConnectDes, _user);
frm.Config = _config;
frm.FormClosed += new System.Windows.Forms.FormClosedEventHandler(frm_FormClosed);
//frm.Pos = _address;
//if (value.Contains("TT_XLB_ED"))
// frm.GetQueryBuilderFromFomular(value);
//frm.TopMost = true;
//
frm.Show(new WindowWrapper((IntPtr)ExcelApp.DDEAppReturnCode));//new WindowWrapper((IntPtr)ExcelApp.Hwnd)
}
frm.Focus();
e.Cancel = true;
}
}
else
{
if (Target.Text != null)
{
string formular = Target.Text.ToString();
if (formular.Contains("TT_XLB_EB") || formular.Contains("USER TABLE"))
{
//Target.set_Value(Type.Missing, formular);
//Application.Undo();
if (frm != null)
{
frm.Close();
frm = new QDAddIn(_config, _address, ExcelApp, formular, _strConnect, _strConnectDes, _user);
frm.Config = _config;
frm.FormClosed += new System.Windows.Forms.FormClosedEventHandler(frm_FormClosed);
//frm.Pos = _address;
//if (value.Contains("TT_XLB_ED"))
// frm.GetQueryBuilderFromFomular(value);
//frm.TopMost = true;
frm.Show();//new WindowWrapper((IntPtr)ExcelApp.Hwnd)
}
else
{
frm = new QDAddIn(_config, _address, ExcelApp, formular, _strConnect, _strConnectDes, _user);
frm.Config = _config;
frm.FormClosed += new System.Windows.Forms.FormClosedEventHandler(frm_FormClosed);
//frm.Pos = _address;
//if (value.Contains("TT_XLB_ED"))
// frm.GetQueryBuilderFromFomular(value);
//frm.TopMost = true;
//
frm.Show(new WindowWrapper((IntPtr)ExcelApp.DDEAppReturnCode));//
}
frm.Focus();
e.Cancel = true;
}
}
}
}
/// <summary>
/// 将指定索引列的数据相同的行合并，对指定WorkSheet操作
/// </summary>
/// <param name="columnIndex">要合并的列索引</param>
/// <param name="beginRowIndex">合并开始行索引</param>
/// <param name="rows">要合并的行数</param>
private void MergeRows(Excel.Worksheet sheet, int columnIndex, int beginRowIndex, int rows)
{
int beginIndex = beginRowIndex;
int count = 0;
string text1;
string text2;
if (sheet == null)
return;
for (int j = beginRowIndex; j < beginRowIndex + rows; j++)
{
range1 = (Excel.Range)sheet.Cells[j, columnIndex];
range2 = (Excel.Range)sheet.Cells[j + 1, columnIndex];
text1 = range1.Text.ToString();
text2 = range2.Text.ToString();
if (text1 == text2)
{
++count;
}
else
{
if (count > 0)
{
this.MergeCells(sheet, beginIndex, columnIndex, beginIndex + count, columnIndex, text1);
}
beginIndex = j + 1; //设置开始合并行索引
count = 0; //计数器清0
}
}
}
private void butexcel_Click(object sender, EventArgs e)
{
if (dataGridView1.DataSource == null)
{
return;
}
try
{
DataTable tb = null;
string ss = "";
if (tabControl1.SelectedTab == tabPage1)
{
tb = (DataTable)this.dataGridView1.DataSource;
ss = "执行科室项目汇总表(" + TreeDept.Nodes[0].Text + ")";
}
if (tabControl1.SelectedTab == tabPage2)
{
tb = (DataTable)this.dataGridView2.DataSource;
ss = "执行科室项目汇总表(" + TreeDept.Nodes[0].Text + ")";
}
if (tabControl1.SelectedTab == tabPage3)
{
tb = (DataTable)this.dataGridView3.DataSource;
ss = "执行科室项目明细表(" + TreeDept.Nodes[0].Text + ")";
}
// 创建Excel对象 --LeeWenjie 2006-11-29
Excel.Application xlApp = new Excel.ApplicationClass();
if (xlApp == null)
{
MessageBox.Show("Excel无法启动");
return;
}
// 创建Excel工作薄
Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[1];
// 列索引，行索引，总列数，总行数
int colIndex = 0;
int RowIndex = 0;
int colCount = 0;
int RowCount = tb.Rows.Count + 1;
for (int i = 0; i <= tb.Columns.Count - 1; i++)
{
colCount = colCount + 1;
}
//查询条件
string swhere = "";
if (chksfrq.Checked == true)
{
swhere = " 记费日期从:" + dtp1.Value.ToString() + " 到 " + dtp2.Value.ToString();
}
if (chkqfrq.Checked == true)
{
swhere = " 确费日期从:" + dtpqrrq1.Value.ToString() + " 到 " + dtpqrrq2.Value.ToString();
}
// 设置标题
Excel.Range range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, colCount]);
range.MergeCells = true;
xlApp.ActiveCell.FormulaR1C1 = ss;
xlApp.ActiveCell.Font.Size = 20;
xlApp.ActiveCell.Font.Bold = true;
xlApp.ActiveCell.HorizontalAlignment = Excel.Constants.xlCenter;
// 设置条件
Excel.Range range1 = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, colCount]);
range1.MergeCells = true;
//xlApp.ActiveCell.FormulaR1C1 = swhere;
//xlApp.ActiveCell.Font.Size = 20;
//xlApp.ActiveCell.Font.Bold = true;
//xlApp.ActiveCell.HorizontalAlignment = Excel.Constants.xlCenter;
// 创建缓存数据
object[,] objData = new object[RowCount + 1, colCount + 1];
// 获取列标题
for (int i = 0; i <= tb.Columns.Count - 1; i++)
{
objData[1, colIndex++] = tb.Columns[i].Caption;
}
// 获取数据
objData[0, 0] = swhere;
for (int i = 0; i <= tb.Rows.Count - 1; i++)
{
colIndex = 0;
for (int j = 0; j <= tb.Columns.Count - 1; j++)
{
//if (myDataGrid1.TableStyles[0].GridColumnStyles[j].Width>0)
//{
if (tb.Columns[j].Caption == "门诊号")
{
objData[i + 2, colIndex++] = "'" + tb.Rows[i][j].ToString();
}
else
{
objData[i + 2, colIndex++] = "" + tb.Rows[i][j].ToString();
}
//}
}
Application.DoEvents();
}
// 写入Excel
range = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]);
range.Value2 = objData;
//
xlApp.get_Range(xlApp.Cells[3, 1], xlApp.Cells[RowCount + 2, colCount]).Borders.LineStyle = 1;
//设置报表表格为最适应宽度
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Select();
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Columns.AutoFit();
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Font.Size = 9;
xlApp.Visible = true;
}
catch (System.Exception err)
{
MessageBox.Show(err.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
this.Cursor = Cursors.Arrow;
}
}
/// <summary>
/// 将DataTable数据写入Excel文件（不分页）
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="top">表格数据起始行索引</param>
/// <param name="left">表格数据起始列索引</param>
public void DataTableToExcel(DataTable dt, int top, int left)
{
int rowCount = dt.Rows.Count; //DataTable行数
int colCount = dt.Columns.Count; //DataTable列数
//利用二维数组批量写入
string[,] arr = new string[rowCount+1, colCount+1];
for (int i = 0; i < colCount;i++ )
{
arr[0, i] = dt.Columns[i].ColumnName.ToString();
}
for (int j = 0; j < rowCount; j++)
{
for (int k = 0; k < colCount; k++)
{
arr[j+1, k] = dt.Rows[j][k].ToString();
}
}
range = (Excel.Range)workSheet.Cells[top, left];
range = range.get_Resize(rowCount+1, colCount+1);
range.Value = arr;
}
private void button1_Click(object sender, EventArgs e)
{
try
{
DataTable tb = (DataTable)this.dgvgqyp.DataSource;
// 创建Excel对象
Excel.Application xlApp = new Excel.ApplicationClass();
if (xlApp == null)
{
MessageBox.Show("Excel无法启动");
return;
}
// 创建Excel工作薄
Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[1];
// 列索引，行索引，总列数，总行数
int colIndex = 0;
int RowIndex = 0;
int colCount = 0;
int RowCount = tb.Rows.Count;
for (int i = 0; i <= dgvgqyp.Columns.Count - 1; i++)
{
if (dgvgqyp.Columns[i].Width > 0)
{
colCount = colCount + 1;
}
}
// 设置标题
Excel.Range range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, colCount]);
range.MergeCells = true;
xlApp.ActiveCell.FormulaR1C1 = this.Text;
xlApp.ActiveCell.Font.Size = 20;
xlApp.ActiveCell.Font.Bold = true;
xlApp.ActiveCell.HorizontalAlignment = Excel.Constants.xlCenter;
//查询条件
string bz = "";
bz = bz + "药剂科室:" + cmbyjks.Text.Trim() + " 入库日期：从" + dtpshrqB.Value.ToString("yyyy-MM-dd") + " 到 " + dtpshrqE.Value.ToString("yyyy-MM-dd");
bz = bz + " 失效日期：" + dtpXQ.Value.ToString("yyyy-MM-dd");
string swhere = " " + bz;
// 设置条件
Excel.Range rangeT = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, colCount]);
rangeT.MergeCells = true;
object[,] objDataT = new object[1, 1];
range = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]);
objDataT[0, 0] = swhere;
range.Value2 = objDataT;
// 创建缓存数据
object[,] objData = new object[RowCount + 1, colCount + 1];
// 获取列标题
for (int i = 0; i <= dgvgqyp.Columns.Count - 1; i++)
{
if (dgvgqyp.Columns[i].Width > 0)
{
objData[0, colIndex++] = dgvgqyp.Columns[i].HeaderText;
}
}
// 获取数据
for (int i = 0; i <= tb.Rows.Count - 1; i++)
{
colIndex = 0;
for (int j = 0; j <= dgvgqyp.Columns.Count - 1; j++)
{
if (dgvgqyp.Columns[j].Width > 0)
{
objData[i + 1, colIndex++] = "" + tb.Rows[i][j].ToString();
}
}
Application.DoEvents();
}
// 写入Excel
range = xlSheet.get_Range(xlApp.Cells[3, 1], xlApp.Cells[RowCount + 3, colCount]);
range.Value2 = objData;
//
xlApp.get_Range(xlApp.Cells[3, 1], xlApp.Cells[RowCount + 3, colCount]).Borders.LineStyle = 1;
//设置报表表格为最适应宽度
xlApp.get_Range(xlApp.Cells[3, 1], xlApp.Cells[RowCount + 3, colCount]).Select();
xlApp.get_Range(xlApp.Cells[3, 1], xlApp.Cells[RowCount + 3, colCount]).Columns.AutoFit();
xlApp.get_Range(xlApp.Cells[3, 1], xlApp.Cells[RowCount + 3, colCount]).Font.Size = 9;
xlApp.Visible = true;
}
catch (System.Exception err)
{
MessageBox.Show(err.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
this.Cursor = Cursors.Arrow;
}
}
}//end ArrayToExcel
/// <summary>
/// 将二维数组数据写入Excel文件（不分页）
/// </summary>
/// <param name="arr">二维数组</param>
/// <param name="top">行索引</param>
/// <param name="left">列索引</param>
public void ArrayToExcel(string[,] arr, int top, int left)
{
int rowCount = arr.GetLength(0); //二维数组行数（一维长度）
int colCount = arr.GetLength(1); //二维数据列数（二维长度）
range = (Excel.Range)workSheet.Cells[top, left];
range = range.get_Resize(rowCount, colCount);
range.FormulaArray = arr;
}//end ArrayToExcel
private void toolbtnOpen_Click(object sender, EventArgs e)
{
if (openFileDialog1.ShowDialog() != DialogResult.OK)
{
return;
}
List <Tuple <Lab, Rgb> > oldlabs = new List <Tuple <Lab, Rgb> >(Labs);
string oldfilename = openFileDialog1.FileName;
bool isexcelformat = openFileDialog1.FileName.EndsWith(".xlsx");
Excel.Application xlApp = null;
Excel.Workbook xlWorkbook = null;
Excel._Worksheet xlWorksheet = null;
Excel.Range xlRange = null;
try
{
Labs.Clear();
if (isexcelformat)
{
xlApp = new Excel.Application();
xlWorkbook = xlApp.Workbooks.Open(openFileDialog1.FileName, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorksheet = (Excel._Worksheet)xlWorkbook.Sheets[1];
xlRange = xlWorksheet.UsedRange;
for (int r = 2; r <= xlRange.Rows.Count; r++)
{
double l = (double)(xlRange.Cells[r, 1] as Excel.Range).Value2;
double a = (double)(xlRange.Cells[r, 2] as Excel.Range).Value2;
double b = (double)(xlRange.Cells[r, 3] as Excel.Range).Value2;
Labs.Add(new Tuple <Lab, Rgb>(new Lab {
L = l, A = a, B = b
}, new Rgb {
R = 0, G = 0, B = 0
}));
}
}
else
{
string[] lines = File.ReadAllLines(openFileDialog1.FileName, Encoding.Default);
CultureInfo cult = new CultureInfo("ru-RU");
foreach (var line in lines.Where(x => !string.IsNullOrWhiteSpace(x)))
{
double l = 0;
double a = 0;
double b = 0;
string[] tokens = line.Split(new char[] { '\t' }, StringSplitOptions.RemoveEmptyEntries);
double.TryParse(tokens[0], NumberStyles.Float, cult, out l);
double.TryParse(tokens[1], NumberStyles.Float, cult, out a);
double.TryParse(tokens[2], NumberStyles.Float, cult, out b);
Labs.Add(new Tuple <Lab, Rgb>(new Lab {
L = l, A = a, B = b
}, new Rgb {
R = 0, G = 0, B = 0
}));
}
}
Generated = false;
}
catch (Exception ex)
{
Labs = oldlabs;
openFileDialog1.FileName = oldfilename;
MessageBox.Show("Ошибка чтения файла, предыдущие данные не были изменены. Подробности об ошибке\n" + ex.Message);
}
finally
{
if (isexcelformat)
{
Marshal.FinalReleaseComObject(xlRange);
Marshal.FinalReleaseComObject(xlWorksheet);
xlWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);
Marshal.FinalReleaseComObject(xlWorkbook);
xlApp.Quit();
Marshal.FinalReleaseComObject(xlApp);
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
toollabelCount.Text = "Count: " + Labs.Count.ToString();
}
}//end ArrayToExcel
/// <summary>
/// 将二维数组数据写入Excel文件（不分页）
/// </summary>
/// <param name="sheetIndex">工作表索引</param>
/// <param name="arr">二维数组</param>
/// <param name="top">行索引</param>
/// <param name="left">列索引</param>
public void ArrayToExcel(int sheetIndex, string[,] arr, int top, int left)
{
if (sheetIndex > this.WorkSheetCount)
{
this.KillExcelProcess();
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
// 改变当前工作表
this.workSheet = (Excel.Worksheet)this.workBook.Sheets.get_Item(sheetIndex);
int rowCount = arr.GetLength(0); //二维数组行数（一维长度）
int colCount = arr.GetLength(1); //二维数据列数（二维长度）
range = (Excel.Range)workSheet.Cells[top, left];
range = range.get_Resize(rowCount, colCount);
range.Value2 = arr;
}//end ArrayToExcel
private void butexcel_Click(object sender, EventArgs e)
{
try
{
DataTable tb = null;
string ss = "";
tb = (DataTable)this.dataGridView1.DataSource;
ss = this._chineseName;
// 创建Excel对象
Excel.Application xlApp = new Excel.ApplicationClass();
if (xlApp == null)
{
MessageBox.Show("Excel无法启动");
return;
}
// 创建Excel工作薄
Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[1];
// 列索引，行索引，总列数，总行数
int colIndex = 0;
int RowIndex = 0;
int colCount = 0;
int RowCount = tb.Rows.Count + 1;
for (int i = 0; i <= tb.Columns.Count - 1; i++)
{
colCount = colCount + 1;
}
//查询条件
string swhere = "";
swhere = " 记费日期从:" + dtpBjksj.Value.ToString() + " 到 " + dtpEjksj.Value.ToString();
// 设置标题
Excel.Range range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, colCount]);
range.MergeCells = true;
xlApp.ActiveCell.FormulaR1C1 = ss;
xlApp.ActiveCell.Font.Size = 20;
xlApp.ActiveCell.Font.Bold = true;
xlApp.ActiveCell.HorizontalAlignment = Excel.Constants.xlCenter;
// 设置条件
Excel.Range range1 = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, colCount]);
range1.MergeCells = true;
// 创建缓存数据
object[,] objData = new object[RowCount + 1, colCount + 1];
// 获取列标题
for (int i = 0; i <= tb.Columns.Count - 1; i++)
{
objData[1, colIndex++] = tb.Columns[i].Caption;
}
// 获取数据
objData[0, 0] = swhere;
for (int i = 0; i <= tb.Rows.Count - 1; i++)
{
colIndex = 0;
for (int j = 0; j <= tb.Columns.Count - 1; j++)
{
objData[i + 2, colIndex++] = "" + tb.Rows[i][j].ToString();
}
Application.DoEvents();
}
// 写入Excel
range = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]);
range.Value2 = objData;
//
xlApp.get_Range(xlApp.Cells[3, 1], xlApp.Cells[RowCount + 2, colCount]).Borders.LineStyle = 1;
//设置报表表格为最适应宽度
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Select();
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Columns.AutoFit();
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Font.Size = 9;
xlApp.Visible = true;
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
}
private void SetJumpBoard(int rowStart,string getClassName)
{
rowStart += 1;
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart,1],m_objSheet.Cells[rowStart+1,1]);
m_objRange.MergeCells = true;
m_objRange.Value = "园所信息：";
m_objRange.Font.Size = 10;
m_objRange.Font.Bold = true;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart,2],m_objSheet.Cells[rowStart+1,3]);
m_objRange.MergeCells = true;
m_objRange.Value = new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString();
m_objRange.Font.Size = 10;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart+2,1],m_objSheet.Cells[rowStart+3,1]);
m_objRange.MergeCells = true;
m_objRange.Value = "统计班级：";
m_objRange.Font.Size = 10;
m_objRange.Font.Bold = true;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart+2,2],m_objSheet.Cells[rowStart+3,3]);
m_objRange.MergeCells = true;
m_objRange.Value = getClassName;
m_objRange.Font.Size = 10;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart+4,1],m_objSheet.Cells[rowStart+5,1]);
m_objRange.MergeCells = true;
m_objRange.Value = "制表日期：";
m_objRange.Font.Size = 10;
m_objRange.Font.Bold = true;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart+4,2],m_objSheet.Cells[rowStart+5,3]);
m_objRange.MergeCells = true;
m_objRange.Value = DateTime.Now.ToString("yyyy-MM-dd");
m_objRange.Font.Size = 10;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
}
public void RealtimeMorningPrint(DataSet dsRealtimeMorning,string savePath)
{
KillProcess();
try
{
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath+@"report\RealtimeMorningInfo.xls",
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 = (Excel.Sheets)m_objBook.Worksheets;
//获取第一个打印页
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
for ( int row = 0; row < dsRealtimeMorning.Tables[0].Rows.Count; row ++ )
{
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 7, 1], (Excel.Range)m_objSheet.Cells[row + 7, 1]);
m_objRange.Value = dsRealtimeMorning.Tables[0].Rows[row][1].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.Font.Size = 10;
for ( int column = 3;column < dsRealtimeMorning.Tables[0].Columns.Count; column ++)
{
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 7, column - 1], (Excel.Range)m_objSheet.Cells[row + 7, column - 1]);
m_objRange.set_Item(1,1,dsRealtimeMorning.Tables[0].Rows[row][column].ToString());
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.Font.Size = 10;
}
if ( row == dsRealtimeMorning.Tables[0].Rows.Count - 1 )
{
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 9, 1], (Excel.Range)m_objSheet.Cells[row + 9, 1]);
m_objRange.Value = "园所信息：";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 12;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 9, 2], (Excel.Range)m_objSheet.Cells[row + 9, 2]);
m_objRange.Value = new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString();
m_objRange.Font.Size = 12;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 10, 1], (Excel.Range)m_objSheet.Cells[row + 10, 1]);
m_objRange.Value = "统计日期：";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 12;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 10, 2], (Excel.Range)m_objSheet.Cells[row + 10, 2]);
m_objRange.Value = DateTime.Now.ToString("yyyy-MM-dd");
m_objRange.Font.Size = 12;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
}
}
m_objBook.SaveAs(savePath, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
}
catch(Exception e)
{
Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
public void FinanMgmtInfoPrint(DataSet dsFinanInfo,string savePath)
{
int rowStart = 6;
try
{
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath+@"report\FinanMgmtInfo.xls",
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 = (Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
m_objSheet.Copy(m_objSheet,m_objOpt);
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
m_objSheet.Name = dsFinanInfo.Tables[0].Rows[0][2].ToString();
for ( int finanRow = 0;finanRow < dsFinanInfo.Tables[0].Rows.Count; finanRow ++ )
{
if ( !dsFinanInfo.Tables[0].Rows[finanRow][2].ToString().Equals(m_objSheet.Name) )
{
SetJumpBoard(rowStart,dsFinanInfo.Tables[0].Rows[finanRow-1][2].ToString());
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(m_objSheets.Count);
m_objSheet.Copy(m_objSheet,m_objOpt);
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(m_objSheets.Count-1);
m_objSheet.Name = dsFinanInfo.Tables[0].Rows[finanRow][2].ToString();
rowStart = 6;
}
for ( int column = 1;column < dsFinanInfo.Tables[0].Columns.Count; column ++)
{
switch ( column )
{
case 1: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart,column],m_objSheet.Cells[rowStart+1,column]);
m_objRange.MergeCells = true;
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 3: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart,column-1],m_objSheet.Cells[rowStart+1,column-1]);
m_objRange.MergeCells = true;
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 4: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart,column-1],m_objSheet.Cells[rowStart+1,column-1]);
m_objRange.MergeCells = true;
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 5: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart,column-1],m_objSheet.Cells[rowStart,column-1]);
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 6: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart,column-1],m_objSheet.Cells[rowStart,column-1]);
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 7: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart,column-1],m_objSheet.Cells[rowStart,column-1]);
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 8: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart+1,column-4],m_objSheet.Cells[rowStart+1,column-4]);
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 9: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart+1,column-4],m_objSheet.Cells[rowStart+1,column-4]);
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 10: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart+1,column-4],m_objSheet.Cells[rowStart+1,column-4]);
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 11: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart,column-4],m_objSheet.Cells[rowStart,column-4]);
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 12: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart+1,column-5],m_objSheet.Cells[rowStart+1,column-5]);
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 13: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart,column-5],m_objSheet.Cells[rowStart+1,column-5]);
m_objRange.MergeCells = true;
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
}
}
rowStart += 2;
}
SetJumpBoard(rowStart,dsFinanInfo.Tables[0].Rows[dsFinanInfo.Tables[0].Rows.Count-1][2].ToString());
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(m_objSheets.Count);
m_objSheet.Visible = Excel.XlSheetVisibility.xlSheetHidden;
m_objBook.SaveAs(savePath, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
}
catch(Exception e)
{
Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
private void AutoFitColumns(string startRange, int rowCount, int colCount)
{
_range = _sheet.get_Range(startRange, _optionalValue);
_range = _range.get_Resize(rowCount, colCount);
_range.Columns.AutoFit();
}
